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

 

[Domski]

Nope, you don't need Acrobat to create PDF's if you have this add-in installed:

http://www.microsoft.com/downloads/...FamilyID=4d951911-3e7e-4ae6-b059-a2e79ed87041

Dom

 

[davidathoegh]

sorry for sounding stupid, but when i install this add in, does that mean the distiller programme will come up?/ therefore allowing me to use the script you provided earier?

or...

 

[Domski]

Nope, that code is specific to Adobe.

I've not tried vba with this add-in but hopefully this will point you in the right direction:

http://www.rondebruin.nl/pdf.htm

Dom

 

[davidathoegh]

Dom, really appreciate the help you have given. WHEN i get it sorted (glass is half full kinda guy), ill post the code

 

[davidathoegh]

The microsoft addin was 100 times better and quicker to use. I would suggest that anyone wanting to save as PDF use this method over the cutepdf or adobe pro.

After the install, this code was used, and does exactly what i want it to do.
Thanks to everyone who helped me get this solution.

Regards,

'SaveFile as PDF using Microsoft pdf addin

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\User\Superman\Secret_CIA_Files\" & ActiveWorkbook.Name, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

 

[Domski]

The microsoft addin was 100 times better and quicker to use. I would suggest that anyone wanting to save as PDF use this method over the cutepdf or adobe pro.

After the install, this code was used, and does exactly what i want it to do.
Thanks to everyone who helped me get this solution.

Regards,

'SaveFile as PDF using Microsoft pdf addin

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\User\Superman\Secret_CIA_Files\" & ActiveWorkbook.Name, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Nice and simple . Alas I'm stuck with Adobe and Excel 2003 at work at the moment but some time next year we're 'scheduled' to move onto 2010.

Dom

 

[crychnun]

Thankful for posting this, but I have a small question.

I am trying to use this in a loop and not able to do it.

My code looks like this.


Private Sub CommandButton1_Click()
Dim Rw As Long
For Rw = 2 To Sheet1.UsedRange.Rows.Count
Sheet2.Cells(1, 1).Value = Sheet1.Cells(Rw, 1).Value
Filename = "D:\New folder\" & ActiveSheet.Range("I7").Value & ".pdf"
SendKeys Filename & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne02:", Collate:=True
Next Rw
End Sub

I have two sheets, Sheet 1 contains payroll data and sheet 2 contains a salary slip template in which the data changes corresponding to the value in Cell A1. I need to save/print pdf of payslips for all the employee numbers in the payroll data.

Can you please help me with this since we have more than 1000 staff and its really painful to save as pdf each time after changing the values in the cell.

 

[crychnun]

I finally figured out a way to get this done, am posting it here in case it helps.

Sub Print_Pdf_All()
Dim Rw As Long
For Rw = 2 To Sheet1.UsedRange.Rows.Count
Sheet2.Cells(1, 1).Value = Sheet1.Cells(Rw, 1).Value

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"D:\Payslips - April 2012\UCC\" & Sheet2.Range("I7"), Quality _
:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next Rw
End Sub

Furthermore, I found another macro which I edited a bit and got to the below which instantly sends the pdf as an email. So I am able to run it in two parts, one for saving the payslips as pdf and second to email the payslips to employees' individual IDs.

Sub MailPaySlips()
Dim olApp As Object
Dim olMItem As Object
Dim MyPath As String
Dim MyFile As String
Dim LastRow As Long
Dim i As Long

' Change the path to the folder containing the pdf files
MyPath = "C:\Users\a.iyer\Desktop\New folder"

If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

Set olApp = CreateObject("Outlook.Application")



LastRow = Cells(Rows.Count, "a").End(xlUp).Row

For i = 2 To LastRow
Set olMItem = olApp.CreateItem(0)
If Cells(i, "a").Value <> "" And Cells(i, "c").Value <> "" Then
MyFile = MyPath & Cells(i, "a").Value & ".pdf"
If Dir(MyFile) <> "" Then
With olMItem
.To = Cells(i, "c").Value
.Subject = "Pay Slip"
.Body = "Please find attached a copy of your pay slip."
.Attachments.Add MyFile
.Save
'.Send
End With
Cells(i, "d").Value = "Sent"
Else
Cells(i, "d").Value = "Not Sent - PDF file is not available."
End If
Else
Cells(i, "d").Value = "Not Sent - staff code and/or email address is not available."
End If
Next i

End Sub

Thanks again...

 

[Cindy Ellis]

I finally figured out a way to get this done, am posting it here in case it helps.

First...welcome to Mr. Excel!
Thank you for posting your solution for others to take advantage of.
Sometimes a post to an old thread doesn't get a timely response because it doesn't show up in the unanswered threads list. If a similar situation comes up again (where the previous posts are a year old or so), consider opening a new thread, pointing to the old one for reference.
Hope we can help in the future...

 

[crychnun]

Thank you for your advice, Cindy...

I shall certainly consider it while posting in the future.

Look forward to lots of knowledge sharing...


Regards,
Arvind