Combining workbooks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Combining workbooks

Author

Topic

srxr9
Starting Member

15 Posts

Posted - 2006-10-05 : 12:21:08

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

SourcePathName = "C:\temp\"
TargetFileName = "C:\temp\new.xls"

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

Main = DTSTaskExecResult_Success

END FUNCTION

Thanks
Suresh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-05 : 12:46:10

Little change is needed, since VBA syntax does not work entirely in ActiveX scripting:

Source_WorkBook.Sheets(SheetName).Copy(Target_WorkBook.Worksheets(Target_WorkBook.Worksheets.Count))

Harsh Athalye
India.
"Nothing is Impossible"

srxr9
Starting Member

15 Posts

Posted - 2006-10-05 : 12:53:50

Thanks Harsh, it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources