Show code in...     

  

 
Object Model ExcelApplication Worksheets Worksheet Cells Cell Range
Charts Chart SeriesCollection Series 3DProperties Axis ChartFrame Line Area
PivotField PivotFields PivotTable PivotTables
Style Characters Font Pictures Picture DocumentProperties PageSetup


The PivotTable Object (IPivotTable)

New

* The PivotTable object is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree.

The IPivotTable object represents a single PivotTable in a worksheet. A PivotTable is an interactive table that combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details of different fields. To create an IPivotTable object, use the CreatePivotTable method, for example:

[VBScript]
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")

'--- Open the workbook containing source data for the PivotTable.
XlwApp.Open "c:\reports\pivotdata.xls"

'--- Get a reference to the source data sheet.
Set wsData = XlwApp.Worksheets("DataSheet")	

'--- Create a second worksheet and name it PivotSheet.
Set wsPivot = XlwApp.Worksheets(2)
wsPivot.Name = "PivotSheet"

'--- The data in the workbook is in a named range called 
'--- DataNamedRange.  Get an SARange object representing 
'--- that range.
Set rngData = XlwApp.NamedRange("DataNamedRange")

'--- Pass CreatePivotTable the range of source data to 
'--- create the PivotTable.
Set pvtTable = wsPivot.PivotTables.CreatePivotTable(rngData, 2, 2)
...
IPivotTable Properties IPivotTable Methods
IPivotTable Properties
AutoFormatType
Signature
[VBScript]
Property AutoFormatType As SAXlAutoFormatType (read/write)
Description

AutoFormatType applies one of Excel's built-in PivotTable formats to the specified PivotTable. Set AutoFormatType to an SAXlAutoFormatType value by name or number:

SAXlAutoFormatType Values
saxlAutoFormatNone

Setting AutoFormatType to saxlAutoFormatNone disables AutoFormatting in the generated PivotTable.		-1 saxlAutoFormatReport619
saxlAutoFormatClassic10saxlAutoFormatReport720
saxlAutoFormatClassic21saxlAutoFormatReport821
saxlAutoFormatTable12saxlAutoFormatReport922
saxlAutoFormatTable23saxlAutoFormatReport1023
saxlAutoFormatTable34saxlAutoFormatReport1124
saxlAutoFormatTable45saxlAutoFormatReport1225
saxlAutoFormatTable56saxlAutoFormatReport1326
saxlAutoFormatTable67saxlAutoFormatReport1427
saxlAutoFormatTable78saxlAutoFormatReport1528
saxlAutoFormatTable89saxlAutoFormatReport1629
saxlAutoFormatTable910saxlAutoFormatReport1730
saxlAutoFormatTable1011saxlAutoFormatReport1831
saxlAutoFormatTable1112saxlAutoFormatReport1932
saxlAutoFormatReport113saxlAutoFormatReport2033
saxlAutoFormatReport214saxlAutoFormatReport2134
saxlAutoFormatReport315saxlAutoFormatReport2235
saxlAutoFormatReport417saxlAutoFormatReport2336
saxlAutoFormatReport518saxlAutoFormatBlank37

To apply an AutoFormat in Excel:

  1. Select the PivotTable.


  2. Open the Format menu.


  3. Select AutoFormat...
Example
[VBScript]
pvtTable.AutoFormatType = saxlAutoFormatReport1

Top
DataCollectionInRow
Signature
[VBScript]
Property DataCollectionInRow As Boolean (read/write)
Description

A data field contains values to be summarized. By default, number values are summarized by the Sum function and text values are summarized by the Count function. (To change the summarization type set SummarizeBy.) If a PivotTable contains more than one data field, a single field drop-down named Data appears in the report for access to all of the data fields.

DataCollectionInRow determines whether the collection of data fields will be displayed with the row or column fields. Set this property if the PivotTable contains more than one data field.

The Data button provides access
to the data fields.
Example
[VBScript]
pvtTable.DataCollectionInRow = True

Top
EmptyCellsValue
Signature
[VBScript]
Property EmptyCellsValue As String (read/write)
Description

EmptyCellsValue sets a string to display in empty cells in the PivotTable. The string will only be displayed if ShowValueForEmptyCells is set to true.

Example
[VBScript]
pvtTable.EmptyCellsValue = "N/A"

Top
EnableDrillDown
Signature
[VBScript]
Property EnableDrillDown As Boolean (read/write)
Description

If EnableDrillDown is set to true, the user will be able to:

  • Select a cell in the PivotTable and click Show Detail (on Excel's PivotTable toolbar) to display detailed source data for the cell.


  • Double-click the cell to display detailed source data for the cell.

If EnableDrillDown is set to false, the Show Detail button will be disabled.

Example
[VBScript]
pvtTable.EnableDrillDown = True

Top
EnableFieldDialog
Signature
[VBScript]
Property EnableFieldDialog As Boolean (read/write)
Description

EnableFieldDialog enables or disables Excel's PivotTable Field dialog for the specified PivotTable in the generated spreadsheet. If the property is set to true, the user will be able to right-click within the PivotTable and select Field Settings to open the dialog. If the EnableFieldDialog is set to false Field Settings will be disabled.

Example
[VBScript]
pvtTable.EnableFieldDialog = True

Top
EnableWizard
Signature
[VBScript]
Property EnableWizard As Boolean (read/write)
Description

EnableWizard enables or disables:

  • Excel's PivotTable and PivotChart Wizard for the specified PivotTable, and


  • Excel's PivotTable Options dialog for the specified PivotTable.

If EnableWizard is set to false, the user will not be able to select the PivotTable and open the wizard, or right-click the table and select Table Options...

Example
[VBScript]
pvtTable.EnableWizard = True

Top
Fields
Signature
[VBScript]
Public ReadOnly Property Fields As IPivotFields
Description

Fields returns the collection of IPivotFields in the specified PivotTable. An IPivotField represents a PivotTable field.

Example
[VBScript]
'--- The Fields collection is populated automatically
'--- based on the range of data passed to the CreatePivotTable 
'--- method.
Set fldProduct = pvtTable.Fields("Product")
'--- Product name will be displayed across the top.
fldProduct.Type = saxlPivotFieldColumn

Top
FieldsPerPage
Signature
[VBScript]
Property FieldsPerPage As Long (read/write)
Description
If PageLayout is set to saxlOverThenDown, FieldPerPage sets the number of page fields in each row in the PivotTable. If PageLayout is set to saxlDownThenOver, FieldPerPage sets the number of page fields in each column in the PivotTable.
Example
[VBScript]
pvtTable.FieldsPerPage = 7

Top
FriendlyErrorValue
Signature
[VBScript]
Property FriendlyErrorValue As String (read/write)
Description

FriendlyErrorValue replaces error values in the PivotTable with the specified string. The friendly error string will be displayed only if ShowFriendlyErrorValue is set to true.

Example
[VBScript]
pvtTable.FriendlyErrorValue = "N/A"

Top
GrandTotalsForColumns
Signature
[VBScript]
Property GrandTotalsForColumns As Boolean (read/write)
Description

If GrandTotalsForColumns is set to true, grand totals for each of the columns will be displayed in the PivotTable. If the property is set to false, grand totals for each of the columns will not be displayed.

Example
[VBScript]
pvtTable.GrandTotalsForColumns = True

Top
GrandTotalsForRows
Signature
[VBScript]
Property GrandTotalsForRows As Boolean (read/write)
Description

If GrandTotalsForRows is set to true, grand totals for each of the rows will be displayed in the PivotTable. If the property is set to false, grand totals for each of the rows will not be displayed.

Example
[VBScript]
pvtTable.GrandTotalsForRows = True

Top
InOutlineForm
Signature
[VBScript]
Property InOutlineForm As Boolean (read/write)
Description

InOutlineForm specifies whether to display the PivotTable in outline form or in tabular form.

Example
[VBScript]
pvtTable.InOutlineForm = true

Top
MergeLabels
Signature
[VBScript]
Property MergeLabels As Boolean (read/write)
Description

Set MergeLabels to true to center PivotTable labels vertically and horizontally.

Example
[VBScript]
pvtTable.MergeLabels = True

Top
Name
Signature
[VBScript]
Property Name As String (read/write)
Description

Use Name to set a name for the PivotTable. In Excel the name is displayed in the Pivot Table Options dialog (right-click the PivotTable and select Table Options...). Using the property IPivotTables.Item, you can use the value of Name to reference a specific PivotTable in code.

Example
[VBScript]
pvtTable.Name = "Sales Report 2003"

Top
PageLayout
Signature
[VBScript]
Property PageLayout As SAXlOrder (read/write)
Description

Set PageLayout to control the order in which the PivotTable is printed when it does not fit on one page.

Set PageLayout to an SAXlOrder value by name or number:

SAXlOrder Values
saxlDownThenOver1
saxlOverThenDown2

Example
[VBScript]
pvtTable.PageLayout = saxlOverThenDown

Top
PreserveFormatting
Signature
[VBScript]
Property PreserveFormatting As Boolean (read/write)
Description

If PreserveFormatting is set to true, most PivotTable formatting will be preserved when you change the report layout or refresh the data. Cell border changes are not preserved.

Example
[VBScript]
pvtTable.PreserveFormatting = True

Top
RepeatItemLabels
Signature
[VBScript]
Property RepeatItemLabels As Boolean (read/write)
Description
If RepeatItemLabels is set to true, PivotTable item labels will be repeated on each printed page.
Example
[VBScript]
pvtTable.RepeatItemLabels = True

Top
SetPrintTitles
Signature
[VBScript]
Property SetPrintTitles As Boolean (read/write)
Description

To display the PivotTable's row and column labels as print titles, set SetPrintTitles to true. Print titles are row or column labels that are displayed at the top or left side of each printed page.

SetPrintTitles will not take effect if Rows to repeat at top or Columns to repeat at left is set. To make sure that these settings are clear:

  1. Open the File menu and select Page Setup...


  2. Select the Sheet tab.


  3. Clear the Rows to repeat at top and Columns to repeat at left fields.
Example
[VBScript]
pvtTable.SetPrintTitles = True

Top
ShowFriendlyErrorValue
Signature
[VBScript]
Property ShowFriendlyErrorValue As Boolean (read/write)
Description
If ShowFriendlyErrorValue is set to true, the value set by FriendlyErrorValue will replace error values in the PivotTable.
Example
[VBScript]
pvtTable.ShowFriendlyErrorValue = True

Top
ShowValueForEmptyCells
Signature
[VBScript]
Property ShowValueForEmptyCells As Boolean (read/write)
Description
If ShowValueForEmptyCells is set to true, the value set by EmptyCellsValue will be displayed in empty cells in the PivotTable.
Example
[VBScript]
pvtTable.ShowValueForEmptyCells = True

Top
SubtotalHiddenPageItems
Signature
[VBScript]
Property SubtotalHiddenPageItems As Boolean (read/write)
Description
If SubtotalHiddenPageItems is set true, hidden page items will be included in totals.
Example
[VBScript]
pvtTable.SubtotalHiddenPageItems = True

Top
UpperLeftColumn
Signature
[VBScript]
Property UpperLeftColumn As Long (read/write)
Description
Sets or returns the PivotTable's upper left column number.
Example
[VBScript]
pvtTable.UpperLeftColumn = 7

Top
UpperLeftRow
Signature
[VBScript]
Property UpperLeftRow As Long (read/write)
Description
Sets or returns the PivotTable's upper left row number.
Example
[VBScript]
pvtTable.UpperLeftRow = 7

	
Top
IPivotTable Methods
AddDataField
Signature
[VBScript]
Function AddDataField(BaseField As IPivotField, 
			summFunc As SAXlSummarizationType) 
			As IPivotField
Description

A data field contains values to be summarized. By default, number values are summarized by the Sum function and text values are summarized by the Count function. (To change the summarization type set SummarizeBy.) If a PivotTable contains more than one data field, a single field drop-down named Data appears in the report for access to all of the data fields.

AddDataField adds a new data field to the PivotTable, based on an existing field in the table. Alternatively, you can add a data field by setting an IPivotField's Type to saxlPivotFieldData.

Data fields added are not true fields, in that they are not a column of the data range so these fields cannot be changed later to another type.

Pass AddDataField takes two parameters: an IPivotField and an SAXlSummarizationType by name or number.

SAXlSummarizationType Values
saxlPivotFieldUndefined0
saxlPivotFieldRow1
saxlPivotFieldColumn2
saxlPivotFieldPage4
saxlPivotFieldData8
Example
[VBScript]
Set pvtTable = wsPivot.PivotTables.CreatePivotTable(rngData, 2, 2)
 
Set fldCountry = pvtTable.Fields("Country")
Set fldCategory = pvtTable.Fields("CategoryName")
Set fldOrderID = pvtTable.Fields("OrderID")
Set fldTotal = pvtTable.Fields("Total")
Set fldYear = pvtTable.Fields("Year")

fldCountry.Type = saxlPivotFieldRow
fldCategory.Type = saxlPivotFieldRow

fldYear.Type = saxlPivotFieldColumn
        
'--- Add two data fields to the PivotTable.
'--- The Total field will be tabulated by sum to 
'--- show the total dollar value. The OrderID
'--- field will be counted, to show the number 
'--- of orders.
pvtTable.AddDataField fldTotal, saxlSummarizationSum
pvtTable.AddDataField fldOrderID, saxlSummarizationCount

Top
Move
Signature
[VBScript]
Public Sub Move(newUpperLeftRow As Long, newUpperLeftColumn As Long)
Description
Move moves the PivotTable to a different location in the worksheet. The method takes the new row and column of the upper left corner of the table. You can also move a PivotTable by setting the properties UpperLeftColumn and UpperLeftRow.
Example
[VBScript]
pvtTable.Move 7, 7

Top


Object Model ExcelApplication Worksheets Worksheet Cells Cell Range
Charts Chart SeriesCollection Series 3DProperties Axis ChartFrame Line Area
PivotField PivotFields PivotTable PivotTables
Style Characters Font Pictures Picture DocumentProperties PageSetup

Copyright © 2005, SoftArtisans, Inc.