Pivot Table Methods

Name Returns Parameters Description

AddDataField Pivot Field As Object, Adds a data field to a

Field [Caption], PivotTable report.

[Function] Field is the unique field on the server, Caption is the label used to identify this data field, and Function is the function performed in the added data field

Adds row, column, and page fields to a PivotTable report. RowFields, ColumnFields, and PageFields can hold a single string field name or an array of string field names. Set AddToTable to True to add the fields to the report. Set AddToTable to False to replace the fields in the report

Calculated Fields

Calculated Fields

Returns the collection of calculated fields in the PivotTable report

Change Connection


Change the connection string for a given pivot cache

ChangePivot Cache


Change the PivotCache for a given PivotTable

ClearAll Filters

Clears all filters applied to the PivotField, including manual filters and those applied from the PivotFilters collection of the PivotField

AddFields Variant [RowFields],









ConvertTo Formulas

ConvertFilters As Boolean

CreateCubeFile String

File As String, [Measures], [Levels], [Members], [Properties]

DisplayAll Member Properties InTooltip




Name As String

Removes all fields from the PivotTable, deletes all applied filters, deletes all applied sorting, and returns the Piv-otTable to an empty state

Converts a given OLAP PivotTable to Cube formulas

Creates a cube file from a PivotTable report connected to an OLAP data source. File is the name of the cube file to be created, Measures is an array of unique names of measures that are to be part of the slice, and Levels an array of strings where each array item is a unique level name. Members is an array of string arrays where the elements correspond, in order, to the hierarchies represented in the Levels array. Properties should be set to False if you don't want member properties being included in the slice

Determines whether member properties are listed in a ToolTip when hovering over a member

Get the value of a specific cell in the Pivot-Table report. The Name parameter must be in the standard PivotTable report selection format

Name Returns



GetPivotData Range


Returns information


about a data item in a


PivotTable report.


FieldN is the name of a


column or row field in


the PivotTable report,


and ItemN is the name


of an item in FieldN
































Name As String, [Mode As XlPTSelection Mode], [UseStandardName]

Creates a separate worksheet with the list of all the calculated Pivot-Table items and fields

Returns a data cache associated with the current PivotTable

Returns an object or collection containing the PivotTable field (PivotField) or PivotTable fields (PivotFields) associated with the fields of the PivotTable

Selects the part of the PivotTable specified by Name parameter in the standard PivotTable report selection format. Mode decides which part of the PivotTable to select (for example, xlBlanks). Set UseStandardName to True for recorded macros that will play back in other locales

Table continued on following page





PivotTable Wizard

[SourceType], [SourceData], [TableDestination], [TableName], [RowGrand], [ColumnGrand], [SaveData], [HasAutoFormat], [AutoPage], [Reserved], [BackgroundQuery], [OptimizeCache], [PageFieldOrder], [PageFieldWrapCount] [ReadData], [Connection]

Creates a PivotTable report. The SourceType uses the XLPivotTable SourceType constants to specify the type of SourceData being used for the PivotTable. The TableDestination holds the range in the parent worksheet where the report will be placed. TableName holds the name of the new report. Set RowGrand or ColumnGrand to True to show grand totals for rows and columns, respectively. Set HasAutoFormat to True for Excel to format the report automatically when it is refreshed or changed. Use the AutoPage parameter to set if a page field is created for consolidation automatically. Set BackgroundQuery to True for Excel to query the data source asynchronously. Set OptimizeCache to True for Excel to optimize the cache when it is built. Use the PageFieldOrder with the XLOrder constants to set how new page fields are added to the report. Use the PageFieldWrapCount to set the number of page fields in each column or row. Set ReadData to True to copy the data from the external database into a cache. Finally, use the Connection parameter to specify an ODBC connection string for the Piv-otTable's cache







Refreshes the PivotTable report from the source data and returns True if successful


RowLayout As XlLayout RowType

Sets the layout options for all existing Pivot-Fields. Note that if layout options cannot be set on any of the Pivot-Fields, no change will be made on any of the fields in the PivotTable




Creates a new Pivot-Table report for each item in the page field (PageField) in a new worksheet

Subtotal Location

Location As XlSubtotal LocationType

Sets the Layout SubtotalLocation property for all existing PivotFields. Note that changing the subtotal location has a visual effect only for those fields in outline form


Updates the PivotTable report

0 0


    Which are the function performed in the added data field?
    8 years ago

Post a comment