Cannot find PivotFields with VBA Code using a Powerpivot table

Question

Hello All,

I am trying to make a button that will expand or collapse my pivot table.

First I made one in a test sheet (as this is my first time using VBA and didn't want to accidentally screw up my whole excel file), which worked fine.

Then I made a new button at the real sheet, copied the code and changed table and field names.

When I tried the button, I got the following message: "error 1004 unable to get the pivotitems property of the pivotfield class"

Somehow he does not recognize the pivotfield name, eventhough I am 100% sure this is its name.

The difference between the not-working and working button is that the not-working one is a linked to a pivottable created via PowerPivot.

The difference i found is that it is linked to multiple tables (the screenshot below is the "list of fields" on the right side of your screen when targetting a pivot table (Sorry for the dutch language, but I guess you guys get the point), while a normal pivottable is not.

Though, only values from "Tabel 6" are actually used.

Here is my current code:

Private Sub ToggleButton1_Click()

Dim pt As PivotTable, fld As PivotField, pvti As PivotItem

Set pt = ActiveSheet.PivotTables("Draaitabel1")

Set fld = pt.PivotFields("MEVERK:Boekjaar")

For Each pvti In fld.PivotItems

If pvti.ShowDetail = True Then

    pvti.ShowDetail = False

    Else

    pvti.ShowDetail = True

End If

Next

End Sub

How can I change this so it finds the PivotField?

Thanks in advance!

Answer 1

I determined a way to do this regarding PowerPivot-based pivot tables.  I have three possible pivot tables on 3 different sheets, and one function for them all (called by a button on each sheet). 

.Range("B4") below is where the first column and row of the pivot table is, so you know which pivot field to collapse. 

The secret is the last line of code.  It isn't ShowDetail True/False, apparentlyfor PowerPivot it is DrilledDown True/False. 

Dim pt As PivotTable, pf As PivotField

    Select Case ActiveSheet.Name

        Case "Sheetname1"

            Set pt = .PivotTables("PivotTable1")

            Set pf = pt.PivotFields("[PowerPivot table name].[" & .Range("B4") & "].[" & .Range("B4") & "]")

            Case "Sheetname2"

            Set pt = .PivotTables("PivotTable6")

            Set pf = pt.PivotFields("[PowerPivot table name].[" & .Range("B4") & "].[" & .Range("B4") & "]")

            Case "Sheetname3"

            Set pt = .PivotTables("PivotTable2")

            Set pf = pt.PivotFields("[PowerPivot table name].[" & .Range("B4") & "].[" & .Range("B4") & "]")

    End Select

    pf.DrilledDown = False

One way to get the full name of the pivot fields is with this snippet:

wbBook.Worksheets(SheetName).Activate

With Worksheets("SheetName").PivotTables(1)

 For i = 1 To .PivotFields.Count

 MsgBox .PivotFields(i).Name

 Next

End With

One last comment: I only figured this out by recording a macro and then clicking the collapse field menu option for the pivot table.  Then I looked at the resulting VBA code and 'there it was'!

Hope this helps.

Answer 2

I still have not been able to figure this out.

Is there anyone that can tell me anything about Powerpivotfields in vba coding?