Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Button to refresh a PivotTable (Read 9945 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Button to refresh a PivotTable
22.10.08 at 22:24:56
 
Button to refresh a PivotTable
 
Quote:
How do I specify a target of PivotTable?
I want to create a ommand button tht refreshes a Pivot Table

 
Please use the following procedure to make the macro code:
 
1. start macro recorder
2. execute the select statement, set output target as the pivot table
3. stop the macro recorder
 
This will result in a macro that creates/refreshes the pivot table.
 
Note that refreshing the pivot table can manually be done by putting the cursor in the top left cell of the pivot table and pressing the refresh queries button. SQL*XL will load the SQL back into the SQL dialog and preset the Resultset dialog to update the pivot table.  
 
Rerunning the macro will refresh the pivot table.
 
Use Excel to make the button and to assign the macro. You can read the following topic about this:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1133092522
Back to top
 
« Last Edit: 28.09.09 at 11:24:21 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Button to refresh a PivotTable
Reply #1 - 28.09.09 at 11:27:02
 
Please note that to refresh the query behind a pivot table the refresh queries feature should be used. Refresh queries recognises that the SQL is part of the pivot table and will preset the output target to the pivot table. When the SQL dialog is opened having the pivot table selected, the SQL will be loaded into the editor but SQL*XL will execute it as a new statement. A warning will be given that the results are going to be placed in a cell that is part of a pivot table.
 
To refresh a pivot table use the refresh queries option (button on the toolbar or ribbon).
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1