?
Solved

how can i refresh a pivot table using tsql 2000

Posted on 2009-04-21
3
Medium Priority
?
309 Views
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.
0
Comment
Question by:9772885
  • 2
3 Comments
 
LVL 15

Expert Comment

by:rob_farley
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?

Rob
0
 
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

0
 
LVL 51

Accepted Solution

by:
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 
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

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