output data into Excel Template

[jhall01]

Challenge:

Get a daily report for upper management in a specific format.

What I have:

I have a excel template with charts and tables. It takes aprox 5 minutes for each query I use to fill the tables that drive the charts. There are 5 tables to fill. Normally I would create an ODBC connection to the SQL server to fill the data using ADO. but since the queries take so long this is not feasible. I want SQL to run the queries and fill a template with the necessary information and save it to a share drive with a unique name early in the morning....4 AM.

I have the t-SQL queries written and work with DTS quite often. I have several Local Packages that utilize ActiveX scripts so I am very familiar with VBS.

I don't however, know how to get the info into a excel template using DTS so I can have it run as a Job 5 days a week.

Any Ideas or links to help me with a solution?

Thanks,

Jon

 

[cbhnp]

I would suggest combining your approaches. At 4 AM run your queries to populate SQL tables with your report data. Set permanent links in your templates (via ADO or embedded query) to display the report data in your charts and tables. Now the templates only need to access a single table and should update very quickly.

cb