Page 374 - Microsoft Office Excel 2003 Programming Inside Out

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

Because Excel uses the summary data directly, you can’t change the summary functions for
data fields. Another side effect of using the summary data is that you might not be able to
access the detail data. This information is determined by the design of the OLAP database.

Warning PivotTables based on OLAP data sources behave somewhat differently than
those created using non-OLAP data sources. This is due to the fact that OLAP servers
return data that’s already summarized, so different objects are used internally to store the
summary data and to interact with the OLAP server. The rest of this chapter assumes that
you are working with non-OLAP data sources.

PivotTable Objects

Each Excel worksheet can hold multiple PivotTables. To access a particular PivotTable object,
you start with the Worksheet object that represents the worksheet containing the PivotTable
that you want to manipulate. Then you use the PivotTables collection to reach the specific
PivotTable object that you want to manipulate.
Each PivotTable object contains several key objects that represent the various pieces of a
PivotTable. (See Figure 16-6.)

Worksheet object

PivotTables collection

PivotTable object

PivotCache object
Chapter 16

PivotFields collection
(can be accessed through:
ColumnFields, DataFields,
HiddenFields, PageFields,
RowFields, VisibleFields,
CalculatedFields properties)

PivotField object

PivotItems collection

PivotItem object
Figure 16-6. The parts of a PivotTable are represented by a collection of different types
of objects.

348