Copying an Excel worksheet (ActiveX Script)

• Stephen E. Cook

  SSCrazy

  Points: 2275

  More actions

  January 9, 2006 at 2:24 pm

  #76470

  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

• Jonathan Stokes

  SSCrazy Eights

  Points: 9861

  More actions

  January 10, 2006 at 8:26 am

  #613828

  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

  ---

  ------------------------------
  The Users are always right - when I'm not wrong!

• Stephen E. Cook

  SSCrazy

  Points: 2275

  More actions

  January 10, 2006 at 8:33 am

  #613830

  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

• Gosta Munktell

  SSCertifiable

  Points: 5973

  More actions

  January 11, 2006 at 7:08 am

  #614052

  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