Hi,

Is there any way to take the result set of a query, run excel, and create pivot table from the results for a user to manipulate and save?

I have been looking for a solution like this....


Ron Sissons, DBA
Information Technology Services
Riverside County Office of Education
3939 Thirteenth Street, Riverside, CA 92502-0868
Telephone: (909) 826-6471; FAX: [909] 826-6451

Hi Ron,

I would use VBA and Automation from within either Access or Excel to connect
to the SQL Server, run the query, and return the result set and populate an
Excel spreadsheet and then apply pivot table methods to the data.

I've created a number of Access-based applets for our accountants that pull
data from our production SQL Server and dump it into some Access tables which
I can then use as a staging area to apply different kinds of logic using VBA
and Access queries. When finished applying logic I use automation to create
an Excel spreadsheet, populate it, and then apply some grouping, outlining,
and general formatting. Pivot table functioanlity should be fully exposed to
automation.

If you don't want any end-user interaction I suppose you could script out
the automation stuff into VBScript and use an ActiveX Script task in DTS to
run the code.

Hope this helps.
Brian Nadel
DBA/Sys Admin
Freedom Communications, Inc.


"Ron Sissons" wrote: