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

corporateaccount

Board Regular
Joined
Aug 11, 2004
Messages
64
Lifesaver.

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

CP
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

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

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

Board Regular
Joined
May 9, 2008
Messages
128
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

Active Member
Joined
Dec 19, 2007
Messages
367
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
 

Robby87

Board Regular
Joined
May 9, 2008
Messages
128
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.
 

Forum statistics

Threads
1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top