Excel – Saving worksheet to PDF with automated filename

excelvba

I've written the macro below so that the worksheet in use will print to PDF and the filename to be populated based on the contents of a specific cell.

It almost works but for some reason whenever I run it states that 'The output folder does not exist'. This is because for some reason the file path is doubling up

(i.e.S:\Purchase Orders\2013\TEST – Damien\2013 Pos\S:\Purchase Orders\2013\TEST – Damien\2013 POs\filename.pdf

Sub SaveToPDF()


' This line of code calls your PDF printer and runs the conversion. Record your own macro to call your PDF printer and copy and paste it here.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Bullzip PDF Printer", Collate:=True

' This set of code tells the macro to pause for 2 seconds. This will allow for the PDF printer to run through its process and prompt you for a filename.

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

' This line of code specifies your directory as well as the cell or range which you want the filename to come from.

FileName = "S:\Purchase Orders\2013\TEST - Damien\2013 POs\" & ActiveSheet.Range("BT12").Value

' This line of code sends the filename characters and the ENTER key to the active application (i.e. the prompt window). The "False" statement allows the macro to continue running without waiting for the keys to be processed.

SendKeys FileName & "{ENTER}", False

End Sub

If anyone could help me fix this code that'd be great.

Thanks in advance.

Best Answer

try this:

SendKeys "^(a)" & "{DELETE}" & Filename & "{ENTER}", False

it deletes old file name in print dialog box

ps: in my test it only worked when i set waitTime to 5 seconds, but may be i have slow machine

Related Topic