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

 

[Robby87]

Ok, so at first I thought this was a retarded question...but then as I looked around and realized that there are NO answers anywhere online I have all but given up on it.

Here is where I am at now.
My code:

Sub MakePDFNS()

Sheets("NS").Select
ChDir "G:\Group\Gfin\RobYoung\"
FileName = "G:\Group\Gfin\RobYoung\" & Format(Now, "yyyymmdd") & "NS" & ".pdf"
SendKeys FileName & "{ENTER}", True
ActiveWindow.SelectedSheets.PrintOut copies:=1, ActivePrinter:= _
"Adobe PDF", Collate:=True
SendKeys FileName & "{ENTER}", True
DoEvents
Application.Wait Now + TimeValue("0:00:02")
SendKeys FileName & "{ENTER}", True

End Sub
(Put SendKeys in all types of locations to see what the deal was, so disregard the multiple instances of it, they have no effect on the overall code I have found - no error messages)

This code works for opening up Adobe 7.0 and prompting me to save PDF file...however, the .pdf Save As dialog box is not going to drive G:\ instead it just starts off at My Documents, and then I assume SendKeys is having no effect because no "{ENTER}" button is EVER pushed, It basically has me further from the point than would happen if I just did it manually. So looking at the code and through my own experiments I can:

1.) Activate the printer to convert to Adobe .pdf's sad I know, this is as far as I am with allll of this.

WEIRD: The filename/path that I gave the code is not even coming close to the SaveAs dialog box, instead it is just dumping it into the last cell I had highlighted in Excel (2003) - and then pushing enter (and thanks to my unneccesary iterations I get 3 lines of my file path in my active sheet)

Please, does anyone know how to send the proper filename to the TEXTBOX associated with the SAVE AS window?

Please help meeee!!


-Rob

 

[widdman50]

Rob,

I'm thinking you need to copy your last posting and put it in a brand new thread so that others can find your dilemma and possibly chime in with their ideas.

As for me, you stumped me. The macro works so well on my machine, I'm not sure why yours is having so much trouble. My last thought is to go into your "Printers and Faxes" and mess around with the settings for your Adobe printer. Maybe changing something there will have a big impact on the way the macro operates with Adobe.

Keep me posted, though, if you make any headway.

Sorry man. Good luck!!!

 

[Domski]

Feel like I'm harping on a bit I did post an example of how to control Adobe directly without using Sendkeys (which I would only consider a last resort). Just do an advanced search under my user name and you'll find it.

Dom

 

[Robby87]

Thanks Dom, checking it out right now!

 

[Domski]

I've put an updated version of the code on the thread if it's any use to anyone:

http://www.mrexcel.com/forum/showthread.php?t=193347&page=2

Dom

 

[VQCHEESE]

If i am using the code from the first post on this topic, wher i am printing to a pdf, I need some help with some code, if there is a file name already in the location, and I want over write the exsiting file. It works fine right now, but if i have a file in the location already, i want to overwrite it,i need that built in this macro too. I think it would have to go afte the SENDKEYS, so when the replace or overwrite exsisting file message pops up, move it over to "Yes" from "NO, and save. Please Help.

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

Filename = "Y:\Milk Procurement\Daily PDF\1030.pdf" '& 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

 

[Robby87]

VQCHEESE: Suggest that you read through the most recent post (3 up) by Domski regarding controlling adobe PDF. It should default to overwrite your files as it creates them... Assuming your files will always be called by the same name then there should be no issues in the overwrite, I used this code on several workbooks for a similar purpose (only want the most recent files stored as PDF's, always the same name). Read it and give it a whirl, I think you will be pleasantly surprised!

 

[VQCHEESE]

I am just using cutepdf writer. There has to something really simple to add to that to overwrite my file, its always the same name.

 

[Robby87]

My best suggestion to you is to search for "cutepdf file overwrite" or something of that nature, there are TONS of threads about many pdf writers and these will contain some or most of the answers you need. Trust me, this site is like google for excel, you can search for pretty well anything you need!

 

[Venom]

Has anyone found a way to do this, using CutePDF (free version), without "SendKeys", that doesn't leave a "corrupt" file?
"SendKeys" seems to trigger an AV popup and halts the whole thing.