How do i run excel macro in DTS

[FulFill]

Hi,

I have created excel macro to run and dump data into diff workbook1. It works great when I open excel macro by itself dumps data and closes fine, but I want to run this macro in DTS to dump new data into workbook1 please help me this is urgent.

Thank you in advance
P

 

[TheJon]

I am sure that DTS would be better employed reading your data from one excel workbook, transforming it in temporary tables using SQL, then writing to another workbook.

But, as you have invested time in creating your macro, I would suggest creating an autorun macro to run your macro when the excel workbook is opened, which would then need to close excel after running (see Excel help).

You will then need to create an "Execute Process Task" in your DTS package with a Win32 Process entry of "C:\Program Files\Microsoft Office\Excel.exe c:\myDirectory\myExcelFileWithMacro.xls" to automatically open your excel macro. I haven't tried this, and I don't know if it will keep excel open in the background, you may need to use ActiveX and VBScript instead. And if you are scheduling this, you will need Excel installed on your SQL server. This process can be also be done using the command scheduler in windows, though.

Unless you are transforming data with SQL server, using DTS may not be the best answer to your problem.

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]