Class PivotCache (Excel VBA)

The class PivotCache represents the memory cache for a PivotTable report.

Set

To use a PivotCache class variable it first needs to be instantiated, for example

Dim wb As Workbook: Set wb = 
Dim pvtcacCreate As PivotCache
Set pvtcacCreate = wb.PivotCaches.Create(SourceType:=xlConsolidation)

The following procedures can be used to set variables of type PivotCache: PivotCaches.Item, PivotCaches.Create, PivotTable.PivotCache and Workbook.PivotCaches

For Each

Here is an example of processing the PivotCache items in a collection.

Dim wb As Workbook: Set wb = 
Dim pvtcacPivotCache As PivotCache
For Each pvtcacPivotCache In wb.PivotCaches()
	
Next pvtcacPivotCache

Themes

The following procedures in this class have been grouped together and are described on a separate theme page

Create with its procedures CreatePivotChart, CreatePivotTable and PivotCaches.Create

Methods

Other Methods

SaveAsODC - Saves the PivotTable cache source as a Microsoft Office Data Connection file.

Sub UseSaveAsODC() 
 
 Application.ActiveWorkbook.PivotCaches.Item(1).SaveAsODC ("ODCFile") 
 
End Sub

Properties

ADOConnection returns an ADO Connection adoconnection if the PivotTable cache is connected to an OLE DB data source. The ADOConnection property exposes the Microsoft Excel connection to the data provider, allowing the user to write code within the context of the same session that Excel is using with ADO (relational source) or ADO MD (OLAP source). Read-only.

Sub UseADOConnection() 
 
 Dim ptOne As PivotTable 
 Dim cmdOne As New ADODB.Command 
 Dim cfOne As CubeField 
 
 Set ptOne = Sheet1.PivotTables(1) 
 ptOne.PivotCache.MaintainConnection = True 
 Set cmdOne.ActiveConnection = ptOne.PivotCache.ADOConnection 
 
 ptOne.PivotCache.MakeConnection 
 
 ' Create a set. 
 cmdOne.CommandText = "Create Set [Warehouse].[My Set] as '{[Product].[All Products].Children}'" 
 cmdOne.CommandType = adCmdUnknown 
 cmdOne.Execute 
 
 ' Add a set to the CubeField. 
 Set cfOne = ptOne.CubeFields.AddSet("My Set", "My Set") 
 
End Sub

BackgroundQuery true if queries for the PivotTable report are performed asynchronously (in the background).

Worksheets(1).PivotTables("Pivot1") _ 
 .PivotCache.BackgroundQuery = True

CommandText returns or sets the command string for the specified data source. Read/write Variant.

CommandType returns or sets one of these XlCmdType constants: xlCmdCube, xlCmdDefault, xlCmdSql, or xlCmdTable.

Set qtQtrResults = _ 
 Workbooks(1).Worksheets(1).QueryTables(1) 
With qtQtrResults 
 .CommandType = xlCmdSQL 
 .CommandText = _ 
 "Select ProductID From Products Where ProductID < 10" 
 .Refresh 
End With

Connection returns or sets a string that contains one of the following:.

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) 
 .Connection = _ 
 "OLEDB;Provider=MSOLAP;Location=srvdata;Initial Catalog=National" 
 .MaintainConnection = True 
 .CreatePivotTable TableDestination:=Range("A3"), _ 
 TableName:= "PivotTable1" 
End With 
With ActiveSheet.PivotTables("PivotTable1") 
 .SmallGrid = False 
 .PivotCache.RefreshPeriod = 0 
 With .CubeFields("[state]") 
 .Orientation = xlColumnField 
 .Position = 0 
 End With 
 With .CubeFields("[Measures].[Count Of au_id]") 
 .Orientation = xlDataField 
 .Position = 0 
 End With 
End With

EnableRefresh true if the PivotTable cache or query table can be refreshed by the user. The default value is True.

Worksheets(1).PivotTables("Pivot1") _ 
 .PivotCache.EnableRefresh = False

Index returns a Long value that represents the index number of the object within the collection of similar objects.

IsConnected returns True if the MaintainConnection property is True, and the PivotTable cache is currently connected to its source. Returns False if it is not currently connected to its source.

Sub CheckIsConnected() 
 
 ' Handle run-time error if external source is not OLE DB. 
 On Error GoTo Not_OLEDB 
 
 ' Check connection setting and notify the user accordingly. 
 If Application.ActiveWorkbook.PivotCaches.Item(1).IsConnected = True Then 
 MsgBox "The PivotCache is currently connected to its source." 
 Else 
 MsgBox "The PivotCache is not currently connected to its source." 
 End If 
 Exit Sub 
 
Not_OLEDB: 
 MsgBox "The data source is not an OLE DB data source." 
 
End Sub

LocalConnection returns or sets the connection string to an offline cube file. Read/write String.

With ActiveWorkbook.PivotCaches(1) 
 .LocalConnection = _ 
 "OLEDB;Provider=MSOLAP;Data Source=C:\Data\DataCube.cub" 
 .UseLocalConnection = True 
End With 

MaintainConnection true if the connection to the specified data source is maintained after the refresh and until the workbook is closed. The default value is True.

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) 
 .Connection = _ 
 "OLEDB;Provider=MSOLAP;Location=srvdata;Initial Catalog=National" 
 .MaintainConnection = False 
 .CreatePivotTable TableDestination:=Range("A3"), _ 
 TableName:= "PivotTable1" 
End With 
With ActiveSheet.PivotTables("PivotTable1") 
 .SmallGrid = False 
 .PivotCache.RefreshPeriod = 0 
 With .CubeFields("[state]") 
 .Orientation = xlColumnField 
 .Position = 0 
 End With 
 With .CubeFields("[Measures].[Count Of au_id]") 
 .Orientation = xlDataField 
 .Position = 0 
 End With 
End With

MemoryUsed returns the amount of memory currently being used by the object, in bytes.

MsgBox "Microsoft Excel is currently using " & _ 
 Application.MemoryUsed & " bytes"

MissingItemsLimit returns or sets the maximum quantity of unique items per PivotTable field that are retained even when they have no supporting data in the cache records.

Sub CheckMissingItemsList() 
 
 Dim pvtCache As PivotCache 
 
 Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1) 
 
 ' Determine the maximum number of unique items allowed per PivotField and notify the user. 
 Select Case pvtCache.MissingItemsLimit 
 Case xlMissingItemsDefault 
 MsgBox "The default value of unique items per PivotField is allowed." 
 Case xlMissingItemsMax 
 MsgBox "The maximum value of unique items per PivotField is allowed." 
 Case xlMissingItemsNone 
 MsgBox "No unique items per PivotField are allowed." 
 End Select 
 
End Sub

OLAP returns True if the PivotTable cache is connected to an Online Analytical Processing (OLAP) server.

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

OptimizeCache true if the PivotTable cache is optimized when it's constructed. The default value is False.

Worksheets(1).PivotTables("Pivot1") _ 
 .PivotCache.OptimizeCache = True

Parent returns the parent object for the specified object. Read-only.

QueryType indicates the type of query used by Microsoft Excel to populate the PivotTable cache.

RecordCount returns the number of records in the PivotTable cache or the number of cache records that contain the specified item.

MsgBox Worksheets(1).PivotTables("Pivot1") _ 
 .PivotFields("Product").PivotItems("Kiwi").RecordCount

RefreshDate returns the date on which the cache was last refreshed.

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable 
dateString = Format(pvtTable.RefreshDate, "Long Date") 
MsgBox "The data was last refreshed on " & dateString

RefreshName returns the name of the person who last refreshed the PivotTable cache.

RefreshOnFileOpen true if the PivotTable cache is automatically updated each time the workbook is opened. The default value is False.

ActiveWorkbook.PivotCaches(1).RefreshOnFileOpen = True

RefreshPeriod returns or sets the number of minutes between refreshes.

Set objPC = Worksheets("Sheet1").PivotTables("PivotTable3").PivotCache 
objPC.RefreshPeriod = 15

RobustConnect returns or sets how the PivotTable cache connects to its data source.

Sub CheckRobustConnect() 
 
 Dim pvtCache As PivotCache 
 
 Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1) 
 
 ' Determine the connection robustness and notify user. 
 Select Case pvtCache.RobustConnect 
 Case xlAlways 
 MsgBox "The PivotTable cache is always connected to its source." 
 Case xlAsRequired 
 MsgBox "The PivotTable cache is connected to its source as required." 
 Case xlNever 
 MsgBox "The PivotTable cache is never connected to its source." 
 End Select 
 
End Sub

SavePassword true if password information in an ODBC connection string is saved with the specified query. False if the password is removed.

SourceConnectionFile returns or sets a String indicating the Microsoft Office Data Connection file or similar file that was used to create the PivotTable.

Sub CheckSourceConnection() 
 
 Dim pvtCache As PivotCache 
 
 Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1) 
 
 On Error GoTo No_Connection 
 
 MsgBox "The source connection is: " & pvtCache.SourceConnectionFile 
 Exit Sub 
 
No_Connection: 
 MsgBox "PivotCache source can not be determined." 
 
End Sub

SourceData returns the data source for the PivotTable report, as shown in the following table.

Set newSheet = ActiveWorkbook.Worksheets.Add 
sdArray = Worksheets("Sheet1").UsedRange.PivotTable.SourceData 
For i = LBound(sdArray) To UBound(sdArray) 
 newSheet.Cells(i, 1) = sdArray(i) 
Next i 

SourceDataFile returns a String value that indicates the source data file for the cache of the PivotTable.

Sub CheckSourceConnection() 
 
 Dim pvtCache As PivotCache 
 
 Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1) 
 
 On Error GoTo No_Connection 
 
 MsgBox "The data source connection is: " & _ 
 pvtCache.SourceDataFile 
 Exit Sub 
 
No_Connection: 
 MsgBox "PivotCache source cannot be determined." 
 
End Sub

SourceType returns an XlPivotTableSourceType value that represents the type of item being published.

UpgradeOnRefresh contains information on whether to upgrade the PivotCache and all connected PivotTables on the next refresh.

UseLocalConnection returns True if the LocalConnection property is used to specify the string that enables Microsoft Excel to connect to a data source. Returns False if the connection string specified by the Connection property is used.

With ActiveWorkbook.PivotCaches(1) 
 .LocalConnection = _ 
 "OLEDB;Provider=MSOLAP;Data Source=C:\Data\DataCube.cub" 
 .UseLocalConnection = True 
End With 
 

Version returns the version of Microsoft Excel in which the PivotCache was created.

WorkbookConnection establishes a connection between the current workbook and the PivotCache object.

PivotCaches.Count returns a Long value that represents the number of objects in the collection.

PivotCaches.Parent returns the parent object for the specified object. Read-only.