Clear OLAP Pivot table cache

I am trying to clear the pivot cache from an OLAP pivot table. Tried the following VBA Code Private Sub Workbook_Open() Dim xPt As PivotTable Dim xWs As Worksheet Dim xPc As P...

excel

Macros and VBA

NikolinoDE

Platinum Contributor

Aug 12, 2023

Akash_Mathew

Clearing the pivot cache of an OLAP pivot table is a bit different from a traditional pivot table because OLAP pivot tables retrieve data directly from a data source (like an Analysis Services cube) rather than storing the data within the Excel workbook's cache. As a result, you cannot refresh or clear the cache in the same way you would for non-OLAP pivot tables.

In the case of OLAP pivot tables, you need to focus on refreshing the data itself, rather than trying to clear a cache that does not exist. The code you provided is attempting to refresh a nonexistent cache, which is why you are encountering runtime errors.

To refresh an OLAP pivot table's data, you can use the .RefreshTable method. Here is how you could modify your code to refresh the OLAP pivot tables:

Vba code:

Private Sub Workbook_Open()
    Dim xPt As PivotTable
    Dim xWs As Worksheet
    Application.ScreenUpdating = False
    For Each xWs In ActiveWorkbook.Worksheets
        For Each xPt In xWs.PivotTables
            If xPt.SourceType = xlExternal Then ' Check if the pivot table is an OLAP pivot table
                xPt.RefreshTable
            End If
        Next xPt
    Next xWs
    Application.ScreenUpdating = True
End Sub

In this code, the SourceType property is used to check whether a pivot table is an OLAP pivot table (xlExternal) before attempting to refresh it.

Keep in mind that refreshing data in OLAP pivot tables may involve authentication and connectivity to the data source. Make sure the users running the code have the necessary permissions and connectivity to refresh the data from the OLAP source.

My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.

SergeiBaklan
Diamond Contributor
Aug 12, 2023
NikolinoDE

Perhaps you are right, I have no idea how PivotTable cache works with OLAP connection, but it exists. You may create PivotTable based on data model and check the cache as

Sub CheckPivotCache() ' Determine if PivotCache has OLAP connection. If Application.ActiveWorkbook.PivotCaches.Item(1).OLAP = True Then MsgBox "The PivotCache is connected to an OLAP server" Else MsgBox "The PivotCache is not connected to an OLAP server." End If End Sub

I guess mechanisms are different for cached PivotTable and data model PivotTable. Perhaps in the latest we try not to keep data in data model with file. Have no idea how to do that with VBA.

Forum Discussion

Clear OLAP Pivot table cache

Resources