Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • 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.
0
9772885
Asked:
9772885
  • 4
  • 3
1 Solution
 
SharathData EngineerCommented:
provide more information like your table structure, sample data from your table and the expected result.
0
 
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
0
 
SharathData EngineerCommented:
Mark - do you have an english version of the URL, you have provided?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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'

0
 
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 
BEGIN
    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'
END
 
 
IF @status=0 
and @editing = 1
BEGIN
    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'
END
 
IF @editing > 0
BEGIN
    EXEC sp_OAMethod @Excel, 'ActiveWorkbook.Save'
    EXEC sp_OAMethod @Excel, 'Workbooks.Close'
END
 
EXEC sp_OAMethod @Excel, 'Close' 
  
IF @status <> 0
BEGIN
    set @errormessage = @errormessage + '  Status Code =' +convert(varchar,@status) 
    raiserror (@ErrorMessage, 16, 1)
END
 
EXEC sp_OADestroy @Excel
EXEC sp_OADestroy @WorkSheet
EXEC sp_OADestroy @WorkBook

Open in new window

Pivot-Example.xls
0
 
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.
0
 
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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