Microsoft Office Tutorials and References
In Depth Information
Example 20-2. The ShowFields Procedure
*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
pivottable fields!
Search JabSto ::




Custom Search