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

 

[rexel12]

This works really well with cute PDF for a single worksheet. I have tried to modify the code to loop through an entire workbook and create a PDF for each worksheet.

Unfortunately my code seems to view the file name as the path and will not save the PDF's.

Any ideas on what changes to make?

Regards

Sub pdf()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

Sheets(ws.Name).Activate

Filename = "Q:\Users\GF\RH\Ad-Hoc\JMS\Test\" & ActiveSheet.Range("G1").Value


ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True


SendKeys Filename & "{ENTER}", False


newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime


Next ws



End Sub

 

[mrexcelid]

Very innovative

Thx to you and all others for your inputs.

Can you help me with doing this,

Your macro is to print and save file with some name.pdf, now

I have pivot report,having page fields as email accounts.

i can print a report for every email acount, But can i send the same report to that email acount ?if Yes

Can you please give idea how i can do that?

Regards
Shri

 

[mrexcelid]

Ook found BIGGOAN's reply

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

But will it send email one after another saved pdf? have to check.

Thx.

Very innovative

Thx to you and all others for your inputs.

Can you help me with doing this,

Your macro is to print and save file with some name.pdf, now

I have pivot report,having page fields as email accounts.

i can print a report for every email acount, But can i send the same report to that email acount ?if Yes

Can you please give idea how i can do that?

Regards
Shri

 

[DeWaal Le Grange]

for some reason, this does not work... if got the following vba

Sub MakePDFNS()
Filename = "C:\Documents and Settings\sales13\My Documents\New Folder\" & ActiveSheet.Range("a1").Value & ".pdf"
SendKeys Filename & "{ENTER}", False
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

End Sub

if attached the vba to a button it paste's the file name in a random cell when i press it, any ideas....

it doesnt do any conversion to PDF at all...

Thanks

Dewaal

 

[Domski]

for some reason, this does not work... if got the following vba

Sub MakePDFNS()
Filename = "C:\Documents and Settings\sales13\My Documents\New Folder\" & ActiveSheet.Range("a1").Value & ".pdf"
SendKeys Filename & "{ENTER}", False
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

End Sub

if attached the vba to a button it paste's the file name in a random cell when i press it, any ideas....

it doesnt do any conversion to PDF at all...

Thanks

Dewaal

You can control Adobe Acrobat without using Sendkeys. Have a look at the code I posted here:

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

Dom

 

[DeWaal Le Grange]

domski

thank you, but can you explain this to me step by step... i dont understand your code...

thanks a mill, sorry

 

[Domski]

I'm not sure I can be much more descriptive than the comments that I have already put in the code (lines beginning with ').

I'm afraid I'm a little short of time this afternoon and then not going to be on-line until mid-next week.

Hope you get what you want sorted.

Dom

 

[Tigam]

This works really well with cute PDF for a single worksheet. I have tried to modify the code to loop through an entire workbook and create a PDF for each worksheet.

Unfortunately my code seems to view the file name as the path and will not save the PDF's.

Any ideas on what changes to make?

Ok... So it's an old thread... But you all helped me so I figured I better come back and show what worked for me. Keep in mind I'm very novice. It's likely sloppy code but I got it to work and I'm happy as all get out.

It'll save to your desktop, or at least it does mine, but the short term clutter is well worth the time it's saving me.

Ok... I'm using Excel 2003 and the free version CutePDF Writer to get this done.

' Code to set up a loop for all worksheets in the Workbook
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
' Activate the sheet so you can pull information from that single sheet
    Sheets(ws.Name).Activate
' If you have made the sheet invisable then it won't PDF it, and skip to the next Worksheet
    If Sheets(ws.Name).Visible = False Then GoTo Skipit:
' I made the file name to a CONCATENATE command in Excel to this Cell.  That way I didn't have to deal with the destination doesn't exist problem and I still get to name the file what I want.
    Range("A58").Select
    Selection.Copy
    Range("A59").Select
' Named it to make it easier to figure out what's going on if I ever have to come back to this code.
    Filename = ActiveSheet.Range("A59")
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
' Stole this... Thank you rexel12
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "CutePDF Writer on CPW2:", Collate:=True
    
    SendKeys Filename & "{ENTER}", False


newHour = Hour(Now())
newMinute = Minute(Now())
' I don't know if my computer is just slow or what... But 3 seconds seems to work better than two.
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

    Range("A59").Select
    Selection.Copy
Skipit:
    Next ws
    
    
       
    
End Sub

 

[DeWaal Le Grange]

Hi tigam

this works 100% for me, but is there any way to modify it to only convert the current sheet you are working on...

Have a fab day

thanks

dewaal

 

[Domski]

Hi dewaal,

I've adapted the code above to just work on the active sheet picking the file name up from cell A1 (change as required).

Not tested as don't have CutePDF but think it should work:

Sub PDF_Sheet()
Dim Filename As String
    
With ActiveSheet
   
    Filename = .Range("A1")
    .PrintOut Copies:=1, ActivePrinter:= _
    "CutePDF Writer on CPW2:", Collate:=True
    
    SendKeys Filename & "{ENTER}", False
End With
    
End Sub

Hope it helps,

Dom