HOw use DTS call the VBA code execute?

[andy5323]

Hi all,

I have some problem to need you all help. My problem is i have a Excel Worksheet with VBA coding, in VBA code it have modules,function and sub procedures. after that, i change the VBA coding in add-in file and save as .xla to make the user friendly.

But now, i need to use DTS to automatic to execute the excel file that contain VBA code every week. This is because the VBA in excel cannot automation excute by every week. So, i need use DTS to make it automation. The problem is i don't know how to use the DTS function to call the addin excel to execute? Which the connection and task compenent should i use? Or how do i paste the VBA code in DTS?

DTS is very new for me, are any help can provide me the solution? thanks

Regards,
Andy

[rmiao]

Why don't schedule it with windows task scheduler?

[skasarla]

I am not sure how a VBA file will be executed in Windows Task Scheduler. But any thing that can be executed in a batch file can be run from a DTS or SQL job as sqlcmdexec. You can try to execute VBA from an Activx Script and use that in DTS Activex Script task.

[rmiao]

Anything that can be executed in a batch file can be run with windows task scheduler.

[andy5323]

Hi rmiao,skasarla,

Thanks for reply. Actually, how to use windows task scheduler? I didn't find it in DTS component. Can teach me the step?

Thanks,
Andy

[MAK]

Click Start-settings-control panel-Scheduled Tasks
Click add task. It is a wizard.