How to Loop Through OLAP Pivot table Filters in vba

Question

I Am new to VBA. I have Excel files which have some pivot tables created by connecting to OLAP Cubes.

I Need help on how to Loop through the filter column in the Pivot Tables the same How I loop through the Row Fields, column Fields & Data Fields as described in following code.

Private Sub CommandButton1_Click()    
    Dim wb As Workbook
    Dim wsheet As Worksheet
    Dim PvtTbl As PivotTable
    Dim pvtFld As PivotField
    Dim wsmapping As Worksheet

    ' Set the Variable Values
    Set wb = ActiveWorkbook
    Set wsmapping = Worksheets("Mapping")

    'WorkBook Active sheets
    For Each wsheet In wb.Worksheets
        'Current Worksheet Pivot Table
        For Each PvtTbl In wsheet.PivotTables

            'Current Pivot Table Row Fields
            For Each PvtRowFld In PvtTbl.RowFields
                PvtRowFldName = PvtRowFld.Name
                RowFldPos = PvtRowFld.Position
                RowField = Left(PvtRowFldName, InStrRev(PvtRowFldName, ".") - 1)

                NewRowField = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(RowField, wsmapping.Range("A2:B10"), 2, False), "No Mapping")
                'MsgBox NewPvtfld

                If NewRowField <> "No Mapping" Then
                    PvtTbl.CubeFields(RowField).Orientation = xlHidden

                    With PvtTbl.CubeFields(NewRowField)
                        .Orientation = xlRowField
                        .Position = RowFldPos
                    End With
                End If
            Next PvtRowFld

            'Current Pivot Table Column Fields
            For Each PvtColFld In PvtTbl.ColumnFields
                PvtColFldName = PvtColFld.Name
                ColFldPos = PvtColFld.Position
                ColField = Left(PvtColFldName, InStrRev(PvtColFldName, ".") - 1)
                NewColField = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(ColField, wsmapping.Range("A2:B10"), 2, False), "No Mapping")
                MsgBox NewColField

                If NewColField <> "No Mapping" Then
                    PvtTbl.CubeFields(ColField).Orientation = xlHidden

                    With PvtTbl.CubeFields(NewColField)
                        .Orientation = xlColumnField
                        .Position = ColFldPos
                    End With
                End If
            Next PvtColFld

            'Current Pivot Table Measure
            For Each PvtMeasure In PvtTbl.DataFields
                PvtMeasureName = PvtMeasure.Name
                PvtMeasurePos = PvtMeasure.Position

                NewMeasureName = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(PvtMeasureName, wsmapping.Range("A2:B10"), 2, False), "No Mapping")
                MsgBox NewMeasureName

                If NewColField <> "No Mapping" Then
                    PvtTbl.CubeFields(PvtMeasureName).Orientation = xlHidden

                    With PvtTbl.CubeFields(NewMeasureName)
                        .Orientation = xlDataField
                        .Position = PvtMeasurePos
                    End With
                End If
            Next PvtMeasure

            'Current Pivot Table Active Filters
            For Each pvtFilter In PvtTbl.ActiveFilters
                MsgBox pvtFilter.Name
            Next pvtFilter
        Next PvtTbl  
    Next wsheet
End Sub

In the Above code PvtTbl.ActiveFilters For each loop is not Looping the Pivot table filter fields, Though I have many Active filters on the Pivot Table.

I see some references to PvtTbl.PivotFields which is looping through all the fields like rows, columns, Measures and Filters too. Using this I couldn't able to find that where the field is coming from whether it is from column/row/filter ?

Asger Asger · Accepted Answer · 2019-07-04T18:26:07

Filter fields above a pivot table are also PivotFields which are used as PageField.

As the term "filter" is something different (e. g. filtering each pivotfield additionally), I suggest to use another name for it, e. g. "pvtPageField".

You may reference it either as PivotTable.PageField or as PivotTable.CubeField, when its Orientation is xlPageField:

' simple approach:
Dim pvtPagefield As PivotField
For Each pvtPagefield In PvtTbl.PageFields
    Debug.Print pvtPagefield.Name
    Debug.Print pvtPagefield.Value
    Debug.Print pvtPagefield.SourceName
    Debug.Print pvtPagefield.Caption
Next pvtPagefield

' better approach:
Dim pvtCubeField As CubeField
For Each pvtCubeField In PvtTbl.CubeFields
    If pvtCubeField.Orientation = xlPageField Then
        Debug.Print pvtCubeField.Position
        Debug.Print pvtCubeField.Name
        Debug.Print pvtCubeField.Value
        Debug.Print pvtCubeField.Caption
        For Each pvtPagefield In pvtCubeField.PivotFields
            Debug.Print pvtPagefield.Name
            Debug.Print pvtPagefield.Value
            Debug.Print pvtPagefield.SourceName
            Debug.Print pvtPagefield.Caption
        Next pvtPagefield
    End If
Next pvtCubeField

I also suggest to use Option Explicitas first code line of every module.
That enforces a Dim of every variable, e. g.

Dim pvtFld As PivotField, PvtRowFld As PivotField, PvtColFld As PivotField
Dim PvtRowFldName As String, RowField As String, RowFldPos As Variant, NewRowField As String
Dim PvtColFldName As String, ColField As String, ColFldPos As Variant, NewColField As String

How to Loop Through OLAP Pivot table Filters in vba

1 Answers