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

 

[random_noise]

You also seem to have posted this here as a poll

http://www.mrexcel.com/forum/showthread.php?t=324725

 

[random_noise]

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.

what format does the date appear in? does it have a slash / in it? This is an illegal character for a file name.

try formatting the cell as a custom type like dd-mm-yyyy (or whatever for your locale)

 

[Robby87]

Ok, welcome to Friday the 13th... weird stuff. I have just reformatted my date as a 10-Jun-08 format... now when i run the macro it definitely is outputting the "filename" i gave it as an input...but instead of inputting it in the filename box, it drops it into the cell that i last selected on my spreadsheet (or inserts in the last line of vba code i had highlighted...). Why doesn't excel put the filename into the prompt box? it still comes up as the name of my entire workbook, with the option to save in My Documents.

 

[random_noise]

Sounds like either the wait period isnt giving it long enough for the dialogue box to appear or something is causing it to not be in focus.

This is the problem of using sendkeys is that there is no way of knowing for sure that the keys are being sent to the correct window.

Try making the wait period longer.

 

[random_noise]

Actually maybe you should be setting the filename BEFORE you tell the application to wait

 

[widdman50]

Robby87,

Are you using Adobe as your conversion tool or a free version of pdf software? The reason I ask is that the code for the wait period is intented to be used only with a free pdf converter, such as CutePDF. If you are using Adobe's virtual printer, then the Filename command and Send Key command need to come before you call the Adobe printer.

So, the order should be:

1. Filename command
2. Send Key command
3. Adobe Printer command

 

[Robby87]

Widdman, awesome suggestion, just got in to the office, just plugging it in right now I will let you know how it goes! thanks dude!


Rob

 

[Robby87]

Alright, crap! I think the code is working alright for calling the printer, just the filenaming is totally off. I can't make it select a drive to save in or select cells to name it :S.

it just continually gives me the option to save in the my documents folder which is quite annoying. Does the filename command give anyone else major issues? that make them want to punch their monitor?

Here is what I have now...

Sub MakePDFNS()

FileName = "G:\Group\Gfin\Rob Young\" & ActiveSheet.Range("Q3").Value & "NS"
Application.SendKeys FileName & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne01:", Collate:=True

End Sub

Any suggestions as to why it insists on saving in My Documents with the original filename for the workbook only?
I need serious help from anyone please!!

Thanks in advance,

Rob

 

[widdman50]

When you run the macro, does a window still pop up prompting you for a filename or does it save it in My Documents under the workbook name automatically?

 

[Robby87]

here is exactly the process that I see on screen when I run my macro...

The button is clicked, the Adobe PDF printing box comes up then after it "prints" it brings up the save window.

Save window is (i guess as a default) on My Documents and has the Workbooks name in the Save As: field as opposed to the name I put in the code.

the enter button is not pushed and I am essentially left with having to manually input the filename.

Ok I was just typing this as I was clicking around my workbook....apparently it works 1 time only... it saved 1 of the 8 pdf's I need to make. And it saved with the datestamp I put n front and with the "NS" behind the datestamp.... but then after saving I simply clicked the button again and right back to square one (see above).. why does this happen?

Please, I really want to get this sorted so ANYTHING you can offer would be most appreciated.

Thanks in advance,
Rob