Refresh an OLAP PivotTable with VBA in WPS Office Spreadsheets
Question details
The user needs to refresh an OLAP PivotTable through VBA and remove stale items, without using the PivotCache clearing method that works for standard PivotTables.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has existing VBA that sets MissingItemsLimit and refreshes PivotCaches. It works for standard PivotTables but errors on an OLAP PivotTable, so they need a reliable way to refresh connected OLAP data.
- Observed behavior
- The standard VBA approach that clears or refreshes the workbook PivotCache causes an error when applied to an OLAP PivotTable.
Confirm that the OLAP PivotTable is connected to an accessible external data source and that you have permissions to refresh it. Open the workbook in WPS Office Spreadsheets and enable VBA macros if prompted.
Refresh an OLAP PivotTable with VBA Using SourceType and RefreshTable
Use this solution when you want to automate the refresh of externally sourced PivotTables and avoid the cache-clearing code that fails with OLAP data sources.
OLAP PivotTables retrieve data directly from an external source such as an Analysis Services cube, so they do not use the workbook cache in the same way as standard PivotTables. Instead of clearing PivotCaches, check the SourceType property and call RefreshTable only for external PivotTables.
Use the Workbook_Open procedure to refresh all external PivotTables whenever the workbook opens, or adapt the code to run from a macro button. The refresh still requires access, authentication, and permissions for the OLAP data source.
In WPS Office Spreadsheets, click the Developer tab and then click View Code, or press Alt+F11 to open the VBA editor.
In the Project Explorer, double-click ThisWorkbook, paste the macro, and confirm it loops through ActiveWorkbook.Worksheets and each worksheet PivotTables collection.
Inside the loop, use If xPt.SourceType = xlExternal Then xPt.RefreshTable to refresh only externally sourced OLAP PivotTables.
Save the workbook in a macro-enabled format, then reopen it to run the Workbook_Open refresh or run the macro manually from the Developer tab.
Manually Refresh an OLAP PivotTable in WPS Office Spreadsheets
Use this method for a one-time update when you prefer not to run VBA.
Refresh OLAP PivotTables Automatically with WPS Office Spreadsheets VBA
Use the built-in VBA editor in WPS Office Spreadsheets to loop through externally sourced PivotTables and call RefreshTable directly, avoiding the workbook cache methods that fail with OLAP data sources.
- 1. Enable macros: Open WPS Office Spreadsheets and enable macros from the Developer tab or trust center settings.
- 2. Open the VBA editor: Press Alt+F11 to open the VBA editor and double-click ThisWorkbook in the Project Explorer.
- 3. Insert the refresh code: Paste the Workbook_Open code that loops through worksheets and refreshes each PivotTable where SourceType equals xlExternal.
- 4. Run or reopen the workbook: Save the workbook in a macro-enabled format, then reopen it to run the refresh automatically or run the macro manually.
Frequently Asked Questions
Why does clearing PivotCaches fail for an OLAP PivotTable?
OLAP PivotTables connect directly to an external cube or Analysis Services source rather than storing all source data in the workbook cache. Use the SourceType and RefreshTable approach instead of clearing the cache.
How can I identify an externally sourced PivotTable in VBA?
Check the PivotTable.SourceType property. For OLAP and other external PivotTables, SourceType returns xlExternal, so you can conditionally call RefreshTable.
Does refreshing an OLAP PivotTable require extra permissions?
Yes. You need access to the OLAP data source, valid authentication, and permissions to query or refresh the cube. The refresh will fail if the connection is unavailable.
Can I run this VBA refresh in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports VBA macro editing. Open the VBA editor, add the macro to ThisWorkbook, and save the workbook in a macro-enabled format.
What should I do if stale OLAP items still appear after refreshing?
If RefreshTable succeeds but stale items remain, check the OLAP cube, dimension, or measure group configuration on the server, and make sure the connection points to the updated database or analysis database.
