Worksheets("Sheet1").Copy Before:=

Hello,
I'm trying to figure out how to use the
worksheet.copy function in a vbs script but it generates
an error saying expected statement. The code is below

XLFile = "c:\scripts\test\book1.xls"

Set objExcel = WScript.CreateObject("EXCEL.application")
objExcel.Visible = True
objExcel.Workbooks.Open XLFile
objExcel.Worksheets.Add.Name ="USERSLIST"



objExcel.Workbooks(XLFile).Worksheets("USERSLIST").Copy
After:=Workbooks(XLFile).Worksheets(2)


Any help at all would be greatly appreciated.

Thanks.

Alexander
Wed Jul 30 19:26:39 CDT 2003

on 31.07.03 01:22 Mark Garrison wrote:
> Hello,
> I'm trying to figure out how to use the
> worksheet.copy function in a vbs script but it generates
> an error saying expected statement. The code is below
[..]
> objExcel.Workbooks(XLFile).Worksheets("USERSLIST").Copy
> After:=Workbooks(XLFile).Worksheets(2)

VBS doesn't know how to deal with named VBA-arguments,
the pascal-like syntax ":=" just raises an syntax error :-(
you have to treat them as postional arguments instead,
in this case "After" ist the 2nd optional param of workSheets.Copy:

set ws = objExcel.Workbooks(XLFile).Worksheets
ws("USERSLIST").Copy ,ws(2)


--
Gruesse,
Alex

Dave
Wed Jul 30 20:08:34 CDT 2003

Something like this should work.

Option Explicit
Dim filePath, oExcel, oSheet

filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open filepath
Set oSheet = oExcel.ActiveWorkbook.Worksheets("Some Sheet Name")

'copy before
oSheet.Copy oSheet

'copy after
'oSheet.Copy ,oSheet

oExcel.DisplayAlerts = False
oExcel.ActiveWorkbook.SaveAs filePath
oExcel.ActiveWorkbook.Close
set oSheet = Nothing
Set oExcel = Nothing

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft MVP [Windows NT/2000 Operating Systems]

"Mark Garrison" wrote:
> Hello,
> I'm trying to figure out how to use the
> worksheet.copy function in a vbs script but it generates
> an error saying expected statement. The code is below
>
> XLFile = "c:\scripts\test\book1.xls"
>
> Set objExcel = WScript.CreateObject("EXCEL.application")
> objExcel.Visible = True
> objExcel.Workbooks.Open XLFile
> objExcel.Worksheets.Add.Name ="USERSLIST"
>
>
>
> objExcel.Workbooks(XLFile).Worksheets("USERSLIST").Copy
> After:=Workbooks(XLFile).Worksheets(2)
>
>
> Any help at all would be greatly appreciated.
>
> Thanks.