Microsoft Office 2000/Visual Basic Programmer's Guide

Using VBA to Manipulate PivotTable Reports

Each PivotTable field contains a set of values from the corresponding field in the data source. These are the values that you manipulate to display subsets of data.

In VBA, each PivotTable field has a PivotItems collection. A PivotItem object represents a value that appears in a PivotTable field. For example, the PivotItems collection of a field named Quarter probably contains four PivotItem objects, which have respective values of 1, 2, 3, and 4.

You can only work with fields that are displayed in the PivotTable report. To display a page field item, set the CurrentPage property for the PivotField object. A page field can display either a single value or all values. To display a single value, set the CurrentPage property to that value. To display all pivot items for a page field, set the CurrentPage property to "(All)". For example, if you have a page field named Employees, you can display sales data for a single employee by setting the CurrentPage property to the employee's last name, "Smith." To display sales data for all employees, set the CurrentPage property to "(All)".

To display an item in a row, column, or data field, set that PivotItem object's Visible property to True. You can loop through the PivotField object's collection of PivotItems and set the Visible property of each to True to display all pivot items. The following procedure displays all items in a particular PivotTable field.

Private Sub cmdShowAll_Click()
   ' Makes all pivot items for specified field visible.

   Dim pvfField   As PivotField
   Dim pviItem    As PivotItem

   ' Return reference to selected PivotTable field.
   Set pvfField = p_pvtTable.PivotFields(cboShowAll.Value)
   
   ' Determine PivotTable field orientation.
   Select Case pvfField.Orientation
      Case xlHidden
         MsgBox "Field is not visible in PivotTable report!"
      Case xlPageField
         ' If page field, set CurrentPage property to "(All)".
         pvfField.CurrentPage = "(All)"
      Case Else
         ' If row, column, or data field, make all
         ' pivot items visible.
         For Each pviItem In pvfField.PivotItems
            pviItem.Visible = True
         Next
   End Select
End Sub

This procedure appears in the module for the frmPivotOptions form, which is available in the ODETools\V9\Sampes\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM. The form includes a combo box that displays all available PivotTable fields; the user can select a field from the combo box and click the cmdShowAll button to display all items for that PivotTable field.