PivotCache PivotCache PivotCache Interface


Represents the memory cache for a PivotTable report.

public interface class PivotCache
public interface PivotCache
Public Interface PivotCache


The PivotCache object is a member of the PivotCaches collection.

Use the PivotCache() method to return a PivotCache object for a PivotTable report (each report has only one cache).

Use PivotCaches(index), where index is the PivotTable cache number, to return a single PivotCache object from the PivotCaches collection for a workbook.


ADOConnection ADOConnection ADOConnection

Returns an ADO connection object if the PivotTable cache is connected to an OLE DB data source.

Application Application Application

Returns an Application object that represents the Microsoft Excel application.

BackgroundQuery BackgroundQuery BackgroundQuery

True if queries for the PivotTable report or query table are performed asynchronously (in the background).

CommandText CommandText CommandText

Returns or sets the command string for the specified data source.

CommandType CommandType CommandType

Returns or sets a constant describing the value type of the CommandText property. The default value is xlCmdSQL.

Connection Connection Connection

Returns or sets connection information for a PivotTable cache.

Creator Creator Creator

Returns a 32-bit integer that indicates the application in which this object was created.

EnableRefresh EnableRefresh EnableRefresh

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

Index Index Index

Returns the index number of the object within the collection of similar objects.

IsConnected IsConnected 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.

LocalConnection LocalConnection LocalConnection

Returns or sets the connection string to an offline cube file.

MaintainConnection MaintainConnection 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.

MemoryUsed MemoryUsed MemoryUsed

Returns the amount of memory in bytes currently being used by the object.

MissingItemsLimit MissingItemsLimit 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.


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

OptimizeCache OptimizeCache OptimizeCache

True if the PivotTable cache is optimized when it’s constructed. The default value is False.

Parent Parent Parent

Returns the parent object for the specified object.

QueryType QueryType QueryType

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

RecordCount RecordCount RecordCount

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

Recordset Recordset Recordset

Returns or sets a Recordset object that’s used as the data source for the specified query table or PivotTable cache.

RefreshDate RefreshDate RefreshDate

Returns the date on which the PivotTable cache was last refreshed.

RefreshName RefreshName RefreshName

Returns the name of the person who last refreshed the PivotTable cache.

RefreshOnFileOpen RefreshOnFileOpen RefreshOnFileOpen

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

RefreshPeriod RefreshPeriod RefreshPeriod

Returns or sets the number of minutes between refreshes.

RobustConnect RobustConnect RobustConnect

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

SavePassword SavePassword SavePassword

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

SourceConnectionFile SourceConnectionFile SourceConnectionFile

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

SourceData SourceData SourceData

Returns the data source for the PivotTable report.

SourceDataFile SourceDataFile SourceDataFile

Returns a String indicating the source data file for the cache of the PivotTable.

SourceType SourceType SourceType

Returns a value that identifies the type of item being published.

Sql Sql Sql

Reserved for internal use.

UpgradeOnRefresh UpgradeOnRefresh UpgradeOnRefresh

Contains information on whether to upgrade the PivotCache and all connected PivotTables on the next refresh. Read/write.

UseLocalConnection UseLocalConnection UseLocalConnection

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

Version Version Version

Returns the version of Microsoft Excel in which the PivotCache was created. Read-only.

WorkbookConnection WorkbookConnection WorkbookConnection

Establishes a connection between the current workbook and the PivotCache object. Read-only.


CreatePivotChart(Object, Object, Object, Object, Object, Object) CreatePivotChart(Object, Object, Object, Object, Object, Object) CreatePivotChart(Object, Object, Object, Object, Object, Object)

Creates a standalone PivotChart from a PivotCache object. A Shape object is returned.

CreatePivotTable(Object, Object, Object, Object) CreatePivotTable(Object, Object, Object, Object) CreatePivotTable(Object, Object, Object, Object)

Creates a PivotTable report based on a PivotCache object.

MakeConnection() MakeConnection() MakeConnection()

Establishes a connection for the specified PivotTable cache.

Refresh() Refresh() Refresh()

Updates the cache of the PivotTable object.

ResetTimer() ResetTimer() ResetTimer()

Resets the refresh timer for the specified PivotTable report to the last interval you set using the RefreshPeriod property.

SaveAsODC(String, Object, Object) SaveAsODC(String, Object, Object) SaveAsODC(String, Object, Object)

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

Applies to