CODEWITHSUNDEEP
vbaexcelvstopivot-table
BuddyJoe
BuddyJoe

Reputation: 71101

Excel VBA or VSTO - How do you loop over Fields on a PivotTable?

Here is some sample VBA code:

Sub Macro1()
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("SomePivotTable")
    'Set colOfFields = pt.PivotFields  
End Sub

The third line is incomplete/broken. What is the correct way to get access to collection of all the fields in a PivotTable? I need to be able to loop over them. Actual coding is being done in C# VSTO Project.

Upvotes: 2

Views: 8396

Answers (2)

Patrick Cuff
Patrick Cuff

Reputation: 29786

This works for me (Excel 2003 [11.8146.8202] SP2):

Sub Macro1()
    Dim pt As PivotTable
    Dim col As PivotFields
    Dim c As PivotField

    ' Name of the pivot table comes from right clicking on the pivot table,
    ' Table Options..., Name field.
    Set pt = ActiveSheet.PivotTables("PivotTable1")
    Set col = pt.PivotFields
    For Each c In col
        Debug.Print c.Name
    Next
End Sub

Upvotes: 3

BuddyJoe
BuddyJoe

Reputation: 71101

Ok. Found some C#-flavored code ideas from:
http://blogs.msdn.com/andreww/archive/2008/07/25/creating-a-pivottable-programmatically.aspx

// pvtTable is an Excel.PivotTable set earlier in the code
Excel.PivotFields pflds =     
    (Excel.PivotFields)pvtTable.PivotFields(System.Type.Missing);
    foreach (Excel.PivotField pf in pflds)
    {
      //some code here
    }

The trick is passing in the System.Type.Missing to get the "collection" of fields back.

Upvotes: 1

Related Questions