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

widdman50

New Member
Joined
Jun 2, 2008
Messages
6
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!!!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
I would just point out that you don't need to use Sendkeys, which I would always avoid unless really necessary.

If you add the Adobe Distiller library in the VBA editor under Tools...References you can then control Adbobe directly. I've posted some examples before of how to do this although I have to admit others have struggled to replicate it the way they want.

I know it works however as I use it to create hundreds of documents each month.

Dom
 
Upvote 0
To modify the directory where the PDF file is saved to, simply replace the orange section in the following line of code:

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


For example, if you wanted to save your PDF file to a folder called "PDF Files" that is located in a folder called "Business Files" that is located on your D drive, then the code would read as follows:

Filename="D:\Business Files\PDF Files\" & ActiveSheet.Range("InvNbr").Value & ".pdf"

You always have to reference the directory back to the drive. So, if your directory is on your F Drive, then you need to start with "F:\" and then list each subsequent folder until arriving at your destination folder.

Does this make sense?
 
Upvote 0
Domski,

Let me just say that you were a big part of the research I did when trying to figure out how to get acrobat to take a filename automatically. Thank you for your contributions in these message boards. I actually tried your macro using Distiller and got it to work!

But my #1 priority in writing this macro was quickness and your macro took a little bit too long for what I was wanting to accomplish. In the end, I decided to go with using Send Keys.


So to all those reading this,

If you don't mind a macro that takes a little bit longer, please see Domski's recommendation here (http://www.mrexcel.com/forum/showthread.php?t=193347&highlight=pdf). It is probably a little more reliable than mine. But if quick and easy is what your looking for, than I believe that using SendKeys is a good alternative.

Best of luck to all!
 
Upvote 0
Sub MakePDFNS()

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF:", Collate:=True
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Filename = "G:\Group\Gfin\Rob_Young\" & ActiveSheet.Range("NS!C3:C4").Value & "NS"
SendKeys Filename & "{ENTER}", False

End Sub


Does not work. The line "Filename = "G:\Group...." is giving me a Type Mismatch Error. Any ideas why this might be?

Please help?

Rob
 
Upvote 0
I think that may be bacuse you are trying to use the value of more that 1 cell in that line.

try
Code:
Filename = "G:\Group\Gfin\Rob_Young\" & ActiveSheet.Range("NS!C3").Value & ActiveSheet.Range("NS!C4").Value & "NS"
intstead

Also dont think it matters but you're not saying its a .pdf either
 
Upvote 0
Thanks, I have solved the cell reference error... and no, it doesn't matter about putting .pdf at the end because when you print to your Adobe PDF it auto saves it as a pdf - no need, but i do need the file to be saved as (todays date)XX (xx = province short form)

Still getting a type mismatch error however.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top