Automation from Server Side Script

[John]

Hi,

I am trying to develop a procedure for pulishing excel
pivot table reports via scripts running on an NT4 server
using data from SQL Server 2000. I was able to set up a
dts package to export the data to a spreadsheet using the
excel odbc driver and have a pivot table refresh on open
from the imported data, but I would like to be able to
load the data directly to the pivot cache to keep the file
size to a minimum.

The current report I'm working on is 2M with pivot table
and data saved with the pivot table, but add in the
worksheet I'm using to import the data to populate the
pivot table and the size increases to 5M.

I know I could use an external data source within excel
and have it refresh on open, but then the same data will
be pulled out of sql server across the network every time
someone views the report. I only want to load the data
once.

I'm thinking I'd have to load excel on the server to get
programmatic access to its objects, but I'm not sure the
server admins would go for that.

Any thoughts?

TIA,

John