Office Object Model: Excel

Properties and methods

`ActiveFilters`
`AddDataField()`
`AddFields()`
`AllocateChanges()`
`Allocation`	Evaluates to `xlAllocation` enumeration
`AllocationMethod`	Method to use for what-if analysis (based on OLAP data source)
`AllocationValue`
`AllocationWeightExpression`
`AllowMultipleFilters`
`AlternativeText`	Value to be used for Title on the Alt Text tab of the PivotTable options.
`Application`
`CacheIndex`	Index number of the PivotTable cache.
`CalculatedFields()`
`CalculatedMembers`
`CalculatedMembersInFilters`
`ChangeConnection()`
`ChangeList`
`ChangePivotCache()`
`ClearAllFilters()`
`ClearTable()`
`ColumnFields`	Evaluates to a `columnFields` object
`ColumnGrand`	A read/write boolean controlling if columns' grand totals are shown.
`ColumnRange`
`CommitChanges()`
`CompactLayoutColumnHeader`
`CompactLayoutRowHeader`
`CompactRowIndent`
`ConvertToFormulas()`
`CreateCubeFile()`
`Creator`
`CubeFields`
`DataBodyRange`
`DataFields`
`DataLabelRange`
`DataPivotField`
`DiscardChanges()`
`DisplayContextTooltips`
`DisplayEmptyColumn`
`DisplayEmptyRow`
`DisplayErrorString`
`DisplayFieldCaptions`
`DisplayImmediateItems`
`DisplayMemberPropertyTooltips`
`DisplayNullString`
`DrillDown()`
`DrillTo()`
`DrillUp()`
`EnableDataValueEditing`
`EnableDrilldown`
`EnableFieldDialog`
`EnableFieldList`
`EnableWizard`
`EnableWriteback`
`ErrorString`
`FieldListSortAscending`
`GetData()`
`GetPivotData()`
`GrandTotalName`
`HasAutoFormat`
`Hidden`
`HiddenFields`
`InGridDropZones`
`InnerDetail`
`LayoutRowDefault`
`ListFormulas()`
`Location`	Top-left cell on worksheet. Compare with `tableRange1` and `tableRange2`
`ManualUpdate`
`MDX`
`MergeLabels`
`Name`
`NullString`
`PageFieldOrder`
`PageFields`
`PageFieldStyle`
`PageFieldWrapCount`
`PageRange`
`PageRangeCells`
`Parent`
`PivotCache()`
`PivotChart`
`PivotColumnAxis`
`PivotFields()`
`PivotFormulas`
`PivotRowAxis`
`PivotSelect()`
`PivotSelection`
`PivotSelectionStandard`
`PivotTableWizard()`
`PivotValueCell()`
`PreserveFormatting`
`PrintDrillIndicators`
`PrintTitles`
`RefreshDataSourceValues()`
`RefreshName`, `RefreshDate`	Name of person who last refreshed (and corresponding date) the pivot table
`RefreshTable()`
`RepeatAllLabels()`
`RepeatItemsOnEachPrintedPage`
`RowAxisLayout()`
`RowFields`	Compare `ColumnFields`
`RowGrand`
`RowRange`
`SaveData`
`SelectionMode`
`ShowDrillIndicators`
`ShowPageMultipleItemLabel`
`ShowPages()`
`ShowTableStyleColumnHeaders`
`ShowTableStyleColumnStripes`
`ShowTableStyleLastColumn`
`ShowTableStyleRowHeaders`
`ShowTableStyleRowStripes`
`ShowValuesRow`
`Slicers`
`SmallGrid`
`SortUsingCustomLists`
`SourceData`
`SubtotalHiddenPageItems`
`SubtotalLocation()`
`Summary`
`TableRange1`	Range that pivot table occupies, without page fields. Compare with `location`
`TableRange2`	Range that pivot table occupies, including page fields. Compare with `location`
`TableStyle2`
`Tag`
`TotalsAnnotation`
`Update()`
`VacatedStyle`
`Value`
`Version`
`ViewCalculatedMembers`
`VisibleFields`
`VisualTotals`
`VisualTotalsForSets`

sourceData

The data type of the .sourceData property depends on the pivot table's data source type.

location

The property location refers to the the upper left cell of the pivot table on a worksheet.

Some properties / methods

Fields

.cubeFields, .pivotFields

.dataPivotField (type is pivotField)

In order to get a special subset of fields, there are also the following methods(?) (or indexable properties?)

columnFields()
dataFields()
hiddenFields()
pageFields()
rowFields()
visibleFields

Ranges

.columnRange: the range on the top/right of the pivot table

.dataLabelRange: upper part of the .columnRange

.dataBodyRange: range that displays the accumlated data

.pageRange: ?

.pageRangeCells ?

tableRange1 / tableRange2

The tableRange1 and tableRange2 refer to the ranges that the pivot table occupies.

tableRange1 does not include page fields, tableRange2 does include them.

Others

.refreshDate and .refreshName

.collumnGrand and .rowGrand are booleans.

.displayEmptyRow and .displayEmptyColumn

If the pivot table is used to provide data for a pivot chart, the .pivotChart property evaluates to a shape object that displays that pivot chart.

In such a case, the pivot table is hidden.

.mdx

Events

Events that are related to a pivotTable object include:

pivotTableAfterValueChange
pivotTableBeforeAllocateChanges
pivotTableBeforeCommitChanges
pivotTableBeforeDiscardChanges
pivotTableChangeSync
pivotTableUpdate

These events are routed to a worksheet object (for example sub Worksheet_PivotTableChangeSync …).

Name of PivotTable

The name of the pivotTable is not only stored in its .name property but also in the .value property.

Office Object Model: Excel - PivotTable