Worksheets("Sheet1").Copy Before:=

[Mark Garrison]

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 Mueller]

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 Patrick]

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]

>.
>

Thanks this did it.

Set ws = objExcel.Worksheets
ws("USERSLIST").Copy ,ws(2)