Programming Excel with VBA and .NET

PivotItem and PivotItems Members

Use the PivotItems collection to get the items in a pivot field. Use the PivotField object’s PivotItems property to get a reference to this collection. Use the PivotItem object to get information about an item. The PivotItems collection and PivotItem object have the following members:

1 Collection only
2 Object and collection
Add 1	Application 2
Caption	ChildItems
Count 1	Creator 2
DataRange	Delete
DrilledDown	Formula
IsCalculated	Item 1
LabelRange	Name
Parent 2	ParentItem
ParentShowDetail	Position
RecordCount	ShowDetail
SourceName	SourceNameStandard
StandardFormula	Value
Visible

PivotItem objects represent the individual values stored in each pivot field. The following code displays a pivot table’s data hierarchically in the Immediate window:

Sub ListAllItems(  )
    Dim pt As PivotTable, pf As PivotField, _
      pi1 As PivotItem, pi2 As PivotItem
    ' Get pivot table.
    Set pt = Worksheets("BookSales").PivotTables(1)
    ' Show table name.
    Debug.Print pt.name
    For Each pf In pt.PivotFields
        ' Show each field name.
        Debug.Print , pf.name
        For Each pi1 In pf.PivotItems
            ' Show each item value.
            Debug.Print , , pi1.name
            For Each pi2 In pi1.ChildItems
                ' Show subitems (not available for OLAP).
                Debug.Print , , , pi2.name
            Next
        Next
    Next
End Sub