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

 

[DeWaal Le Grange]

Domski it works 100%

only thing, for some reason lets say i click on any cell on the worksheet. When i run the macro it copies the data in cell a1 into the cell the is being clicked on.

Any ideas

 

[Domski]

I assume that's to do with the Senkeys line which I thought from the other code posted was needed to populate the filename in Cutepdf. Is that needed?

Dom

 

[Tigam]

I assume that's to do with the Senkeys line which I thought from the other code posted was needed to populate the filename in Cutepdf. Is that needed?

Dom

Yes... That send keys is needed *in my code*

If you aren't copying and pasting a CONCATENATE cell and just using a single cell to call and name remove this from the code.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

If it still happens, and I'm not sure why it would, it would be related to sendkeys. You can get around using those, someone in this thread had a way to do it, just follow the links.

 

[Domski]

If it still happens, and I'm not sure why it would, it would be related to sendkeys. You can get around using those, someone in this thread had a way to do it, just follow the links.<!-- / message -->

That was me but it related to using Adobe Acrobat Pro which you can control directly with VBA libraries, not CutePdf which I don't know much about but have never seen anything saying you can hence the use of Sendkeys.

Dom

 

[btardo01]

Hi all:

I think I am getting close but the following code is just looping over and over again and not actually saving any files. Can someone help me fix this:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim x As Range
With ActiveSheet
For Each x In .Range("BrokerNames")
.Range("D3").Value = x
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="CutePDF Writer on CPW2:", Collate:=True
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Filename = "I:\Users\Tardo, Brian\test\" & .Range("D3").Value & "_" & Format(.Range("D2").Value, "mmmm yy") & "_" & "Commissions Statement" & ".xls"
SendKeys Filename & "{ENTER}", False
Next
End With
End Sub

 

[nehpets12]

This is fantastic It does just what I want it to do. I am still writing the code I need, but this is a fantastic start. Thank You

 

[hennysmafter]

Hi guys,

I am running in an strange problem using the following code. I adapted the code from page 1 and ended up with this.

Sub PrintMonth1Record1()

Filename = "C:\Users\USERNAME\Desktop\" & Sheets("RPT-Month 1").Range("AG6").Value & ".pdf"
SendKeys Filename & "{ENTER}", False
Sheets("RPT-Month 1").Select
ActiveWindow.SelectedSheets.PrintPreview
Application.ActivePrinter = "Adobe PDF on Ne07:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne07:", Collate:=True
Application.Goto Sheets("PRINTINGPAGE").Range("a1")
End Sub

Where you can see USERNAME is the username of the user of the current computer. I removed it from the code for privacy reasons of my client.

This code is working perfectly but it does that only for 1 time.
so when I start excel and I click on the print button then it works good. When I do it for the second time then it cannot find the location.

I get the following error codes:

Example 1:
Cannot find: ers\USERNAME\Desktop\" & Sheets("RPT-Month 1").Range("AG6").Value & ".pdf"

Example 2:
Cannot find: sktop\" & Sheets("RPT-Month 1").Range("AG6").Value & ".pdf"

So the problem is somewhere in the filename by the first time it uses the correct filename and the second time it cannot find the correct filename. I am stuck and I hope that any of you can help me with it. Thanks in advance.

 

[random_noise]

Looks to me like something else is going on in the background which is screwing up where the keys which are being sent to so that they are not being typed in to the save window. This is the problem with using the sendkeys method.

You can check what the file name is by adding

msgbox filename

directly after the line which defines what the 'filename' variable contains to to check that the variable is being stored correctly (which I would suspect is ok).

I'm not sure exactly what the best suggestion would be for resolving the issue though- maybe adding a wait command to give the save dialogue box time to pop up?

Edit:

Ok, sorry just re-read your code again. You seem to be sending the 'sendkeys' command before you've even selected print. I'm not sure why the code works the first time around at all...

 

[gnoke]

Hi, and thanks for the code

When I run the macro everthing seems to run smoothly.
The creating adobe PDF box appear and shows the correct destination folder and file name.
The problem is that the creating pdf box does not dissapear, and the file is not created. It just hangs there...

Any ideas?

Sub Macro8()
'
' Macro8 Macro
' Macro recorded 8/10/2010 by gnoke
'
'
Filename = "H:\div\" & ActiveSheet.Range("A1").Value & ".pdf"
SendKeys Filename & "{ENTER}", False
    Application.ActivePrinter = "Adobe PDF on Ne02:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

Gnoke
http://www.excelforum.com/excel-pro...tiple-sheets-to-separate-pdf.html#post2359682

 

[Domski]

You might want to have a look at my other post about working with Adobe:

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

Dom