VBA Pivot Items in Power Pivot

[Misca]

What is the proper way to access the Pivot Items in Power Pivot using VBA?

When I record a macro the syntax seems to be

.CurrentPageName = "[TableName].[ColumnName].&[ItemName]"

I can get my macro to loop correctly if I get the ItemNames from another table and place that in the CurrentPageName string but I'd prefer if it could be done with either index numbers or using the "For each PivotItems in .PivotItems" loop.

 

[Kamolga]

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">I normally use this structure to loop through item, doesn't work?

[COLOR=#101094]Dim[/COLOR][COLOR=#303336][FONT=inherit] Table [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] PivotTable[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] PvI [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] PivotItem[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Table [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Analyse"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PivotTables[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"tablename"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Each[/FONT][/COLOR][COLOR=#303336][FONT=inherit] PvI [/FONT][/COLOR][COLOR=#101094][FONT=inherit]In[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Table[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PivotFields[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"fieldname"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PivotItems
        
[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next
[/FONT][/COLOR]

</code>

 

[Misca]

Thanks for your suggestion. That works with normal pivot tables but so far I haven't figured out how to get to the PivotItems level in the Power Pivot. The cube structure seems to be totally different from the normal pivot tables.

 

[fabio1964]

Hi, I'm in the same situation, trying to loop through PivotItems in a PowerPivot Pivot Table... and I cannot make it work...
Does anyone found a solution? Thanks in advance.

 

[Misca]

So far the only solution to my problem seems to be the helper pivot table that had the pivot items from the first pt on the row field. It's not the prettiest solution in the world but it does the trick.

Since I know the table I'm using in the page field I could've used that as well. However, the dimension table I'm getting the values from has thousands of rows where as my data shouldn't have more than a hundred rows at a time I decided to stick with my quick and dirty solution.

 

[fabio1964]

This sounds interesting... could you elaborate a little more? I tried with the use of a slicer, and that one also didn't work, although I still have a small hope...

 

[Misca]

I have both PTs on Sheet1. PT1 is the one I want to filter and PT2 has the filter values (from my dimension table) in rows with the rowcount (any measure should do) in the values. The measure should filter out all the rows with no values from my pivot table and only show the ones that need to be filtered.

Here's my code:

Sub FilterPT()

Dim PT As PivotTable
Dim PTHelper As PivotTable
Dim PF As PivotField


Dim Rng As Range
Dim c As Range
Dim FirstName As String
Dim n As Integer




Application.ScreenUpdating = False




With Sheet1


    Set PT = .PivotTables(1)
   
    Set PF = PT.PageFields(1)
    
    Set PTHelper = .PivotTables(2)
    
    Set Rng = PTHelper.RowRange.Offset(1).Resize(PTHelper.RowRange.Rows.Count - 1)
        
    FirstName = PF.CurrentPageName  'Current PageFilter
    n = InStrRev(FirstName, "].")   'The last occurence of "]." in Current PageFilter (litterally the first occurence inreverse)
    FirstName = Left(FirstName, n)  'The beginning of the PageFilter before the actual filter value
        
    For Each c In Rng
        
        PF.CurrentPageName = _
        FirstName & ".&[" & c.Value & "]"
    
    Next c
        
End With


End Sub

The only catch in the code is the FirstName -part: In normal pivot tables you only need the actual filter value where as PowerPivot needs also the table & field references ( = the FirstName variable in my code).

Also note that the code will crash if you have the "Select Multiple Values" checked.

 

[fabio1964]

Thank you Misca. Unfortunately, I cannot make it work, but I will keep trying...