January 9, 2006 at 2:24 pm
I am trying to create a copy of a worksheet programtically in an ActiveX Script in DTS (I need between 2 and 'n' pages for a report, where 'n' is determined sometime when I can't sit there and create 'n' worksheet copies manually). These pages are formatted a LOT, so it must be a copy.
I found the Worksheet.Copy() method documentation (and I use that word loosely), but I cannot get it to work.
When using the syntax directly from the MSDN page
Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")
I get an error "Expected statement". Then, I removed the colon because I'm pretty sure VB doesn't sure that operator
Worksheets("Sheet1").Copy After=Worksheets("Sheet3")
and I get a different error "Object doesn't support this property or method"
I must be missing something really simple here, because if it was difficult I would think I'd find more about this problem on the intarweb.
Thanks in advance for any help.
-- Stephen Cook
January 10, 2006 at 8:26 am
This will do it for you:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
' Initialise dimensions
Dim appExcel
Dim newBook
'Set excel objects
Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.Add
' Do the work
newBook.Sheets("Sheet1").Move ,newBook.Sheets("Sheet3")
With newBook
.SaveAs "c:\test.xls"
.save
End With
' Clean up
appExcel.quit
set appExcel = nothing
' Register success
Main = DTSTaskExecResult_Success
End Function
January 10, 2006 at 8:33 am
It looks like the difference between Sheets and Worksheets (whatever that might be) got me.
Thank you very much, you just saved me a lot of hassle!
-- Stephen Cook
January 11, 2006 at 7:08 am
Hi. Why use AxtiveX Script for this task. VB or VBA is easier.
This will also work added to what Stephen wrote:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Set appexcel = CreateObject("Excel.Application")
appexcel.DisplayAlerts = False
set newobject = appexcel.Workbooks.open( "C:\Book1.xls") 'Contains the template sheet1
for N = 3 to 1 step -1 'Replace 3 with actual number of sheets
newobject.Sheets("Sheet1").Copy ,newobject.Sheets("Sheet1")
'Copies the template to a new sheet after sheet1 (index 2)
newobject.Sheets(2).name = N 'Renames
'Put some data in the sheet
next
newobject.Sheets("Sheet1").delete 'Removes the template sheet
With newobject
.SaveAs "c:\test.xls" 'Replace without a warning
End With
appexcel.quit
set appexcel = nothing
Main = DTSTaskExecResult_Success
End Function
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply