Export Excel Dashboards (Improved Version)

Written by

An improved version of the example workbook exporting Excel Dashboards to other applications

The previous post provided an example Excel model with the option to export dashboards to other applications and file formats.

The workbook came with one serious limitation: the PDF and XPS export routine created a separate file for each dashboard, instead of one file with all dashboards.

In the article I stated, I wouldn’t see how to get around this without using an external PDF creator. It turns out that I simply couldn’t see the wood for the trees.

It was (again!) Leonid Koyfman, long-time reader and contributor, who pointed me into the right direction. The idea is as simple as it is efficient: create a temporary Excel workbook, copy all dashboards to it (each dashboard on a separate sheet), use the ExportAsFixedFormat method of the workbook object to export the entire workbook (i.e. all dashboards) to one PDF or XPS file and finally close the temporary workbook without saving changes. So simple, so obvious. I should have figured that out on my own. But I didn’t. Many thanks to Leonid for the idea.

Here is the improved version for free download:

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

Stay tuned.

export excel cell ranges export excel dashboards export excel dashboards to other applications export excel dashboards to pdf export excel dashboards to powerpoint export excel dashboards to word export excel ranges to pictures export excel to pdf export excel to powerpoint export excel to word export excel to xps

Comments

10 responses to “Export Excel Dashboards (Improved Version)”

July 7, 2015

Konrad

Awesome, thanks for the update. Very useful, just like all the other tools on your site.

Reply
October 29, 2015

Guilherme

Nice! but my PPT crashes when i try to open ppt file

Reply
October 29, 2015

Robert

Guilherme,
I am sorry to hear that. The version I posted for download works like a charm for me in Office 2010, 2013 and 2016 (at least for Windows, I didn’t test it on a Mac), so it shouldn’t be a version issue. The code creates a standard PowerPoint file, so it should be possible to open it with PowerPoint without problems. I am sorry, but I have no clue why it doesn’t work for you.

Reply
November 3, 2015

Jacob Petersen

Hi Robort,
I must say, I am very impressed! This is simply awesome and beautiful.
I do have two questions though. Would it be possible to ‘use destination theme & embed workbook’ instead of pasting a picture in Powerpoint?
Also, would it be possible to insert the charts to a template in Powerpoint instead of a blank one?
Thanks once again.

Reply
November 3, 2015

Jacob Petersen

Robert* sorry

Reply
November 3, 2015

Robert

Jacob,
regarding your first question: the statement .Shapes.PasteSpecial 2 in the sub CopyPasteOneSlide exports the range as an Enhanced Metafile. If you change the parameter in this line from 2 to 10, it should export it as an embedded workbook. At least according to this Microsoft reference:
https://msdn.microsoft.com/de-de/library/office/ff744731.aspx
Unfortunately, this doesn’t work. At least not in Office 2013 and 2016. I am sorry, but I have no clue why. Apparently Microsoft changed something here…
Regarding your second question: I assume you want to export the dashboards to an existing PowerPoint presentation, right? Yes, this is possible. Have a look at this article from 2010:

Export Microsoft Excel Dashboards to PowerPoint

The code in that workbook starts with a file open dialogue where you can select an existing PowerPoint presentation and adds the dashboards on slides at the end of it.
I hope this helps a little bit.

Reply
November 4, 2015

Jacob Petersen

Hi Robert,
Thank you for your quick reply. I tried in Office 2010 and unfortunately that does not work either, but thank you for providing the feedback on that.
And also, thank you for the second answer, that is working very well.
Keep up the good work, you have a new follower with your genius dashboards and macros.

Reply
January 8, 2016

Cal

This looks quite interesting, especially if it can be used seamlessly with Microsoft OneNote. Have been looking for ways to visually represent my property for plantings, project plans, etc. with a way to overlay multiple images. With OneNote you can put in background images to notes. When that background image represents a dashboard created from Excel data, so much more interesting… Thanks for the great info!

Reply
June 22, 2016

Alexndra

How can I add this menu tool bar in my own dashboards.Or can i directly use the macro
I am working on dashboard and only want to export in pdf and word and ppt ;
Please help
Alexandra Sameer

Reply
June 22, 2016

Robert

Alexandra,
the additional tab and icons on the ribbon are not created by the VBA code.
You can change the ribbon in Excel with the freely available Custom UI Editor. Have a look here for more details:
http://www.rondebruin.nl/win/s2/win001.htm

Reply