PivotTable Members 

  • Article

Represents a PivotTable report on a worksheet. The PivotTable object is a member of the PivotTables collection. The PivotTables collection contains all the PivotTable objects on a single worksheet.

The following tables list the members exposed by the PivotTable type.

Public Properties

  Name Description
_Default  
Application Returns an Application object that represents the Microsoft Excel application. Read-only.
CacheIndex Returns or sets the index number of the PivotTable cache. Read/write Integer.
CalculatedMembers Returns a CalculatedMembers collection representing all the calculated members and calculated measures for an OLAP PivotTable.
ColumnFields Returns an object that represents either a single PivotTable field (a PivotField object) or a collection of all the fields (a PivotFields object) that are currently shown as column fields. Read-only.
ColumnGrand True if the PivotTable report shows grand totals for columns. Read/write Boolean.
ColumnRange Returns a Range object that represents the range that contains the column area in the PivotTable report. Read-only.
Creator Returns a 32-bit integer that indicates the application in which this object was created. If the object was created in Microsoft Excel, this property returns the string XCEL, which is equivalent to the hexadecimal number 5843454C. Read-only XlCreator.
CubeFields Returns the CubeFields collection. Each CubeField object contains the properties of the cube field element. Read-only.
DataBodyRange Returns a Range object that represents the range that contains the data area in the list between the header row and the insert row. Read-only.
DataFields Returns an object that represents either a single PivotTable field (a PivotField object) or a collection of all the fields (a PivotFields object) that are currently shown as data fields. Read-only.
DataLabelRange Returns a Range object that represents the range that contains the labels for the data fields in the PivotTable report. Read-only.
DataPivotField Returns a PivotField object that represents all the data fields in a PivotTable. Read-only.
DisplayEmptyColumn Returns True when the non-empty MDX keyword is included in the query to the OLAP provider for the value axis. The OLAP provider will not return empty columns in the result set. Returns False when the non-empty keyword is omitted. Read/write Boolean.
DisplayEmptyRow Returns True when the non-empty MDX keyword is included in the query to the OLAP provider for the category axis. The OLAP provider will not return empty rows in the result set. Returns False when the non-empty keyword is omitted. Read/write Boolean.
DisplayErrorString True if the PivotTable report displays a custom error string in cells that contain errors. The default value is False. Read/write Boolean.
DisplayImmediateItems Returns or sets a Boolean that indicates whether items in the row and column areas are visible when the data area of the PivotTable is empty. Set this property to False to hide the items in the row and column areas when the data area of the PivotTable is empty. The default value is True.
DisplayNullString True if the PivotTable report displays a custom string in cells that contain null values. The default value is True. Read/write Boolean.
EnableDataValueEditing True to disable the alert for when the user overwrites values in the data area of the PivotTable. True also allows the user to change data values that previously could not be changed. The default value is False. Read/write Boolean.
EnableDrilldown True if drilldown is enabled. The default value is True. Read/write Boolean.
EnableFieldDialog True if the PivotTable Field dialog box is available when the user double-clicks the PivotTable field. The default value is True. Read/write Boolean.
EnableFieldList False to disable the ability to display the field list for the PivotTable. If the field list was already being displayed it disappears. The default value is True. Read/write Boolean.
EnableWizard True if the PivotTable Wizard is available. The default value is True. Read/write Boolean.
ErrorString Returns or sets the string displayed in cells that contain errors when the DisplayErrorString property is True. The default value is an empty string (""). Read/write String.
GrandTotalName Returns or sets the text string label that is displayed in the grand total column or row heading in the specified PivotTable report. The default value is the string "Grand Total". Read/write String.
HasAutoFormat True if the PivotTable report is automatically formatted when it’s refreshed or when fields are moved. Read/write Boolean.
HiddenFields Returns an object that represents either a single PivotTable field (a PivotField object) or a collection of all the fields (a PivotFields object) that are currently not shown as row, column, page, or data fields. Read-only.
InnerDetail Returns or sets the name of the field that will be shown as detail when the ShowDetail property is True for the innermost row or column field. Read/write String.
ManualUpdate True if the PivotTable report is recalculated only at the user's request. The default value is False. Read/write Boolean.
MDX Returns a String indicating the MDX (Multidimensional Expression) that would be sent to the provider to populate the current PivotTable view. Read-only.
MergeLabels True if the specified PivotTable report’s outer-row item, column item, subtotal, and grand total labels use merged cells. Read/write Boolean.
Name Returns or sets the name of the object. Read/write String.
NullString Returns or sets the string displayed in cells that contain null values when the DisplayNullString property is True. The default value is an empty string (""). Read/write String.
PageFieldOrder Returns or sets the order in which page fields are added to the PivotTable report’s layout. Can be one of the following XlOrder constants: xlDownThenOver or xlOverThenDown. The default constant is xlDownThenOver. Read/write Integer.
PageFields Returns an object that represents either a single PivotTable field (a PivotField object) or a collection of all the fields (a PivotFields object) that are currently showing as page fields. Read-only.
PageFieldStyle Returns or sets the style used in the bound page field area. The default value is a null string (no style is applied by default). Read/write String.
PageFieldWrapCount Returns or sets the number of page fields in each column or row in the PivotTable report. Read/write Integer.
PageRange Returns a Range object that represents the range that contains the page area in the PivotTable report. Read-only.
PageRangeCells Returns a Range object that represents only the cells in the specified PivotTable report that contain the page fields and item drop-down lists.
Parent Returns the parent object for the specified object. Read-only
PivotFormulas Returns a PivotFormulas object that represents the collection of formulas for the specified PivotTable report. Read-only.
PivotSelection Returns or sets the PivotTable selection in standard PivotTable report selection format. Read/write String.
PivotSelectionStandard Returns or sets a String indicating the PivotTable selection in standard PivotTable report format using English (United States) settings. Read/write.
PreserveFormatting This property is True if formatting is preserved when the report is refreshed or recalculated by operations such as pivoting, sorting, or changing page field items. Read/write Boolean.
PrintTitles True if the print titles for the worksheet are set based on the PivotTable report. False if the print titles for the worksheet are used. The default value is False. Read/write Boolean.
RefreshDate Returns the date on which the PivotTable report was last refreshed. Read-only DateTime.
RefreshName Returns the name of the person who last refreshed the PivotTable report data or the PivotTable cache. Read-only String.
RepeatItemsOnEachPrintedPage True if row, column, and item labels appear on the first row of each page when the specified PivotTable report is printed. False if labels are printed only on the first page. The default value is True. Read/write Boolean.
RowFields Returns an object that represents either a single field in a PivotTable report (a PivotField object) or a collection of all the fields (a PivotFields object) that are currently showing as row fields. Read-only.
RowGrand True if the PivotTable report shows grand totals for rows. Read/write Boolean.
RowRange Returns a Range object that represents the range including the row area on the PivotTable report. Read-only.
SaveData True if data for the PivotTable report is saved with the workbook. False if only the report definition is saved. Read/write Boolean.
SelectionMode Returns or sets the PivotTable report structured selection mode. Read/write XlPTSelectionMode.
ShowCellBackgroundFromOLAP True if the MDX (Multidimensional Expression) that Microsoft Excel sends to the server includes the BackColor property for each cell in the data area that corresponds to a cell in the Online Analytical Processing (OLAP) data set. The default value is False. Read/write Boolean.
ShowPageMultipleItemLabel When set to True (default), "(Multiple Items)" will appear in the PivotTable cell on the worksheet whenever items are hidden and an aggregate of non-hidden items is shown in the PivotTable view. Read/write Boolean.
SmallGrid True if Microsoft Excel uses a grid that’s two cells wide and two cells deep for a newly created PivotTable report. False if Excel uses a blank stencil outline. Read/write Boolean.
SourceData Returns the data source for the PivotTable report. Read-write Object.
SubtotalHiddenPageItems True if hidden page field items in the PivotTable report are included in row and column subtotals, block totals, and grand totals. The default value is False. Read/write Boolean.
TableRange1 Returns a Range object that represents the range containing the entire PivotTable report, but doesn’t include page fields. Read-only.
TableRange2 Returns a Range object that represents the range containing the entire PivotTable report, including page fields. Read-only.
TableStyle Returns or sets the style used in the body of the PivotTable report. The default value is a null string (no style is applied by default). Read/write String.
Tag Returns or sets a string saved with the PivotTable report. Read/write String.
TotalsAnnotation True if an asterisk (*) is displayed next to each subtotal and grand total value in the specified PivotTable report if the report is based on an OLAP data source. The default value is True. Read/write Boolean.
VacatedStyle Returns or sets the style applied to cells vacated when the PivotTable report is refreshed. The default value is a null string (no style is applied by default). Read/write String.
Value The name of the PivotTable report. Read/write String.
Version Returns the Microsoft Excel version number. Read-only XlPivotTableVersionList.
ViewCalculatedMembers When set to True (default), calculated members for Online Analytical Processing (OLAP) PivotTables can be viewed. Read/write Boolean.
VisibleFields Returns an object that represents either a single field in a PivotTable report (a PivotField object) or a collection of all the visible fields (a PivotFields object). Visible fields are shown as row, column, page, or data fields. Read-only.
VisualTotals True (default) to enable Online Analytical Processing (OLAP) PivotTables to recalculate the total after an item has been hidden from view. Read/write Boolean.

Top

Public Methods

  Name Description
_PivotSelect  
AddDataField Adds a data field to a PivotTable report. Returns a PivotField object that represents the new data field.
AddFields Adds row, column, and page fields to a PivotTable report or PivotChart report.
CalculatedFields Returns a CalculatedFields collection that represents all the calculated fields in the specified PivotTable report. Read-only.
CreateCubeFile Creates a cube file from a PivotTable report connected to an Online Analytical Processing (OLAP) data source.
Dummy15  
Format Sets a PivotTable report to one of the predefined indented, nonindented, or cross-tabulated formats.
GetData Returns data from the specified PivotTable cell.
GetPivotData Returns a Range object with information about a data item in a PivotTable report.
ListFormulas Creates a list of calculated PivotTable items and fields on a separate worksheet.
PivotCache Returns a PivotCache object that represents the cache for the specified PivotTable report. Read-only.
PivotFields Returns an object that represents either a single PivotTable field (a PivotField object) or a collection of both the visible and hidden fields (a PivotFields object) in the PivotTable report. Read-only.
PivotSelect Selects part of a PivotTable report.
PivotTableWizard Creates a PivotTable object. This method doesn’t display the PivotTable Wizard. This method isn’t available for OLE DB data sources. Use the Add method to add a PivotTable cache, and then create a PivotTable report based on the cache.
RefreshTable Refreshes the PivotTable report from the source data. Returns True if it’s successful.
ShowPages Creates a new PivotTable report for each item in the page field. Each new report is created on a new worksheet.
Update Updates the link or PivotTable report.

Top

See Also

Reference

PivotTable Interface
Microsoft.Office.Interop.Excel Namespace