A PivotTable is associated with a number of objects, listed in Table.
All of these objects are members of their respective collections: PivotTables, PivotFields, PivotFormulas, PivotItems, and PivotItemList.
Table. Objects Associated with a PivotTable
| Object | Description |
| PivotTable | A PivotTable. |
| PivotCache | The memory cache allocated for the PivotTable. This object is returned by the PivotCache method of the PivotTable object. |
| PivotCell | A cell in the PivotTable. This object can be obtained via the PivotCell property of the Range object. |
| PivotField | A field in the PivotTable. This object is returned by the PivotFields method of the PivotTable object. |
| PivotFormula | A formula used for calculations in the PivotTable. This object is returned by the PivotFormulas method of the PivotTable object. |
| PivotItem | An item of a PivotTable field. This object is returned by the PivotItems method of the PivotTable object. |
| PivotLayout | Data about the placement of fields and axes of a PivotTable or PivotChart. This object can be obtained via the PivotLayout property of the Chart object. |
The PivotTable Object
The PivotTable object encapsulates data about the PivotTable.
It is a member of the PivotTables collection.
Table. Methods of the PivotTable Object
| Method | Description |
| AddDataField | Adds data fields. |
| AddFields | Adds rows, columns, and pages to the PivotTable. |
| CalculatedFields | Returns the CalculatedFields collection of all calculated fields in the PivotTable. |
| Format | Sets the report format of the PivotTable. |
| GetData | Returns the data from a specified PivotTable cell. |
| GetPivotData | Returns a range with information about the PivotTable. |
| ListFormulas | Creates a list of formulas on a separate sheet. |
| PivotCache | Returns the PivotCache object. |
| PivotFields | Returns the PivotFields collection. |
| PivotSelect | Selects a part of the PivotTable. |
| PivotTableWizard | Builds a PivotTable based on a given table. |
| RefreshTable | Refreshes the PivotTable. To recalculate manually, select the PivotTable and use Data → Refresh Data. |
| ShowPages | Sets the content of the Page area. |
| Update | Updates the links in the PivotTable. |
Table. Properties of the PivotTable Object
| Property | Description |
| ColumnFields, RowFields, DataFields, PageFields | Return an object (either a single field or a collection of fields) representing the columns, rows, data, or pages of the PivotTable. |
| VisibleFields, HiddenFields | Return an object (either a single field or a collection of fields) that is currently displayed (or hidden) in the PivotTable. |
The PivotCache Object
The PivotCache object represents the memory cache allocated to a specific PivotTable.
It is a member of the PivotCaches collection and is returned by the PivotCache method of the PivotTable object.
The primary method of the PivotCaches collection is Add, with the following syntax:
Add(SourceType, SourceData)
- SourceType — required. Defines the type of data used to build the PivotTable. Acceptable constants (XlPivotTableSourceType):
- xlConsolidation (consolidation of several worksheet ranges),
- xlDatabase (Excel list or database),
- xlExternal (external database),
- xlPivotTable (another PivotTable).
- SourceData — optional. Specifies the source of data depending on the SourceType. Required if SourceType ≠ xlExternal.
Table. Methods of the PivotCache Object
| Method | Description |
| CreatePivotTable | Creates a PivotTable object.CreatePivotTable(TableDestination, TableName, ReadData)• TableDestination — optional. Reference to the cell for the upper-left corner of the PivotTable. If creating on a new sheet, pass an empty string.• TableName — optional. The name of the PivotTable.• ReadData — optional Boolean. If True, the PivotTable is built on the entire external database. |
| MakeConnection | Establishes connections to the cache. |
| Refresh | Refreshes the cache. |
| ResetTimer | Resets the cache refresh timer. |
| SaveAsODC | Saves the cache to an ODC (Office Data Connection) file. |
The PivotField Object
The PivotField object represents a PivotTable field.
It is a member of the PivotFields collection and is returned by the PivotField method of the PivotTable object.
Table. Methods of the PivotField Object
| Method | Description |
| AddPageItem | Adds an item to a field. |
| AutoShow | Sets a rule for automatically displaying field items. |
| AutoSort | Sets a rule for automatic field sorting. |
| CalculatedItems | Returns the CalculatedItems collection. |
| Delete | Deletes the field. |
| PivotItems | Returns the PivotItems collection of all field items. |
Table. Main Properties of the PivotField Object
| Property | Description |
| Orientation | Returns the field’s location in the PivotTable. Possible values: xlColumnField, xlDataField, xlHidden, xlPageField, xlRowField. |
| Name | The name of the field. |
| Function | The summary function used in the field. Possible values: xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar, xlVarP. |
| Position | Returns the field’s position (first, second, etc.) among fields of the same location. |