Retrieve all items from PageFields of a pivot table

[dibbler]

Hi everyone,

I found the following code in a former thread and it works great for "normal" pivot tables.
http://www.mrexcel.com/forum/excel-...elp-how-retrieve-all-pivot-table-filters.html

Unfortunately my pivot tables get its data mainly from a power pivot data model.
So when I start the macro (I changed it slightly in order to write the items in a separate sheet) , all I get is the name of the page field but no items below.

This is what I get: [Pivot_MerO_DataImport].[Decided Year].[Decided Year]

'Pivot_MerO_DataImport' is the name of the table in the data model from where I get the data for the pivot table and 'Decided Yea'r is the name of the page field. Which settings do I have to carry out so that the code works also for power pivot data?

Sub Get_Current_PageFilters_OnePageField()     
Dim PT As PivotTable Dim sArray() As String Dim i As Long     
On Error Resume Next          
Set PT = Worksheets("PivotData").Range("A1").PivotTable      
If PT.PageFields.Count < 1 Then MsgBox "There are no PageFields in this Pivot Table"         
Exit Sub     
End If          
With PT.PageFields(1)        
 '---store field name in sArray(0)         
ReDim sArray(0)         
sArray(0) = .Name 
If .EnableMultiplePageItems Then        
'---store visible items in sArray(1) and up             
For i = 1 To .PivotItems.Count                 
If .PivotItems(i).Visible Then                     
ReDim Preserve sArray(UBound(sArray) + 1)                     
sArray(UBound(sArray)) = .PivotItems(i)                 
End If             
Next i         
Else             
ReDim Preserve sArray(UBound(sArray) + 1)             
sArray(UBound(sArray)) = .CurrentPage         
End If     
End With          
'---display results in Immediate Window     
For i = 0 To UBound(sArray)         
'Debug.Print sArray(i)         
Worksheets("Filter").Range("A" & i + 1).Value = sArray(i)     
Next i End Sub

And one more question: I want to start the makro every time the filter is changed and the report is updated.How do I get this solved?
(My knowledge of <acronym title="visual basic for applications">VBA</acronym> programming is rather poor)

 

[dibbler]

Hi everybody,

I asked the same question in a german excel forum,

Excel | VBA | Excel-Funktionen | Excel-Makros | Auftrags-Programmierung | Forum

The following code works for power pivot. Alle selected page filter items are written into the Worksheet "Filter". The first page field is written in A1, the selected items below. The second page field is written to B1 and so on.

'Anwendung bei Pivot-Tabellenberichten mit OLAP-Cube-Datenquelle 
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)     
Dim pvField As PivotField, intV, intZeile As Integer, intSpalte As Integer, _         
pvItem As Variant, strItem As String, Zelle0 As Range     
With Me         
Set Zelle0 = Worksheets("Filter").Range("A1") 'Startzelle für das Eintragen der Werte         
If Zelle0 <> "" Then Zelle0.CurrentRegion.ClearContents         
'Alle slektierten Elemente der >Berichtsfelder in den rechten Nachbarzellen anzeigen         
intSpalte = 0         
For Each pvField In Target.PageFields             
intZeile = 0             
'Pivot-Feldname (ohne vorangestellte Elemente der Datenquelle             
strItem = pvField.Name             
strItem = Mid(strItem, InStrRev(strItem, ".[") + 2)             
strItem = Left(strItem, Len(strItem) - 1)             
Zelle0.Offset(intZeile, intSpalte).Value = strItem             
If pvField.DataRange.Text = "All" Then                 
'Alle Elemente sind gewählt                 
intZeile = intZeile + 1                 
Zelle0.Offset(intZeile, intSpalte).Value = "(Alle)"            
 ElseIf pvField.VisibleItemsList(1) = "" Then            
'Ein einzelnes Element ist selektiert - mehrfachselektion ist deaktiviert                 
intZeile = intZeile + 1                 
Zelle0.Offset(intZeile, intSpalte).Value = pvField.DataRange.Text             
Else                 
'Mehrfachselektion ist aktiviert                 
For Each pvItem In pvField.VisibleItemsList                    
'Pivot-Item (ohne vorangestellte Elemente der Datenquelle)                    
strItem = pvItem                     
strItem = Mid(strItem, InStr(1, strItem, "&") + 2)                     
strItem = Left(strItem, Len(strItem) - 1)                     
intZeile = intZeile + 1                     
Zelle0.Offset(intZeile, intSpalte).Value = strItem                 
Next             
End If             
intSpalte = intSpalte + 1         
Next     
End With 
End Sub