Creating a PDF report from Excel Worksheet with VBA

Take your Excel model one step further beyond calculations. Prepare and publish the final report as a slick PDF file with a click of a button. Here is how…

Why PDF

PDF has become to be the standard file format for immutable documents.

Immutable, for those not computer programmers yet, means it cannot be changed once it is created.

PDF is highly popular for its quality, compactness, and the ability to sign electronically to render a formal document accepted by private and public institutions.

Preparing the report

What I always do, is dedicate a Worksheet for staging the report. This Worksheet is designed with all elements of the report, including colors, borders, titles, charts, and placeholders for calculated results.

For charts, I stage the data in another, helper Worksheet, either with Excel formulas filtering the chart data or populated with VBA code.

Designing this report Worksheet may require some practice. Some points to consider:

• Start with sizing the columns. It is better to shrink a column (and merge cells for titles) rather then expand and have less flexibility with all report elements. It you are presenting a table – start with the table columns. Be generous with leave extra columns (column “A”, at least).
• Print Preview (Ctrl+P) to show the dashed limits of the printed page on the Worksheet grid. This will help design your data to fit in the pages nicely.
• Publishing a PDF file is like printing the Worksheet, only the end destination is not the printer – take advantage of that: preset Page Setup parameters to help with the layout and flow of the report. For example: page margins, rows to repeat at top, page size, fit to 1 page wide, Header/Footer.
• To center titles and other data across the report width, merge cells from side to side.
• Some dynamic calculations can be presented in the report by regular Excel Worksheet functions. This includes staging data in a (hidden) helper Worksheet and fetching it into the report from there.

This training certificate was prepared for a customer as part of a complete project for managing the company’s trainings, competencies and licenses. It is staged in a dedicated Worksheet and published as a PDF certificate sent by email to the employee and his manager.

The point is, that this report Worksheet reflects the complete report. In fact, it is the report. All we need to do after having populated it with all relevant data – it to publish it as a PDF file and open for the user to view.

For more advanced reports (such as with breaking summaries, formatting etc.), I build the complete report with VBA code (except maybe the top report header.

Publishing the report Worksheet as a PDF report

If you have staged the report properly within the page margins, it should be as simple as sending the Worksheet (or Worksheets) to the printer / PDF. The order of the pages is as it is set up in the page settings, either top-bottom first, or left to right first (or right to left in RTL Worksheets).

We will be using the ExportAsFixedFormat method of the Worksheet object to render the Worksheet as a PDF file. It requires that the PDF be actually saved, meaning, it cannot just open the PDF in Acrobat Reader straight from Excel without first saving it as a PDF file.

Therefore, you have two options: you need to save the PDF report anyway, as part of your overall solution, or you need to temporary save the file just for the process.

Either way, you need to prepare a full path and filename to provide to that method.

If you need a temporary storage, I recommend using Windows Temporary folder. Here’s how I do it:

Dim strFileName As String 'PDF file name to save

Dim strPathSeparator As String  

strPathSeparator = Application.PathSeparator

strFileName = Environ("temp")

If (Right(strFileName, 1) <> strPathSeparator) Then strFileName = strFileName & strPathSeparator

strFileName = strFileName & REPORT_FILE_NAME

REPORT_FILE_NAME here is a constant I have declared elsewhere, like that:

Const REPORT_FILE_NAME = "Report_Temp.pdf"

One way or another, make sure you prepare your file name with the full path.

The above piece of code can be part of the Subroutine you assign to a button on the Worksheet for the user to call for the report. Don’t forget to add the call to the PublishPDF subroutine described next, of course.

The following is a generic Subroutine that receives the Worksheet name and the file name as arguments and opens the PDF for the user:

Sub PublishPDF(strSheet As String, strFileName As String)

    Dim shtCurrent As Worksheet

    On Error GoTo FiledSub

    Application.ScreenUpdating = False

    Set shtCurrent = ActiveSheet

    UnhideSheet strSheet

    Sheets(strSheet).Select

    ActiveSheet.ExportAsFixedFormat _

        Type:=xlTypePDF, _

        Filename:=strFileName, _

        Quality:=xlQualityStandard, _

        IncludeDocProperties:=True, _

        IgnorePrintAreas:=False, _

        OpenAfterPublish:=True

CloseSub:

    HideSheet strSheet

    shtCurrent.Activate

    Application.ScreenUpdating = True

    Exit Sub

FiledSub:

    GoTo CloseSub

End Sub

At the heart of our Subroutine is of course the invocation of the ExportAsFixedFormat method call.

The OpenAfterPublish argument is set to True, to open the PDF file after saving it.

Note that I am taking care to unhide the report Worksheet before publishing it – it will not work with a hidden Worksheet.

Before I close the Subroutine I hide it back again and activate the Worksheet that was active when this Subroutine was called. Coupled with turning off ScreenUpdating the user should not experience any flickering or otherwise changes in sight.

For completeness, I’m sharing here a simple version of HideSheet and UnhideSheet I am using:

Sub HideSheet(ByVal SheetName As String)

    ActiveWorkbook.Sheets(SheetName).Visible = xlSheetHidden

End Sub

Sub UnhideSheet(ByVal SheetName As String)

    ActiveWorkbook.Sheets(SheetName).Visible = xlSheetVisible

End Sub

Let’s take a break to examine another example of a PDF report featured in one of my Excel products:

And another one:

What if your reports span multiple Worksheets?

Some reports are staged across multiple Worksheets.

If this is the case, you can select all of these Worksheets together as an array of Worksheets, and the ExportAsFixedFormat method would process them all. Here’s one way to do that:

Sheets(Array("Sheet1", "Sheet2")).Select

ActiveSheet.ExportAsFixedFormat…

Before you run off, please share this with your colleagues and friends – they will thank you (and I will, too!).