• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 525
  • Last Modified:

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.
  • 4
  • 3
1 Solution
SharathData EngineerCommented:
provide more information like your table structure, sample data from your table and the expected result.
Mark WillsTopic AdvisorCommented:
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
SharathData EngineerCommented:
Mark - do you have an english version of the URL, you have provided?
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Mark WillsTopic AdvisorCommented:
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_Name").RefreshTable'
   exec @error = sp_OAMethod @objWorkSheet, 'PivotTables("My_Pivot_Name").SaveData'

Mark WillsTopic AdvisorCommented:
OK, maybe this will make it easier....

Simply create a folder C:\ee
and save the attached spreadsheet exactly as is into that folder.

Open it have a look at the work book - two worksheets 1st is the Pivot, 2nd is the data source for that pivot.

You will see a value of 22222.22 in Feb in the Pivot table. We will change that value on the source data and then refresh that pivot.

The Pivot could easily be directly off a database (in which case you just need the refresh part, not the changing of the value).

NOW CLOSE THE WORKBOOK. otherwise you might get a sharing violation.

Then run the code below, and re-open the spreadsheet.

DECLARE @FileName varchar(512), 
        @status int,
        @Excel int,
        @WorkBook int,
        @WorkSheet int,
        @WorksheetIndex int,
        @ErrorMessage varchar(255),
        @editing int
SET @filename = 'c:\ee\pivot-example.xls'        -- excel filename
SET @editing = 0
SET @ErrorMessage = 'Trying to Update Spreadsheet ' + @filename          -- of course you should put more controls in...
EXEC @status = sp_OACreate 'Excel.Application', @Excel output
IF @status=0 EXEC @status = sp_OAMethod @Excel, 'WorkBooks.Open', @WorkBook output, @FileName
IF @status=0 
    SET @editing = 1
    IF @status=0 EXEC @status = sp_OAMethod @WorkBook, 'Worksheets.Item', @WorkSheet output, 2      --change data on data worksheet - which is sheet 2
    IF @status=0 EXEC @status = sp_OAMethod @WorkSheet, 'Activate'
    IF @status=0 EXEC @status = sp_OASetProperty @WorkSheet, 'Range("D3:D3").font.bold', 1 
    IF @status=0 EXEC @status = sp_OASetProperty @WorkSheet, 'Range("D3").Value', '=21212.12'
IF @status=0 
and @editing = 1
    SET @editing = 2
    IF @status=0 EXEC @status = sp_OAMethod @WorkBook, 'Worksheets.Item', @WorkSheet output, 1      --update pivot on pivot worksheet - which is sheet 1
    IF @status=0 EXEC @status = sp_OAMethod @WorkSheet, 'Activate'
    IF @status=0 EXEC @status = sp_OAMethod @WorkSheet, 'PivotTables(1).RefreshTable'
    IF @status=0 EXEC @status = sp_OAMethod @WorkSheet, 'PivotTables(1).SaveData'
IF @editing > 0
    EXEC sp_OAMethod @Excel, 'ActiveWorkbook.Save'
    EXEC sp_OAMethod @Excel, 'Workbooks.Close'
EXEC sp_OAMethod @Excel, 'Close' 
IF @status <> 0
    set @errormessage = @errormessage + '  Status Code =' +convert(varchar,@status) 
    raiserror (@ErrorMessage, 16, 1)
EXEC sp_OADestroy @Excel
EXEC sp_OADestroy @WorkSheet
EXEC sp_OADestroy @WorkBook

Open in new window

SharathData EngineerCommented:
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.
Mark WillsTopic AdvisorCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now