Re: EXCEL workbooks Refresh from DTS

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Ed Enstrom (spambait_at_domain.invalid.net)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 00:04:10 GMT

"Kanan" <Kanan@discussions.microsoft.com> wrote in message
news:356568F6-7168-4ABB-A158-AC0B4F086F60@microsoft.com...
> hi,
> I would like to know if DTS is a good way to open EXCEL workbooks in a
> folder
> and refresh the workbooks (VBA code in WOrkbook to extract data from a SQL
> Server database) by running a macro. I need to do this because I need to
> schedule
> this refresh task on a weekly,monthly basis? I would appreciate if
> somebody
> can
> post sample code on opening and running a macro in an EXCEL workbook.
> Kanan

Is it necessary to update an existing workbook or can you create a new one
of the same name for every report period?

I create report workbooks weekly and monthly. I keep a blank version of
each workbook in a template folder. There is only one *** (a title page)
in them. To create the new report, I use FTP task to copy the template
workbook to the report folder and over-write the old one. I then run a SQL
task to create the table (work***) and then a data pump to fill in the
data. Simple and straightforward and I don't have to muck around with VBA.
I control everything from inside DTS.

Quantcast