Creating PDF Files with Excel VBA

Creating PDF files is a very useful tool as it allows you to put data in a format which cannot be altered.  Creating this automatically is awesome.  Adobe has created possibly the most widely used business application for PDF files.  This can be downloaded free, Adobe Acrobat Reader.  If you don’t already have it you will need reader to run this report.  It can be downloaded free from the Adobe website.
 
http://get.adobe.com/reader/
 
The following will print all of the visible worksheets within a workbook.  If there are sheets which you do not wish included in the PDF file, I guess the easiest way to get around this would be to to hide the sheets you don’t want printed before you run the procedure.

Option Explicit

Sub CreatePDF() 'Excel VBA to create PDF files.
Dim str As String

str="C:\Test\"
ThisWorkbook.ExportAsFixedFormat xlTypePDF, str & "ReportA" & ".pdf"
End Sub


To use it change the file path remembering to include the back slash \ after your directory name. If you are using the below code to test workings then ensure you have a folder path called C:\Test\

The files is saved as ReportA however it maybe more flexible if you link the file name to a cell within the workbook.

Creating a Batch of PDF Files from Excel


The following procedure will create a batch of reports and save them as PDF documents. One thing to remember is it will copy the first 3 sheets (change to suit) to PDF.

Sub PDFBatch() 'Excel VBA to bach some files to PDF (very useful).
Dim i As Integer

For i=2 To Range("A" & Rows.Count).End(xlUp).Row
Sheet1.[a1]=Range("C" & i).Value
Sheets(Array(1, 2, 3)).Select 'Change to suit
ActiveSheet.ExportAsFixedFormat xlTypePDF, "D:\" & Range("A" & i).Value & ".pdf"
Next i
End Sub


The above is very useful if you have lots of reports to push into PDF format. I will attach an example of the above to give the procedure context.