Export Excel Dashboards to other Applications

How to export Microsoft Excel Dashboards to PowerPoint, Word, PDF, XPS or Pictures

Earlier this week I received an email from Andrea, one of my few, but faithful readers. Andrea stumbled across a post from 2010 describing how to export Excel dashboards to PowerPoint and asked if I was planning a follow-up post about exporting from Excel to Microsoft Word.

Truth be told, not up to that point. I personally barely export from Excel at all, but if I do, I usually export to PowerPoint.

However, I found Andrea’s idea interesting for several reasons: exporting Excel dashboards to other applications and file formats may indeed be helpful, e.g. for creating reports or papers in Microsoft Word. Furthermore the original implementation exported three different dashboards, but the best use case for a bulk export to another application is an Excel dashboard displaying one selected period out of many or a dashboard with various filter options. In this case it can be a real time-saver if you have the option to export e.g. all weekly reports of a quarter or a defined set of filter combinations in one go. Last, but not least, it is actually no big deal to adjust the original code for other applications and file formats.

Today’s article provides an example workbook which allows to export the dashboards to PowerPoint, Word, PDF, XPS and some picture file formats (PNG, JPEG, GIF, BMP). As always, the post includes a link to the Excel workbook for free download.

The Idea and the Example

The example workbook I will be using in this post to demonstrate different export features is based on a dashboard published in the very first article here on Clearly and Simply back in January 2009: Take Care of Customer Care.

The workbook provided in that article includes a Performance Dashboard for a Customer Care Center:

It visualizes various key performance indicators for a Call Center on a weekly basis. The only interactive feature is the option to select the displayed week using the spinner at top right. This is a very common way of organizing dashboards for regular reports.

Regarding today’s topic, it can be very helpful to have an option to export not only one week, but a user-defined selection of weeks (e.g. one entire quarter or year) and have each week on e.g. one slide in PowerPoint or one page in Word. Of course you can do this manually by copy and paste, but wouldn’t it be nice to have a feature doing this for you with one single click?

Actually, this is no big deal. Some VBA code (one module, 12 subs, 267 lines of code), a few named ranges and an additional control worksheet to define the settings. That’s it.

Best thing about it? You do not even have to write the code, just download the workbook (link see below).

The Features of the Implementation

The workbook provides various options to export a user defined selection of weeks. It is not restricted to PowerPoint anymore. You can also export to Word, PDF, XPS and some picture file formats (PNG, JPEG, GIF, BMP) by clicking on one of the buttons of the extra tab on the Ribbon called EXPORT TO OTHER APPS:

Before you do so, you probably want to change a few settings on the worksheet [control]:

The first cell (C4) contains the current week and is the target cell of the spinner on the dashboard. All other input cells on the sheet [control] can be changed manually as required.The comments right to the cells describe their purpose, so I think there is no need to repeat this here.

Having said that, I would like to point you to the last 2 input cells. In cells C16 and C17 you defined which and how many weeks shall be exported. In the example shown above, the procedures will export 7 weeks (i.e. 7 dashboards) starting with week 10. You may want to bring these input cells to the dashboard to make the access easier for the user. I put it on the [control] sheet intentionally, because I like to have all all input parameters in one place, but moving them to the dashboard should be an easy thing to do.

The export procedures will create one document (PowerPoint and Word) or a folder with several one-page documents (PDF, XPS and pictures) within the current folder, i.e. where the Excel workbook is located. The files and folders are date and time stamped. After running all export subs, the folder could look like this:

The Limitation

If you want to create PDF or XPS documents by VBA, you can either call an external PDF application (e.g. Acrobat Pro) or use the worksheet method ExportAsFixedFormat.

Since many people do not have a PDF creator installed, I decided to go with the VBA method for exporting to PDF and XPS. This shall work with most Excel installations, but it comes with a considerable downside: the method exports the entire worksheet, but in our case this is only one page. If you want to export several weeks, there is no way of combining them into one PDF or XPS without an external PDF creator. Thus, the VBA code will create a separate PDF or XPS file for every dashboard.

Unfortunate, I know. But I do not see a way of getting around this limitation without an external application. Maybe this is just above my head. If you know of a solution or workaround, I would appreciate if you could share it in a comment.

The Download Link

Here is the Excel workbook shown above including the open VBA code for free download:

Download Export Excel Dashboard to other Applications (zipped Microsoft Excel 2010 – 2013 workbook, 205K)

How to use the Template for your own Dashboard

Option 1: Use the example workbook as a template

First option is to take the example workbook as the basis and copy in your own dashboard, data and calculations. Here are the main steps you have to do:

1. Open the example workbook, and delete all worksheets except for the worksheet [control]
2. Copy the dashboard, data and all other sheets of your model into the example workbook
3. Define the named range “myDashboard” to refer to the desired cell range on your dashboard
4. Make sure the input control (e.g. a spinner or a drop down) for selecting a period on your dashboard is linked to the cell “myCurrentPeriod” on worksheet [control]
5. Replace the (now broken) formulas in cells C12 and C13 on sheet [control] (title and filename) by whatever you find appropriate

Option 2: Copy the relevant parts from the template to your model

Option 2 is the taking opposite way: copy the relevant parts of the example workbook to your model. Here are the steps:

1. Copy the worksheet [control] from the example workbook to your model
2. Copy the VBA module modExport from the example workbook to your model and save your workbook as a macro enabled Excel workbook (.xlsm)
3. Define the named range “myDashboard” to refer to the desired cell range on your dashboard
4. Make sure the input control for selecting a period on your dashboard is linked to the cell “myCurrentPeriod” on worksheet [control]
5. Finally you have to set up a user interface to access the export macros:
   1. If you want to work with the extra tab, you have to customize the Ribbon as e.g. Ron de Bruin explains here: Change the Ribbon in Excel 2007-2013
   2. If you do not want to use a customized ribbon, you can insert e.g. command buttons on your dashboard and assign the export macros to them. Before you can do this, you first have to delete the iRibbonControl parameter passed to the subs in the example workbook. For instance: the sub to export to PowerPoint would then be defined as Sub Export2PPT() instead of Sub Export2PPT(control As IRibbonControl). You have to repeat this for all export subs you want to use

Acknowledgement

Many thanks go to Andrea for the inspiration. Much appreciated.

Stay tuned.

Update on Monday, 6th of July 2015

Leonid Koyfman was kind enough to provide the idea of how to easily overcome the limitations described above. Have a look at this follow-up article:

Export Excel Dashboards (Improved Version)

It includes an improved version of the example workbook for free download. Many thanks go to Leonid.