CODEWITHSUNDEEP
vbaexcel
Rivers31334
Rivers31334

Reputation: 654

Printing to PDF with correct file path and with correct file name

I have written a small macro that takes an daily Excel report and prints it to a specific printer (printing to PDF). When I run the macro, I am still missing the final steps. Running it as is, I still need to click the "save" button that pops up, and have to navigate to the correct file path. Is there a way to have it automatically hit the save button for me, and save the file into the correct folder (as seen in the code below)?

Sub printToPDF()

'declare variable for my file path
Dim filePath As String

'declare variable for my file name
Dim fileName As String

fileName = "Operations_Daily_Outage_Report_" & Format(Date, "yyyy-mm-dd")
filePath = "M:\Daily_Outage_Report\Active"

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

End Sub

Upvotes: 0

Views: 3710

Answers (1)

dbmitch
dbmitch

Reputation: 5386

I think everyone is trying to give you answers that you can try out. I'm not sure why you can't just test it and tell us if it works for you?

If you have a reasonably new version of Access (within last 10 years), then you should be able to use the built-in Office PDF converter

Change this line:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Foxit Reader PDF Printer"

To This:

Worksheets("general_report").ExportAsFixedFormat Type:=xlTypePDF _    
   FileName:=filePath & "\" & fileName Quality:=xlQualityStandard

Come back and tell us if it worked for you.

Upvotes: 1

Related Questions