locked
How to save a PDF to a preset folder with a preset file name? RRS feed

  • Question

  • Hello.  I have a VBA subroutine that prints the active worksheet to a PDF.  When the subroutine is executed, it opens a 'save as' dialogue box asking me where to save the output PDF, and with what name (screenshot).  How do I ensure that this 'save as' box does not appear, and the PDF automatically saves to a preset location, with a preset file name?

    Thanks in advance.

    My full code is below:

    Sub PrintPDF()
        
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 0
        .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 = ""
    End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        
        
    End Sub

    Tuesday, December 11, 2018 1:47 PM

All replies

  • Hi,

    To save the active sheet as PDF use the following code:

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                        Filename:="YourPath\FileName.pdf", _
                                        Quality:=xlQualityStandard, _
                                        IncludeDocProperties:=True, _
                                        IgnorePrintAreas:=False, _
                                        OpenAfterPublish:=True


    Guy Zommer

    Tuesday, December 11, 2018 3:30 PM
  • OK thanks for that.  So are you saying I should replace all of my code with your code?  My code ensures other things, e.g. it prints to PDF while ensuring 'Fit to paper width' is selected.
    Tuesday, December 11, 2018 3:34 PM
  • Hi,

    Instead of print to PDF (ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False) you can use my code to save your file to PDF at specific location


    Guy Zommer

    Tuesday, December 11, 2018 3:39 PM
  • OK so.  To be clear, I replace

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False

    with

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                        Filename:="YourPath\FileName.pdf", _
                                        Quality:=xlQualityStandard, _
                                        IncludeDocProperties:=True, _
                                        IgnorePrintAreas:=False, _
                                        OpenAfterPublish:=True

    Is that correct?

    Tuesday, December 11, 2018 3:41 PM
  • Hi,

    Yes if you need to export your worksheet as PDF.

    Change The Filename:=.... for example "c:\temp\MyFile.pdf"


    Guy Zommer

    Tuesday, December 11, 2018 3:44 PM
  • OK thanks.  Give me a day or two to test that and report back here.
    Tuesday, December 11, 2018 3:45 PM
  • I made the change and ran my code, and it resulted in this error (screenshot):

    Run-time error '1004':

    Document not saved.  The document may be open, or an error may have been encountered when saving.

    Thoughts?

    Tuesday, December 11, 2018 3:53 PM
  • The PDF file that you exported is open you can change the code to

    OpenAfterPublish:= False

    or close the PDF file before re-running the code


    Guy Zommer

    Tuesday, December 11, 2018 3:58 PM
  • OK I changed OpenAfterPublish from False to True, but encountered the same error.  Also, at the time of error, I don't see the PDF open at all--my PDF viewer (Acrobat XI) isn't open.  Thoughts?
    Tuesday, December 11, 2018 4:25 PM