The Pivot Table Object

To understand better what must be done next, we must discuss the PivotTable object and its various child collection objects.

Invoking the PivotTableWizard method has created a PivotTable object named Sales&Trans for us. All PivotTable objects have a PivotFields collection, accessed through the PivotFields property. Thus, the code:

Dim pf As PivotField For Each pf In ActiveSheet.PivotTables("Sales&Trans").PivotFields Debug.Print pf.Name Next produces the following list of pivot fields:

Year Period Store Code Store City Store Type Transactions Sales

Now, each PivotField object can have a designation that specifies whether this field is to be used as a row field, a column field, a page field, or a data field. This designation is referred to as its orientation.

It turns out that there is more than one way to set the orientation of a pivot field. One approach is to set the pivot field's Orientation property, and another approach is to use the AddFields method. Unfortunately, neither of these methods is sufficiently documented, so some experimentation is in order.

As to the Orientation property approach, consider the code in Example 20-1, which sets both the Orientation and Position properties. We will discuss the subtleties of this code after you have looked at it.

Example 20-1. The CreatePivotFields Procedure

Sub CreatePivotFields()

' Assume source for pivot table ' is in sheet named 'Source'

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="'Source'!R1C1:R14 5C7", TableName:="PivotTable1"

With ActiveSheet.PivotTables("Sales&Trans") Debug.Print "Before all:" ShowFields

.PivotFields("Year").Orientation = xlPageField .PivotFields("Year").Position = 1

.PivotFields("Store City").Orientation = xlRowField .PivotFields("Store City").Position = 1

.PivotFields("Store Type").Orientation = xlRowField .PivotFields("Store Type").Position = 2

.PivotFields("Period").Orientation = xlColumnField

Debug.Print "Before data fields:" ShowFields

With .PivotFields("Transactions") .Orientation = xlDataField .Position = 1 End With

With .PivotFields("Sales")

.Orientation = xlDataField .Position = 2 End With

Debug.Print ""

Debug.Print "After data fields:" ShowFields

.PivotFields("Data").Orientation = xlRowField .PivotFields("Data").Position = 3 End With End Sub

The ShowFields procedure used in CreatePivotFields is shown in Example 20-2; it simply prints (to the Immediate window) a list of all pivot fields and is very useful for experimenting or debugging.

Example 20-2. The ShowFields Procedure

Sub ShowFields()

Dim pf As PivotField

Debug.Print "*PivotFields:"

For Each pf In

ActiveSheet.PivotTables("Sales&Trans").PivotFields Debug.Print pf.Name Next

Debug.Print "*RowFields:"

For Each pf In

ActiveSheet.PivotTables("Sales&Trans").RowFields Debug.Print pf.Name Next

Debug.Print "*ColFields:"

For Each pf In

ActiveSheet.PivotTables("Sales&Trans").ColumnFields Debug.Print pf.Name Next

Debug.Print "*DataFields:"

For Each pf In

ActiveSheet.PivotTables("Sales&Trans").DataFields Debug.Print pf.Name Next End Sub

Running CreatePivotFields results in the following display to the Immediate window:

Before all: *PivotFields: Year Period Store Code Store City Store Type Transactions Sales

*RowFields: *ColFields: *DataFields:

Before data fields:

*PivotFields:

Year

Period

Store Code

Store City

Store Type

Transactions

Sales

*RowFields: Store City Store Type *ColFields: Period *DataFields:

After data fields:

*PivotFields:

Year

Period

Store Code

Store City

Store Type

Transactions

Sales

Data

*RowFields: Store City Store Type Data

*ColFields:

Period

*DataFields:

Sum of Transactions

Sum of Sales

The first thing we notice from this list is that the special pivot field called Data is created by Excel only after the Transactions and Sales fields are assigned the xlDataField orientation. This statement is further supported by the fact that if we move the last two lines of code:

.PivotFields("Data").Orientation = xlRowField .PivotFields("Data").Position = 3

to just before the With block related to the Transactions field, Excel will issue an error message when we try to run the code, stopping at the line:

.PivotFields("Data").Orientation = xlRowField because it cannot set the Orientation property of the nonexistent Data field.

Next, we observe that, with respect to Row, Column, and Page fields, Excel simply adds the pivot fields to the appropriate collections. However, with respect to Data fields, Excel creates new field objects called Sum of Transactions and Sum of Sales that are considered data fields but not pivot-table fields!

0 0

Responses

  • mulu fethawi
    What is a pivot field object?
    7 years ago
  • fulvus bunce
    Are pivottable fields objects?
    2 years ago

Post a comment