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.
Sub CreatePDF() 'Excel VBA to create PDF files.
str="C:\Test\"
ThisWorkbook.ExportAsFixedFormat xlTypePDF, str & "ReportA" & ".pdf"
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.
For i=2 To Range("A" & Rows.Count).End(xlUp).Row
Sheets(Array(1, 2, 3)).Select 'Change to suit
ActiveSheet.ExportAsFixedFormat xlTypePDF, "D:\" & Range("A" & i).Value & ".pdf"
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.