Odd Question

Does anyone know of a way to call an Exell macro in a Sql Server DTS package?
Regards
jimI assume that you saved your package as a Visual Basic File
(Enterprise Manager->Package->Save as: Location->Visual Basic File)
Once having the VB code for the package, you can insert there the appropiate code to run the macro:

mySum = Application.Run("MyCustom.xlm!My_Func_Sum", 1, 5)
MsgBox "Macro result: " & mySum

Of course you'll need to define an Excel object, open it and work with the object properties, but basically that's the idea.

Originally posted by JDionne
Does anyone know of a way to call an Exell macro in a Sql Server DTS package?
Regards
jim|||Originally posted by dbadelphes
I assume that you saved your package as a Visual Basic File
(Enterprise Manager->Package->Save as: Location->Visual Basic File)
Once having the VB code for the package, you can insert there the appropiate code to run the macro:

mySum = Application.Run("MyCustom.xlm!My_Func_Sum", 1, 5)
MsgBox "Macro result: " & mySum

Of course you'll need to define an Excel object, open it and work with the object properties, but basically that's the idea.

hmmm thats a bit more than i understand. is there any documentation on that that i can read?
Jim|||Search for "Saving a DTS Package to a Visual Basic File" in Books Online. It's pretty well documented, then for macros look in MSDN documentation on Microsoft's site (check Q219905 - How to Dynamically Add and Run a VBA Macro from Visual Basic).

Hope it helps.

Originally posted by JDionne
hmmm thats a bit more than i understand. is there any documentation on that that i can read?
Jim