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.
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:
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.
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:
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!).
Join today to the Excel VBA Inner Circle with Mor Sagmon.
Get a weekly lesson, weekly live Q&A, monthly deep-dive workshop, monthly challenge and other activities.50% Complete
Once you submit your details, you'll receive an email with a confirmation link. That's it! you're subscribed!