10

Disclaimer: This question was posted in msdn forum but since I didn't get any reply (surprisingly), I am cross posting it here as well. You might want to check that link before you try to attempt this question. I wouldn't want you to waste your time if a solution was posted in that link :)

Problem: I want to find out the field names in the Report Filter. So I am looking for RegionCountryName, Cityname and ProductKey. The Report Filters are dynamic and can change.

Screenshot:

enter image description here

What have I tried: I have checked every property of the Power Pivot but there is no property that I could find which would let me loop through the fields of the power pivot.

I have even tried this code but it apparently give me an error on Set pf = pt.PageFields(1)

Sub Sample()
    Dim pt As PivotTable, pi As PivotItem, pf As PivotField
    Dim lLoop As Long

    Set pt = Sheet1.PivotTables(1)
    Set pf = pt.PageFields(1)

    For Each pi In pf.PivotItems
        Debug.Print pi.Name
    Next pi
End Sub

Error ScreenShot

enter image description here

So what am I missing? Or is there a different approach that I should take?

EDIT

If someone wants to experiment with it, the file can be downloaded from HERE I am looking at the Inventory Sheet.

Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250

3 Answers3

3

After some testing, it seems that you can get the list of Page filter fields by listing the PivotFields and checking if the value of their Orientation property is equal to XlPageField. Try the code below :

Sub Test()
    Dim pt as PivotTable
    Dim pf as PivotFields
    set pt=Sheets("test").PivotTables(1)
    For each pf in pt.PivotFields
        If pf.Orientation=xlPageField Then 
            Debug.Print  pf.Name
        End If
    Next pf
End Sub

Hope this helps

flaberenne
  • 74
  • 5
  • Thanks for your answer but this is not a normal Pivot. It is a Power Pivot. The line `For each pf in pt.PivotFields` gives me a `Run Time Error 13, Type Mismatch`. If you want to experiment with it, you can download it from [here](http://www.microsoft.com/en-gb/download/details.aspx?id=11597) – Siddharth Rout Sep 23 '13 at 06:10
  • Sorry it did not help, from what i can read here and there, powerpivots seem to use CubeFields but I can't test it. Have you tried it ? – flaberenne Sep 23 '13 at 23:01
  • I think you forgot to mention the links – Siddharth Rout Sep 24 '13 at 05:25
  • Hi, here's one of the links : [link](http://www.joyofdata.de/blog/controlling-powerpivot-and-pivot-tables-with-vba/) – flaberenne Sep 24 '13 at 19:51
1

More than 2 years have passed since the original question has been raised but I still could not find a satisfying answer...

BUT I found a way around :-) You can change PowerPivot table via slicers too.

I used this code to change the selected month in my file:

Private Sub SpinButton21_SpinDown()
 Dim SC As SlicerCache
 Dim SL As SlicerCacheLevel
 Dim SI As SlicerItem

 Set SC = ActiveWorkbook.SlicerCaches("Slicer_Month4")
 Set SL = SC.SlicerCacheLevels(1)

 'get the current item number
 c = 1
 For Each SI In SL.SlicerItems
    If SI.Selected = True Then
        MONTHindex = c
        Exit For
    End If
    c = c + 1
 Next SI
 If MONTHindex = 1 Then
    MONTHindex = SC.SlicerCacheLevels.Item.Count + 1
 End If
 SC.VisibleSlicerItemsList = SL.SlicerItems(MONTHindex - 1).Name
End Sub

I am not a pro but I hope it helps.

EndreK
  • 11
  • 2
1

If you change pt.pivotfields to pt.pagefields in the post of realce_net it should run. As the Microsoft reference says, pagefields was introduced with Office 2013.

    Dim pt As PivotTable
    Dim pf As PivotFields
    Set pt = Sheets("test").PivotTables(1)
    For Each pf In pt.PageFields
        If pf.Orientation = xlPageField Then
            Debug.Print pf.Name
        End If
    Next pf
crx
  • 153
  • 2
  • 8