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!!!

 

[fecurtis]

That is helpful, thanks for the tip!

 

[corporateaccount]

Lifesaver.

I'm going to implement it into one our forms for one of our more, um, let's say picky clients.

CP

 

[corporateaccount]

One more thing. Is there a way to modify the folder this is being saved to?

 

[Domski]

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

 

[widdman50]

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?

 

[widdman50]

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!

 

[Robby87]

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

 

[random_noise]

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

try

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

 

[Robby87]

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.