I am posting this question again to know if I could get an answer. This pice of code runs as a macro in Excel without ant problems and copies all the workbooks into one workbook with different worksheets. But when I run this as an activex script in a DTS package, I get compilation error on line where Copy statement is? Does the syntax in activex varies?
FUNCTION Main()
DIM Excel_Application, Source_WorkBook, Target_WorkBook, Source_WorkSheet DIM SourcePathName, TargetFileName, SheetName DIM ArrayVariable, I
SET Excel_Application = CreateObject("Excel.Application")
ArrayVariable = ARRAY("Test1.xls", "Test2.xls") FOR I = LBOUND(ArrayVariable) TO UBOUND(ArrayVariable) SET Target_WorkBook = Excel_Application.Workbooks.OPEN(TargetFileName) SET Source_WorkBook = Excel_Application.Workbooks.Open(SourcePathName & ArrayVariable(I)) For Each Source_WorkSheet in Source_WorkBook.WorkSheets Sheetname = Source_Worksheet.NAME Excel_Application.DisplayAlerts = False Source_WorkBook.Sheets(SheetName).Copy _ After:=Target_WorkBook.Worksheets(Target_WorkBook.Worksheets.Count) Target_WorkBook.Save Excel_Application.DisplayAlerts = True
NEXT Source_WorkBook.CLOSE NEXT
SET Source_WorkBook = Nothing SET Target_WorkBook = Nothing Excel_Application.Quit SET Excel_Application = Nothing
Combining workbooks
Posted - 10/05/2006 : 12:21:08
