Avatar of deskchains
deskchainsFlag for United States of America asked on

Change/edit exported PDF name

I use(thanks to help received from EE members) the following code to export PDF from excel.  It first prompts you for a directory to save to.  It then filters through a pivot table and exports a new report for each item in the filter and names the PDF for the "filtered" item.

Function GetFolder(strPath As String) As String
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
GetFolder = sItem
Set fldr = Nothing
End Function

Private Sub CommandButton2_Click()

Dim pf As PivotField, pi As PivotItem

With ActiveSheet.PivotTables("Master2")
myFolder = GetFolder("F:\Temp\")
For Each pf In .PageFields 'pf is pivot field not page field
      Debug.Print pf.Name
        For Each pi In pf.PivotItems
            pf.CurrentPage = pi.Name
  '*********************** OLD PRINT RANGE  *****************
            ' Set rgPrint = Range("A1:P100")
'********************* New Print Range Below ************************
            Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    Set rgPrint = Range("A5:f125")
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.3)
        .RightMargin = Application.InchesToPoints(0.3)
        .TopMargin = Application.InchesToPoints(0.3)
        .BottomMargin = Application.InchesToPoints(0.3)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 300
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlOverThenDown
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 2
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    ' **** back to orig
    Application.PrintCommunication = True
            rgPrint.ExportAsFixedFormat Type:=xlTypePDF, IgnorePrintAreas:=False, Filename:=myFolder & "\" & Cells(6, 2).Value, Quality:=xlQualityStandard
    End With
       Next pi
    Next pf
 End With

End Sub

I would like to be able to add/append something with the filter item in the name.  If the pivot filter is at say 10, as it is now the PDF would be named 10.PDF.  I would like to change this so that the report is named Report10.pdf.

Microsoft ExcelWindows 7

Avatar of undefined
Last Comment

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Your help has saved me hundreds of hours of internet surfing.