how can i refresh a pivot table using tsql 2000

Posted on 2009-04-21
Medium Priority
Last Modified: 2012-05-06
how can i refresh a pivot table using tsql 2000. I know it can be done using the oacreate stored proc or similar but im after some guidance or useful code.
Question by:9772885
  • 2
LVL 15

Expert Comment

ID: 24201563
Refresh a pivot table? You mean in Excel?

Or you want to have a query which returns a set of results with dynamic columns?

LVL 51

Expert Comment

by:Mark Wills
ID: 24201748
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

LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 24221499
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


Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question