PivotTable Objects
|
| < Day Day Up > |
|
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.)
Figure 16-6: The parts of a PivotTable are represented by a collection of different types of objects.
PivotTables Collection
The PivotTables collection contains the set of all PivotTables in a particular worksheet. Table 16-1 lists the key properties and methods for the PivotTables collection. You can access the PivotTables property from a Worksheet object using the following code:
ActiveSheet.PivotTables(1)
| Property/Method | Description |
|---|---|
| Add(PivotCache, TableDestination, TableName, ReadData, DefaultVersion) | Method: adds a new PivotTable to the collection. PivotCache refers to the data that will be displayed in the PivotTable; TableDestination passes a Range object whose upper-left corner marks the location of the PivotTable in the worksheet; TableName contains the name of the PivotTable (optional); ReadData, when True, caches all records from an external database in PivotCache; DefaultVersion specifies the version of Excel that created the PivotTable. |
| Count | Property (read-only): returns the number of items in the PivotTables collection. |
| Item(index) | Method: returns the PivotTable object reference associated with index. Index may refer to the PivotTable by either its name or its relative position in the collection. |
PivotTable Object
The PivotTable object represents a single PivotTable. You can use this object to access an existing PivotTable on a worksheet, or you can use the Add method from the PivotTables collection to add a new one. You can even use the PivotTableWizard method from the Worksheet object to create a new PivotTable.
Table 16-2 contains a list of the key properties and methods associated with the PivotTable object.
| Property/Method | Description |
|---|---|
| AddDataField(Field, Caption, Function) | Method: This routine adds a data field to a PivotTable. Field is a PivotField object associated with the PivotTable. Caption contains a value that will be used to label the specified PivotField (optional). Function specifies the summary function performed on the data field. |
| CalculatedFields | Method: returns a CalculatedFields collection, which contains the set of PivotField objects, which are computed based on other fields in the PivotTable. |
| ColumnFields | Property (read-only): returns either a PivotField object or a PivotFields collection containing the fields that are currently displayed as column fields. |
| ColumnGrand | Property: when True, the PivotTable report displays grand totals for each column in the PivotTable object. |
| DataFields | Property (read-only): returns either a PivotField object or a PivotFields collection containing the fields that are currently displayed as data fields. |
| DisplayErrorString | Property: when True, any cells with errors will display the text in the ErrorString property. |
| DisplayNullString | Property: when True, any cells with null values will display the text in the NullString property. |
| ErrorString | Property: contains the text that will be displayed in cells with errors, if the DisplayErrorString property is True. |
| Format (format) | Method: sets the PivotTable's format to one of the formats specified in XlPivotFormatType. Valid formats are xlPTClassic, xlPTNone, xlPTReport1 to xlPTReport10, and xlPTTable1 to xlPTTable10. |
| HiddenFields | Property (read-only): returns either a PivotField object or a PivotFields collection containing the fields that are not currently displayed as row, column, page, or data fields. |
| Name | Property: contains the name of the PivotTable. |
| NullString | Property: contains the text that will be displayed in cells with null values when the DisplayNullString property is True. |
| PageFields | Property (read-only): returns either a PivotField object or a PivotFields collection containing the fields that are currently displayed as page fields. |
| PivotCache | Method: returns an object reference to the PivotCache object associated with the PivotTable. |
| PivotFields (index) | Method: returns either the PivotField object specified by the index parameter, or returns the collection of all PivotFields if index is omitted. |
| PivotTableWizard (SourceType, SourceData, TableDestination, TableName, RowGrand, ColumnGrand, SaveData, HasAutoFormat, AutoPage, Reserved, BackgroundQuery, OptimizeCache, PageFieldOrder, PageFieldWrapCount, ReadData, Connection) | Method: constructs a PivotTable named TableName using data from SourceData and places the result at TableDestination. |
| RefreshTable | Method: refreshes the data displayed in the PivotTable based on the data in the associated data source. |
| RowFields | Property (read-only): returns either a PivotField object or a PivotFields collection containing the fields that are currently displayed as row fields. |
| RowGrand | Property: when True, displays grand totals for each row in the PivotTable. |
| VisibleFields | Property (read-only): returns either a PivotField object or a PivotFields collection containing the fields that are currently displayed in the PivotTable. |
| Tip | Refresh Your Data |
PivotCaches Collection
The PivotCaches collection contains the set of all PivotCache objects (see Table 16-3). Because PivotCache objects are stored at the workbook level, they can be easily used to create PivotTables and PivotCharts anywhere in the workbook.
| 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.
| 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. |
| CreatePivotTable (TableDestination, TableName, ReadData, DefaultVersion) | Method: creates a PivotTable object based on the current PivotCache object. |
| Index | Property (read-only): returns the index number of the PivotCache within the PivotCaches collection. |
| IsConnected | Property (read-only): when True, means that the PivotCache is currently connected to a data source. |
| MakeConnection | Method: opens a connection between the PivotCache and the data source specified in the Connection property. |
| MemoryUsed | Property (read-only): returns the number of bytes used by the current PivotCache object. |
| OLAP | Property (read-only): returns True when the cache is connected to an OLAP server. |
| QueryType | Property (read-only): returns the type of query as enumerated in XlQueryType that is used to populate the cache. Value query types are xlADORecordset, xlDAORecordset, xlDDBCQuery, xlOLEDBQuery, xlTextImport, and xlWebQuery. |
| RecordCount | Property (read-only): returns the number of records in the cache. |
| Recordset | Property: contains the Recordset object that will be used to populate the cache. |
| Refresh | Method: updates the information in the PivotCache object and the associated PivotTable object. |
| RefreshDate | Property (read-only): returns the date when the cache was last refreshed. |
| RefreshPeriod | Property: contains the number of minutes between refreshes. Setting this property to zero disables automatic refreshes. |
PivotField Objects
The PivotField object represents one of the fields in a PivotTable. This object contains a lot of useful information that describes the field, including its Name, MemoryUsed, and NumberFormat. However, the most important properties are the Orientation and Position properties. The Orientation property determines the area on the PivotTable where the field is located, and the Position property determines the order of the field within a particular area.
The PivotItems method returns a collection of PivotItem objects. Each PivotItem object represents a specific value within the PivotField object.
You can access a PivotField object through these properties associated with the PivotTable object. These properties provide a shortcut to commonly used subsets of the data.
-
The PivotFields collection contains the complete set of all PivotField objects associated with the PivotTable.
-
The HiddenFields property returns the set of PivotField objects that are not currently displayed on the PivotTable or the PivotChart.
-
The VisibleFields property returns the set of PivotField objects that are currently displayed on the PivotTable or the PivotChart.
-
The ColumnFields property returns the set of PivotField objects that are displayed as columns in a PivotTable or a PivotChart.
-
The RowFields property returns the set of PivotField objects that are displayed as rows in a PivotTable or a PivotChart.
-
The PageFields property returns the set of PivotField objects that are displayed in the page area of a PivotTable or a PivotChart.
-
The DataFields property returns the set of PivotField objects that are displayed in the data area of a PivotTable or a PivotChart.
-
The CalculatedFields collection contains the set of PivotField objects that are computed from other fields in the PivotTable.
Note If there is only one PivotField in a collection, the associated property from the PivotTable object will point directly to the PivotField object rather than to a PivotFields collection.
Table 16-5 contains a list of the key properties and methods associated with the PivotField object.
| Property/Method | Description |
|---|---|
| Caption | Property (read-only): returns the label text for the field. |
| CurrentPage | Property: contains the current page for a page field. |
| DataRange | Property (read-only): returns a Range object with the data contained in the field. |
| DataType | Property (read-only): returns the type of data from the XlPivotFieldDataType enumeration (xlDate, xlNumber, or xlText) that represents the field. |
| DragToColumn | Property: when False, means that the field cannot be dragged to the column area in the PivotTable. |
| DragToData | Property: when False, means that the field can't be dragged to the data area in the PivotTable. |
| DragToHide | Property: when False, means that the field can't be dragged away from the PivotTable. |
| DragToPage | Property: when False, means that the field can't be dragged to the page area of the PivotTable. |
| DragToRow | Property: when False, means that the field cannot be dragged to the row area in the PivotTable. |
| LayoutForm | Property: contains the way that PivotTable items appear. xlTabular implies table format; xlOutline specifies outline format. |
| MemoryUsed | Property (read-only): returns the number of bytes used by the current PivotField object. |
| Name | Property: contains the name of the PivotField object. |
| NumberFormat | Property: contains the formatting specifications that will be used to display the information from the PivotField object. Applies only to fields used in the data area of the PivotTable. |
| Orientation | Property: contains the location of the field in a PivotTable as specified by the XlPivotFieldOrientation type (xlColumnField, xlDataField, xlHidden, xlPageField, or xlRowField). |
| PivotItems | Method: returns the collection of PivotItems representing the individual items within a particular field. |
| Position | Property: contains the relative position of the field within its orientation. |
| ShowAllItems | Property: when True, means that all items in the PivotTable report will be displayed even if they don't contain summary data. |
| Value | Property: contains the name of the specified field in the PivotTable report. |
PivotItem Object
The PivotItem object represents a single, specific value for a particular PivotField object. Table 16-6 contains a list of the key properties and methods associated with the PivotItem object.
| Property/Method | Description |
|---|---|
| Caption | Property (read-only): returns the label text for the field. |
| DataRange | Property (read-only): returns a Range object with the data contained in the field. |
| Formula | Property: contains the formula associated with the item in A1- style notation. If the cell is empty, an empty string will be returned. If the cell contains a constant, the constant will be returned. |
| LabelRange | Property (read-only): returns the Range object representing the cells in the PivotTable report that contain the item. |
| Name | Property: contains the name of the PivotItem object. |
| Position | Property: contains the relative position of the field within its orientation. |
| RecordCount | Property (read-only): returns the number of records in the PivotCache containing the specified item. |
| Value | Property: contains the name of the specified item in the PivotTable field. |
| Visible | Property: when True, means that the item is visible. |
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 161