Excel get checked values from OLAP Filter

user_237109 0
Viewed 363

I would like to get the checked items from a filter in an OLAP Pivot Table.

I know I can get ahold of the field with the following code, but I can't see to figure out, how to get to the data and how to get only the checked items.

Sub Button1_Click()

 ActiveSheet.PivotTables("PivotTable1").PivotFields ("[PA Product].[Commodities].[Commodity Name]") 

End Sub

enter image description here

pivot-table vba excel
1 Answers

You can get all checked items of an OLAP filter by PivotField.VisibleItemsList.

This array only contains values, if "Select Multiple Items" is checked and not all items are checked - which is the case in your example.

So you may start with this short version:

Private Sub GetVisibleItemsOfOLAPFilterVersion1()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim i As Long

    Set pt = ActiveSheet.PivotTables(1)
    Set pf = pt.PageFields(1)
    For i = LBound(pf.VisibleItemsList) To UBound(pf.VisibleItemsList)
        Debug.Print pf.VisibleItemsList(i)
    Next i
End Sub

Following detailed version shows how to check above conditions first:

Private Sub GetVisibleItemsOfOLAPFilterVersion2()
    Dim pt As PivotTable
    Dim cf As CubeField
    Dim pf As PivotField
    Dim i As Long

    Set pt = ActiveSheet.PivotTables(1)
    For Each cf In pt.CubeFields
        If cf.Orientation = xlPageField Then
            If cf.EnableMultiplePageItems And Not cf.AllItemsVisible Then
                For Each pf In cf.PivotFields
                    For i = LBound(pf.VisibleItemsList) To UBound(pf.VisibleItemsList)
                        Debug.Print pf.VisibleItemsList(i)
                    Next i
                Next pf
            End If
        End If
    Next cf
End Sub
user_109087690
Related