Saving Excel Worksheets as PDFs Using Macros

This Tech Tip was provided by Christina Butler, Research Analyst, Tufts University.

Our office produces many department-level and individual-level reports. These reports are often created in an Excel workbook containing worksheets named after each department or individual. For example, we create advising reports for all of our academic advisors in an Excel workbook that contains a worksheet for each individual advisor’s report. Because we give each advisor his/her own report as a PDF, we must save each workbook as a PDF file. The process can actually be completed quickly through the use of VBA code in Excel, as this Tech Tip explains. I have included a link to the VBA code along with some directions, but for more documentation on using this code, please visit the Microsoft website.

1. Within your Excel workbook, create a worksheet titled “WorksheetNames.” In column A of the “WorksheetNames” worksheet, list all the names of the worksheets for which you plan to create a PDF. It is important that the names of the worksheets be reproduced exactly.

tech tip excel image.jpg

2. Open the Visual Basic Editor in Excel (Developer - Visual Basic).

a. Because the Developer tab does not appear in Excel 2010 by default, it may need to be enabled.

To enable the Developer tab:

  • On the File tab, choose Options to open the Excel Options dialog box.
  • Click Customize Ribbon on the left side of the dialog box.
  • Under Choose commands from the left side of the dialog box, select Popular Commands.
  • Under Customize the ribbon on the right side of the dialog box, select Main tabs, and then select the Developer check box.
  • Click OK.

b. Additional directions on using VBA in Excel can be accessed on the Microsoft website.

c. Once the VBA Editor is open, you may need to change the view so that you are in “Code View.” To do this, go to View - Code.

3. Paste the code located in the Word document (and shown below as reference) into the Visual Basic Editor. The variable “numSheets” will need to be replaced with the number of worksheets that will be saved as a PDF file. (In the example above, it would be 4). Also, the filename should be modified to reflect the appropriate file path. Note that lines starting with an apostrophe are macro comments that will not be executed by Excel VBA. A macro comment is a piece of text that provides information about the macro.

4. Run the code and verify that the PDF files have been saved in the appropriate destination folder. You may encounter an error if the worksheets you are trying to save as PDF files are empty.



 

 Comment

 
To add a comment, Sign In
Total Comments: 26
 
Melanie posted on 11/14/2012 2:14 PM
What a great way to streamline work! Terrific tip.
Erin posted on 11/14/2012 2:21 PM
We do something very similar (needing Excel reports saved as PDFs) and though I use macros a lot, I had never thought of using them this way. Definitely something I'll try out!
Maren posted on 11/14/2012 2:27 PM
This looks like it could save a great amount of time... I'll definitely be giving this a try before I start working on spring semester reports. Thanks!
Gary posted on 11/14/2012 2:29 PM
Great application of Visual Basic for Applications - Excel. Loops like this can be a huge time saver. Here's a few additional points to consider:
1.) Alt + F11 brings up the Visual Basic Editor
2.) The Project Explorer window on the left shows all open workbooks(aka "Projects"), as well as all Hidden workbooks. IF you save VBA code into the workbook you're using, it travels with that workbook and will not be available to use on other workbooks. To save code that you want to use across different workbooks, you should save it into your PERSONAL.XLSB project. If you have never recorded a macro before, then this workbook will not exist. First go back to the worksheet view, click the Record Macro button, click your mouse in a few cells, and stop recording the macro. Then PERSONAL.XLSB will appear as a project.
3.) To keep your code bits organized, create a new module within PERSONAL.XLSB by right-clicking on it in the project explorer, and choose Insert > Module. It will be named something like "Module1" by default - you can rename it to something more meaningful like "CreatePDFs."
4.) Be careful, you cannot assume that the highlighted code module in the Project Explorer corresponds to the open code window! Double-click the module you want to add code to to be sure it's the right one!
Angela posted on 11/14/2012 2:30 PM
This is a great example of how VBA can be used to improve routine processes!
Kelly posted on 11/14/2012 2:34 PM
Great time saver - did not realize you could use a VBA macro to create PDFs from multiple excel worksheets. Thanks for providing the code too.
Reuben posted on 11/14/2012 2:49 PM
I don't use VBA nearly as much as I should, and this is a great example to get people started using it and to remind us that it can really be a time saver. Thanks for the tip!
Raj posted on 11/14/2012 2:50 PM
Great tip!!
Erin posted on 11/14/2012 3:10 PM
Thanks for the code. I'm working on a project that requires everything be saved as PDF and this will save me a substantial amount of time!
Laura posted on 11/14/2012 3:19 PM
Thank you for sharing this tip. This looks like a great time saver. I don't use VBA, but I would love to get started. If anyone has any recommendations for beginner resources, I would really appreciate your advice. Thanks!
Nicole posted on 11/14/2012 3:27 PM
Macros are a great way to speed up these repetitive processes. I use macros a lot to format workbooks also. Great tip and thank you for the code!
Gary posted on 11/14/2012 3:36 PM
Laura, get this book:
Excel 2010 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach
Bruce posted on 11/14/2012 3:39 PM
What a tremendous way to save time; how we can only get it to send them my email...
Bamby posted on 11/14/2012 3:48 PM
This tip is explained very clearly and is a good example of the power of VBA .
I do have one question. In my version of Excel, I have the option of creating a pdf using an "Acrobat" option on the top ribbon, which allows for printing one or more pages in a workbook. This option can be turned on by clicking on the office button at the top left, going to Customize, and choosing the "Acrobat" tab.
It may be worth mentioning that this option is available (perhaps not in older version of Excel?)
Or, perhaps I'm missing something here and there is an advantage of using VBA over the built-in Acrobat. Sometime I over-simplify things and miss the point.
Dale posted on 11/14/2012 4:46 PM
Excellent tip and a great use of VBA. The usage of macros and VBA may be intimidating to some, but an adventurous spirit can yield many time-saving benefits.
Terry posted on 11/14/2012 7:28 PM
Very nice, I can't wait to try this. Great way to aggregate multiple pivot table charts.
Cal posted on 11/15/2012 8:35 AM
I think this is an excellent (and well written) tip. I had tried to do something like this a year or so ago, but could not get it to work. I am already using it!
Angela posted on 11/15/2012 9:06 AM
Laura, another good book for getting started with VBA is Michael Alexander's "101 Ready-To-Use Excel Macros." It provides examples of code for everything from adding worksheets to highlighting duplicate data. I believe there is also a companion website which allows you to directly copy the code examples.
Nina posted on 11/15/2012 12:03 PM
Neat tip and what a time saver!! Will definitely be trying this one. Thanks. Also thanks to Angela for the the book recommendation.
Bob posted on 11/15/2012 6:05 PM
If you own a copy of Acrobat Pro (most colleges have site licenses), you can use it to create PDFs from Excel worksheets. Just choose Acrobat as your printer, and "print" the worksheet to a pdf file. AIR member Bamby also mentioned this capability.

Also, if you are using a Mac, creating a pdf from Excel is built into Mac OS X. In the Print Dialog box, just choose "Save as PDF" from the PDF popup menu.
Leslie posted on 11/15/2012 6:39 PM
This is a wonderful example of how to maximize Excel. Very useful and handy tip. Well done!
Leslie posted on 11/15/2012 6:39 PM
This is a wonderful example of how to maximize Excel. Very useful and handy tip. Well done!
Michelle posted on 11/16/2012 8:17 AM
Thank you, Christina. I tried stepping through these and ended up with a syntax error. However, you pointed me to a very good source to learn more about VB. It has been on my "would be nice if" list for years but I was always stuck at how to get started. With a concrete example of something I would want to do and a good link I think I'll now be able to get past the first hurdle of making sense of it all.
Jim posted on 11/16/2012 8:21 AM
For our Factbook and Dashboards, I have a chain of spreadsheet tabs that I need to print at once, rather than as one tab at a time. To do this, control-click select the tabs in the order to be printed (they will be highlighted in a light color), then either print normally, or select a printer to print to pdf or Acrobat. IMPORTANT: Be very careful to un-select all of the printed tabs after printing--if you don't, anything you type in one tab will also show in the corresponding cells in the other selected tabs. (That can sometimes be a very useful feature, but it can be annoying if you forget!)
Christina posted on 11/16/2012 8:43 AM
Just to clarify, Excel does have the capability of saving selected worksheets as PDFs through Acrobat as some of you have mentioned. However, I was never able to find a way (before this macro, that is) to save each worksheet in an individual pdf file without manually doing this one-by-one.

And thanks for all your feedback! I use macros quite a bit and I have found some really great ones that have saved me a lot of time. I look forward to sharing more in the future!
zarfishan posted on 7/2/2013 3:38 AM
You can also use online converter like http://www.aspose.com/java/excel-component.aspx to convert your excel document to pdf securely and instantly. It is a very helpful tool for developers because it uses different languages like Java and .NET to convert documents from one form to another.