Page 378 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Microsoft Office Excel 2003 Programming Inside Out

Table 16-3. Key Properties and Methods of the PivotCaches Collection
Property/Method Description
Add(SourceType, SourceData) Method: adds a new PivotCache object to the collection.
SourceType identifies where the data is coming from. Can
be xlConsolidation, xlDatabase, xlExternal, xlPivotTable, or
xlScenario. SourceData specifies additional information
about the source of the data. Typically, this source is a
Range object, although in the case of an external
database it’s a two-element string array, where the first
element contains the connection string and the second
element contains the SQL query that retrieves the data.
Count Property (read-only): returns the number of items in the
PivotCaches collection.
Item(index) Method: returns the PivotCache object reference
associated with index. Index may refer to the PivotTable
by either its name or its relative position in the collection.

PivotCache Object
The data displayed in a PivotTable is stored in the PivotCache object. You can reference the
PivotCache through the PivotCache method associated with the PivotTable object or through
the PivotCache collection associated with the Workbook object. Multiple PivotTables can
share the same PivotCache object.
Table 16-4 contains a list of the key properties and methods associated with the PivotCache
object.
Table 16-4. Key Properties and Methods of the PivotCache Object
Chapter 16
Property/Method Description
CommandText Property: contains a database command string used to
retrieve data from an external database.
CommandType Property: identifies the type of data stored in
CommandText using a constant found in the XlCmdType
enumeration. Valid command types are xlCmdCube,
xlCmdDefault, xlCmdList, xlCmdSql, and xlCmdTable.
Connection Property: contains an OLE DB or ODBC connection string
used to access an external database; may also contain a
URL to connect a Web data source or the fully qualified
name of a text file or Access database. Use the Refresh
method to update the data contained in the data source.

352