9772885
asked on
Refresh Excel Pivot Table From TSQL 2000
I would like to refresh a pivot table using tsql 2000. I believe this is done using a oacreate stored procedure or similar and i was wondeirng if anyone had any code they could help me with.
Sharath S
provide more information like your table structure, sample data from your table and the expected result.
Did exactly this not so long ago using OA procedures. Ended up being part of a knowledge base SQL User group website (italian), should be easy enough to follow : http://www.ugiss.org/Content/Article/Aggiornare-una-tabella-pivot-di-Microsoft-Excel.aspx
Mark - do you have an english version of the URL, you have provided?
Unfortunately no. And I was a little surprised when the asker posted their final version...
The original Stored Procedure didn't have too many comments, and the code itself should be readily understood.
It is easy enough if you follow the steps :
1) create excel object
2) attach / open to excel workbook
3) attach to excel worksheet (make it the active object)
4) attach to pivot (part of the refresh below, not a seperate step per se)
5) refresh pivot
6) save pivot
7) save workbook
8) close workbook
with the key commands for the pivot refresh being :
exec @error = sp_OAMethod @objWorkSheet, 'PivotTables("My_Pivot_Nam
exec @error = sp_OAMethod @objWorkSheet, 'PivotTables("My_Pivot_Nam
The original Stored Procedure didn't have too many comments, and the code itself should be readily understood.
It is easy enough if you follow the steps :
1) create excel object
2) attach / open to excel workbook
3) attach to excel worksheet (make it the active object)
4) attach to pivot (part of the refresh below, not a seperate step per se)
5) refresh pivot
6) save pivot
7) save workbook
8) close workbook
with the key commands for the pivot refresh being :
exec @error = sp_OAMethod @objWorkSheet, 'PivotTables("My_Pivot_Nam
exec @error = sp_OAMethod @objWorkSheet, 'PivotTables("My_Pivot_Nam
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Mark - you are amazing. I learned new concept today. I am not aware of these SPs (sp_OACraete, sp_OADestroy and sp_OAMethod) till now. I tested your solution and will spend some time in learning these new SPs.
Thanks Sharath. You can do a heck of a lot with OA procedures - and I do mean one heck of a lot (including generating scripts of Data structues using SQLDMO). Hugely undocumented, and a very powerful part of SQL.