SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 ASP.NET
 Combining workbooks
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

srxr9
Starting Member

15 Posts

Posted - 10/05/2006 :  12:21:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5564 Posts

Posted - 10/05/2006 :  12:46:10  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

srxr9
Starting Member

15 Posts

Posted - 10/05/2006 :  12:53:50  Show Profile  Reply with Quote
Thanks Harsh, it works.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000