| “Excel developers realize that the most efficient and maintainable applications are those that make the most of Excel’s built-in functionality,augmented by VBA where appropriate.” |
from Professional Excel Development by Rob Bovey, Dennis Wallentin, Stephen Bullen, and John Green
After completing a multiple worksheet Excel model, my business user requested the reports in PDF format. Great! There goes my morning I thought. How to do I create a VBA routine to convert the multiple Excel worksheets into a single PDF file? Google it!
My 10 minute google search offered no quick solutions. Suddenly, I stopped looking for a VBA solution. The above quote flashed through my mind. Excel more often than not can do what you want without writing new VBA code. It’s the old adage, “if all you have is a hammer, everything looks like a nail”
I only needed 5 PDF files. Why not use Excel to simply produce the files manually. I did the following:
-
Set the print area for all 3 worksheets with a 3-line VBA code snippet
-
In Excel’s Developer ribbon, click the “Record Macro” button
-
Select the three worksheets by holding down the CTRL key while clicking each worksheet with the mouse left button.
-
Go the Excel’s back page by navigating to the File ribbon
-
Click “Save & Send”
-
Click “Create PDF/XPS Document”
-
Click “Create PDF/XPS”
-
Enter your file name of choice and click the “Publish” button
-
In Excel’s Developer ribbon, click the “Stop Recording” button
That’s a lot of clicks to create a 5 PDF files. Anyway, I was able to print the reports. Being the Excel junkie that I am, I couldn’t help but create a VBA routine to avoid all those clicks.
It turns out the answer to automating came from the Excel’s Macro recorder. I opened the VBA editor (ALT-F11) to see the following code created by Excel:
| Sheets(Array("Sheet1", "Sheet2 ", "Sheet3")).Select Sheets("Sheet1").Activate ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:]\test.pdf" _ , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _ :=False, OpenAfterPublish:=True |
The key was selecting the three worksheets programmatically. It also provided the syntax to create a PDF file.
TIP: Use Excel’s Macro recorder to discover VBA objects and collections. It writes horrible code but provides really good insight into the Excel VBA object model.
I did a little more tinkering to produce the following code:
| 'Set the print area for each worksheet. 'rngPrintArea1 is the named reference of a cell that contains a formula that generates an address. EX: ="A1:G"&12+Max(Sheet1!A:A) will generate A1:G14 Worksheets("Sheet1").PageSetup.PrintArea = Worksheets("Sheet1").Range("rngPrintArea1").Value Worksheets("Sheet2").PageSetup.PrintArea = Worksheets("Sheet2").Range("rngPrintArea2").Value Worksheets("Sheet3").PageSetup.PrintArea = Worksheets("Sheet3").Range("rngPrintArea3").Value 'Select the multiple sheets Sheets(Array("Sheet1", "Sheet2 ", "Sheet3")).Select ‘Set filename stFileName = ActiveWorkbook.Path & "\" & Worksheets("Sheet4").Range("A1").Value & " - report.pdf" 'Print all three to PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ stFileName _ , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _ :=False, OpenAfterPublish:=False 'Unselect the three worksheets, otherwise Excel will not allow you to change anything on the three worksheets Sheets("Sheet1").Select |
For more robust VBA code, check out MS Office Dev center’s entry on this topic.
