Microsoft Office 2000/Visual Basic Programmer's Guide

Adding Fields to a PivotTable Report

When you first create a PivotTable report, no data is displayed because all of the fields in the PivotTable data source are hidden. You need to add fields to the PivotTable report in order to display data. You must add one or more row, column, and data fields; page fields are optional. The PivotField object represents a field that's available in the PivotTable data source. The PivotFields collection belongs to a PivotTable object.

To display a PivotTable field in a PivotTable report, set the Orientation property of the corresponding PivotField object to a constant that specifies where in the table you want to add the field: xlRowField, xlColumnField, xlDataField, or xlPageField. To remove a field from a PivotTable report, set the Orientation property to xlHidden. If you're adding more than one field to an area of a PivotTable report, you can use the Position property to specify the order in which the fields are arranged. The following code fragment adds seven fields to a PivotTable report.

With pvtTable
   ' Specify row field.
   .PivotFields("Country").Orientation = xlRowField
   ' Specify column fields. Specify their relative positions
   ' in the table -- year should come before quarter.
   With .PivotFields("Year")
      .Orientation = xlColumnField
      .Position = 1
   End With
   With .PivotFields("Quarter")
      .Orientation = xlColumnField
      .Position = 2
   End With
   ' Specify page fields.
   .PivotFields("CategoryName").Orientation = xlPageField
   .PivotFields("ProductName").Orientation = xlPageField
   .PivotFields("LastName").Orientation = xlPageField
   ' Specify data field.
   .PivotFields("ProductPrice").Orientation = xlDataField
   ' Format data region as currency.
   .DataBodyRange.NumberFormatLocal = "$#,##0.00"
   .
   .
   .
End With

This code fragment is taken from the CreatePivotTableFromMDB procedure in the modPivotTable module in the Northwind.xls sample file, which is available in the ODETools\V9\Sampes\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM.