Class PivotCache (Excel VBA)

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

Class PivotTable gives access to class PivotCache.


Dim pvtcac as PivotCache
Set pvtcac = ActiveWorkbook.PivotCaches(Index:=1)

For Each

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


Dim pvtcac As PivotCache
For Each pvtcac In ActiveWorkbook.PivotCaches()
	
Next pvtcac

Methods

CreatePivotChart - Creates a standalone PivotChart from a PivotCache object. Returns a Shape object.

CreatePivotTable - Creates a PivotTable report based on a PivotCache object. Returns a PivotTable object.

MakeConnection - Establishes a connection for the specified PivotTable cache.

Refresh - Causes the specified chart to be redrawn immediately.

ResetTimer - Resets the refresh timer for the specified query table or PivotTable report to the last interval that you set by using the RefreshPeriod property.

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

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.

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

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.

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

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

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.

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

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 returns the amount of memory currently being used by the object, in 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.

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

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

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.

Recordset

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

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.

RefreshPeriod returns or sets the number of minutes between refreshes.

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

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.

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

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

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.

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.

PivotTable - Represents a PivotTable report on a worksheet.

Shape - Represents an object in the drawing layer, such as an AutoShape, freeform, OLE object, or picture.

WorkbookConnection - A connection is a set of information needed to obtain data from an external data source other than a Microsoft Excel workbook.