Reference Pivot table filter

Question

I am trying to reference a PIVOT table filter to use as the Save As name for generating a PDF. I cannot seem to find any combination of PIVOT table object reference to do this.

Sub Deferred_Rent_To_PDF()

Dim strWorksheet As String
Dim strPivotTable As String
Dim pdfFilename As Variant
Dim strDocName As String
Dim ptDeferredRent As pivotTable

strWorksheet = "Deferred"
strPivotTable = "DeferredRent" 

Set ptDeferredRent = Worksheets(strWorksheet).PivotTables(strPivotTable)
'strDocName = ptDeferredRent.                  <----- THIS IS WHERE I NEED HELP

pdfFilename = Application.GetSaveAsFilename(InitialFileName:=strDocName, _
    FileFilter:="PDF, *.pdf", Title:="Save As PDF")

    If pdfFilename <> False Then

    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=pdfFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    End If

End Sub

Answer 1

Try something like this:

You are looking for field_name.CurrentPage but only if field_name.Orientation = xlPageField (i.e. filter field and not row or data or column field or hidden)

Sub Deferred_Rent_To_PDF()

Dim strWorksheet As String
Dim strPivotTable As String
Dim pdfFilename As Variant
Dim strDocName As String
Dim ptDeferredRent As PivotTable

strWorksheet = "Pivot (2)" '"Deferred"
strPivotTable = "PivotTable7" '"DeferredRent"

ThisWorkbook.Sheets(strWorksheet).Activate

Set ptDeferredRent = Worksheets(strWorksheet).PivotTables(strPivotTable)
'strDocName = ptDeferredRent.                  <----- THIS IS WHERE I NEED HELP

strDocName = Get_Pivot_filter_field(ptDeferredRent)

If strDocName <> "not found" Then
    Debug.Print strDocName

    pdfFilename = Application.GetSaveAsFilename(InitialFileName:=strDocName, _
        FileFilter:="PDF, *.pdf", Title:="Save As PDF")

        If pdfFilename <> False Then

        ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=pdfFilename, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=False, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
        End If
End If

End Sub

Function Get_Pivot_filter_field(pvt As PivotTable)

    'On Error Resume Next

    Debug.Print pvt.Name
    Pivot_Table_Name = pvt.Name
    Debug.Print pvt.PivotFields.Count

    Get_Pivot_filter_field = "not found"

    For Each field_name In pvt.VisibleFields 'pvt.PivotFields
        If pivot_field_active(Pivot_Table_Name, field_name) Then

            With field_name

                Debug.Print field_name & " " & .Orientation

                If .Orientation = xlPageField Then 'xlDataField (4)' 'xlColumnField (2)' 'xlHidden (0)' 'xlPageField (3)' 'xlRowField (1)'

                    Debug.Print field_name & " " & .Orientation & .CurrentPage
                    Get_Pivot_filter_field = .CurrentPage

                Else
                    Debug.Print field_name & " not filter field"
                End If
            End With
        Else
            Debug.Print field_name & " not active"
        End If
    Next

End Function

Function pivot_field_active(ByVal Pivot_Table_Name As String, ByVal strName As String) As Boolean

    Dim strTemp As String
    On Error Resume Next
    With ActiveSheet.PivotTables(Pivot_Table_Name).PivotFields(strName)
        If .NumberFormat = "$#,##0" Then
            'Do nothing no error
        End If
        If .CurrentPage Then
            Err.Clear
        End If
    End With
    If Err = 0 Then pivot_field_active = True Else pivot_field_active = False

End Function

What I do here is loop through all pvt.VisibleFields where pvt is the pivot table you pass into the function pvt.VisibleFields(pvt)

If .Orientation = xlPageField then it is a filter field and if it is then return the .CurrentPage as the result of the function Get_Pivot_filter_field otherwise return "not found"

Then use this .CurrentPage as your PDF name.

Full example here: https://drive.google.com/file/d/1HkeJVgKeFeCuj2ItRn2s90ozy41zlCVL/view?usp=sharing