CODEWITHSUNDEEP
vbaexcel
Rivers31334
Rivers31334

Reputation: 654

Saving a PDF automatically to a predetermined folder

I am writing a macro that automates a daily report that I send out each day. One of my last items is to write a script that saves the formatted excel sheet as a PDF (through a print to pdf option), and save it in a specific folder. I have the following written, however, it still prompts the user as to where to save it.

What would be a better way, or a way in general, to have it save automatically to a folder somewhere on my desktop?

Sub printToPDF()
    Worksheets("general_report").PageSetup.CenterVertically = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Foxit Reader PDF Printer"
End Sub

Upvotes: 0

Views: 1386

Answers (1)

Sergey Ryabov
Sergey Ryabov

Reputation: 656

This should do the work,

Sub printToPDF()

    Dim FilePath As String
    Dim FileName As String

    FilePath = "C:\Users\userName\Desktop\" 'Change as per your username

    ActiveSheet.Copy 'Copy a worksheet to a new workbook

    'It saves .PDF file at your Descrop with the name of the worksheet
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FilePath & ActiveSheet.Name, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

    'Closing a newly created workbook without saving it
    Application.DisplayAlerts = False
    ActiveWorkbook.Close

End Sub

Upvotes: 1

Related Questions