Print Reports into PDF Files

Let's do something fun: print all our territory-level call panel worksheets into PDF files. It has become more and more popular among biotech managers to distribute their reports in PDF format to avoid any formatting discrepancies as well as potential data tempering. This is suitable for any reports that do not require user-interaction, such as IC Payout Scorecard, Goal Attainment Scorecard, etc.

Before we can programatically do this, we need to install a free software called "PDFCreator" on the report creator's machine (not necessary on end-users' computers). The download link is here. After proper installation, go to "VBA Editor" in Excel, choose "Tools" -> "References", and select "PDFCreator", click "OK". This allows VBA access to all the properties and methods of PDFCreator library.

The code to print all 99 territory-level workbooks into PDF files is shown below. Note that for demonstration purpose we will only print out the first two pages for each file. You can download the example file here.

Change the "mkdPDFName" and "mkdPDFPath" variables to your specifications. If you would like to print out all pages (not just the first two), remove "From" and "To" parameters in the above "ActiveSheet.PrintOut" line.

Combining what we have done in this post and the past one, we can easily print out Excel worksheets as PDF files, then immediately email them to various recipients via Outlook.