ANSWER! How to create an excel to PDF macro with auto filename

[widdman50]

After much searching and many times of trial and error, I have finally found a macro that will quickly and easily convert an excel worksheet to PDF and automatically do a save as with a filename based on content in a specified cell. Hope it works for you as well as it works for me!

The following code has been tested on Excel 2003 and Acrobat 8.0 Pro:

' This line of code specifies your directory as well as the cell or range which you want the filename to come from. As you can see, I have a specific cell with the range name "InvNbr" so that the macro knows to pull the filename from there. If you don't want to use a range name, just replace InvNbr with your cell reference, such as C4.

Filename = "C:\Folder1\SubFolder1\" & ActiveSheet.Range("InvNbr").Value & ".pdf"

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

SendKeys Filename & "{ENTER}", False

' This line of code calls the Adobe PDF printer and runs the conversion. To ensure that you replace this code correctly with your own PDF printer, simply record a macro to print to Adobe PDF and then copy and paste it here.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne02:", Collate:=True



If you don't have Acrobat Pro and are using a free version of PDF conversion software, try the following (it has been tested on Excel 2003 and CutePDF):

' 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:= _
"CutePDF Writer on CPW2:", 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 = "C:\Folder1\SubFolder1\" & ActiveSheet.Range("InvNbr").Value & ".pdf"

' 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


The beautiful thing about these macros is that you can specify your directory, so you can customize them to send your worksheet to whatever directory you want and save it under whatever filename you want. Awesome!

I hope someone else finds this useful!!!

 

[gnoke]

Hi and thanks for you replay
I have two questions,
1. I would like to name the file after text in cell A1 and place it in directory "H:\div\". Is this correctly written?
tempPDFRawFileName = ThisWorkbook.Path & "H:\div\" & Range("A1")
2. I get error message "File not found" on line
Kill tempPSFileName
My entire code now looks like this;

Sub lage_pdf()
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
 
'define file name and path in subdirectory of file where code workbook
'is stored called 'Saved Files' and picks file name up from a cell
'named as a named range call 'File_Name'
 
tempPDFRawFileName = ThisWorkbook.Path & "H:\div\" & Range("A1")
 
tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"
 
'Prints worksheet 'Output' as a pdf, an array of sheets can be printed if required
 
Sheets("Sample").PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
 
Dim mypdfDist As New PdfDistiller
 
mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
 
Kill tempPSFileName
Kill tempLogFileName
 
End Sub

Gnoke

 

[Harry Geda]

Hello WIDDMAN50,

I am not sure if you can see this post after so many years.
I am new to this and I have problem compiling code from your text.

Do you have any of it written so I can try it?

I use CutePDF and Excel 2003.

Thank you,
Harry

 

[jdeidrick]

Is there a way to code it so that you don't have to manually click "Save" when prompted by PDF Creator & to save to folder?

 

[Domski]

What software are you using to create the pdf's?

 

[davidathoegh]

Hi all,

I am also using this script which was on the first page to save files as PDF's but I am having a issue with the file save. I use Adobe Pro 9 and Excel 07.

the problem I am having is that the file save does not go to the correct location, and the send key also does not work, therefore I am manually having to input the save folder/ press save. Im not exactly sure where I am going wrong, as I have literally copy and pasted the code?

Any help or suggestions will be greatly appreciated.

Dim wb As Workbook
Dim C As Range
Dim rng As Range
Sheets("Data").Select
For Each C In Range("D8").Cells
'This range is for all holding company files
Set wb = Workbooks(C.Value)
wb.Activate
'the next line looks for the tab names - three columns from the file name
wb.Sheets(C.Offset(0, 3).Value).Select
SendKeys Filename & "{ENTER}", False

Filename = "H:\MB\Joy\Monthly Book 2010\zPDF\In production\" & ActiveSheet.Range("D8").Value & ".pdf"
ActiveSheet.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF", Collate:=True
Next C

 

[Domski]

If you're using Adobe you can control it directly by enabling the correct libraries.

This is some code I wrote for use with Adobe 6.0. Never tried it with any later version but you might want to give it a try:

Sub Create_PDF()
 
'Dom Hill, June 2008
'This code has only been tested with Adobe Acrobat 6.0 Professional
'For this code to work the Adobe Distiller VBA library must be enabled
'Select Tools...References in the VBA editor and check it from the list
 
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
 
'define file name and path in subdirectory of file where code workbook
'is stored called 'Saved Files' and picks file name up from a cell
'named as a named range call 'File_Name'
 
tempPDFRawFileName = ThisWorkbook.Path & "\Print Jobs\" & Range("File_Name")
 
tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"
 
'Prints worksheet 'Output' as a pdf, an array of sheets can be printed if required
 
Sheets("Output").PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
 
Dim mypdfDist As New PdfDistiller
 
mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
 
Kill tempPSFileName
Kill tempLogFileName
 
End Sub

Alternatively MS provided an add-in for Office 2007 onwards to allow pdf production direct from Excel which can be downloaded from here.

Dom

 

[jdeidrick]

How do you code it to automatically open the newly created PDF, then email it to addresses based off of cells in the active excel workbook ?
Using Adobe Reader 9 & PDF Creator. Thanks.

 

[davidathoegh]

Hi Domski,

thanks for your quick response. Unfortunately I cant seem to find Adobe Distiller, which doesnt really help the situation.
Not sure how to go forward with this post...


I think i'll email my friend bill gates for a solution lol

@ jdeidrick, try using http://www.rondebruin.nl/tips.htm
Has a number of tutorials and practice excel worksheets which may be able to help.

Others maybe able to assist further, but im a total noob

 

[Domski]

Have you tried using the MS add-in that I provided the link to?

Dom

 

[davidathoegh]

Hi Dom,

Under the excel ribbon i do have a Acrobat tab (which installed when I installed Acrobat Pro 9) - is that the same thing?