Calling a Macro from a ActiveX Task in DTS

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

SQL Server 2000 Forums

SQL Server Development (2000)

Calling a Macro from a ActiveX Task in DTS

Author

Topic

mcrors_calhoun
Starting Member

22 Posts

Posted - 2006-10-18 : 05:46:37

Hi

I have an created an excel sheet, and populated it usinf an ActiveX Task in a DTS package. I now want to format some of the sheets in a specific way. To do this I have created a Macro that will be run from the DTS package once the excel sheets have been created and populated.

I start of by creating an excel object and then opening a workbook in that object

fullfilename = sourceFolder + file.Name
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open(fullfilename)

I then want to call the macro on that workbook.
I was thinking of something like this

objWorkbook.item("macro1").activate

But I am getting an error that this object does not support this method. I was wondering if anyone knows which method I should use to call this macro.

Thanks

mcrors_calhoun
Starting Member

22 Posts

Posted - 2006-10-18 : 06:43:07

I have updated my ActiveX code and it now reads

Set objExcel = CreateObject("EXCEL.APPLICATION")

objExcel.Workbooks.Open(fullfilename)
objExcel.Run(fullfilename + "!Module1.Macro1")
objExcel.ActiveWorkbook.Save

But I am getting an error saying that it cannot find my macro.
Am I using the right format for passing a parameter to Run()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources