VBS script to copy excel sheets into new excel file 1

[jrh405]

I am trying this code .vbs but am getting an error of "Copy Method of Class Worksheet failed". Existing File1 has two sheets called "Test1" and "Test 2" while File2 has two sheets called "Test3" and "Test4". However generically the number of sheets may be different with different names and it more the process of copying all over regardless of name.

Any help on what could be tweaked would be appreciated.



Dim eapp
Dim wkbk_from
Dim wkbk_to
Dim wksh
Dim J

Set eapp = CreateObject("Excel.Application")
Set wkbk_from = eapp.Workbooks.Open("D:\Excel_VB_Test\File2.xlsx")
Set wkbk_to = eapp.Workbooks.Open("D:\Excel_VB_Test\File1.xlsx")
eapp.Visible = true

For j = 1 to wkbk_from.Worksheets.count
wkbk_from.worksheets(j).Copy wkbk_to
Next

wkbk_to.SaveAs "NewFile.xlsx"

 

[combo]

In excel VBA the Copy method syntax is [tt]WorksheetFrom.Copy (Before, After)[/tt], where Before and After are optional, if specified, they are a sheet the copy is related to, and cannot be defined both.

So, first, you have to refer to sheet, secondly, properly handle arguments:
[tt]wkbk_from.worksheets(j).Copy(wkbk_to.Worksheets(1), Null)[/tt]

combo

 

[jrh405]

@combo

Thanks for your help. Note that the Before and After syntax will not work in a .vbs script execution as I would be calling it from a command line but that order of the sheets doesn't matter too much. I was able to use your suggestion on the wkbkt_to.worksheets(1) to have it work. Thanks so much for your assistance.

 

[strongm]

>the Before and After syntax will not work in a .vbs script

??? How do you mean?