Simplify your reporting by using VBA to export your Excel dashboards to PowerPoint with ease
If you are using Microsoft Excel to create reporting or analytical dashboards, I bet you are spending some time on transferring your Excel dashboards to PowerPoint presentations.
In a recent series of articles (part 1, part 2, part 3), we discussed 3 dashboards designed to visualize the actual status of a software development project. Only showcases, for sure. A real life example would definitely have more dashboards to cover all necessary information needs, probably all in one consolidated workbook.
As far as my experience goes you are not using Microsoft Excel for project reporting. Instead, I guess your quantitative Excel dashboards will be only a part of your overall project reports. Most of you are using Microsoft PowerPoint for your shareholder and management reports, steering board presentations, project team meetings, etc., don’t you?
So what do you do? Copy each and every dashboard from Excel and paste it into PowerPoint slides manually? A lot of laborious work, isn’t it? But there is a better way: Today’s post shows how to automate the export of your Excel dashboards to PowerPoint. On the fly. As always, including an example workbook for free download.
The Idea
The idea and the use case is as simple as obvious:
You are using a Microsoft Excel workbook to consolidate data from one or different sources and to create several dashboards for the visualization of the actual status based on this data. If you have to provide these dashboards as a report not in Excel, but as a part of a PowerPoint presentation, you can save a lot of time by using VBA to convert the Excel visualizations to PowerPoint slides with a couple of mouse clicks:
The step-by-step-tutorial
- Format all dashboards in your Excel workbook in a way that they are all of the same size (approximately) and will fit on one PowerPoint slide.
- Define range names for the cell ranges of all dashboards (in our example: “myDashboard01”, “myDashboard02”, etc.)
- Define range names for other dynamic information you would like to display in the title of your PowerPoint slides. In our example below I used “myReportDate” for the cell containing the actual Friday, “myReportWeek” for the cell with the actual week number and “myInputStartTitles” for the cell above the list with the titles of the 3 dashboards.
- Add a cell containing a scale factor. The VBA will reduce the size of the pictures in PowerPoint to this factor. Assign a range name to this cell (“myScaleFactor”).
- Add the VBA code (see next section) to a new module of your workbook and adopt the range names according to your definitions in steps 3 and 4.
- Add a button to one or all dashboards and assign the macro “ExporttoPPT” to it.
- Save and close the workbook, open it again and enable macros.
- Perform the macro by clicking on the button and find the optimal scale factor to be used for your PowerPoint template by trial and error.
That’s it.
Agreed, the set-up takes some time and effort. But if you are reporting your Excel dashboards in PowerPoint presentations on a regular basis and over a longer period, it definitely pays off.
The VBA Code
Here are the 60 lines of code you need to copy to a new VBA module of your workbook:
Option Explicit
Dim PP As Object
Dim PP_File As Object
Dim PP_Slide As Object
Private Sub CopyandPastetoPPT(myRangeName As String, _
myTitle As String, _
myScaleHeight As Single, _
myScaleWidth As Single)
Dim NextShape As Integer
Dim ReportDate As String
ReportDate = Range(“myReportDate”).Value & ” / Week ” & _
Range(“myReportWeek”).Value & ” – ”
Application.GoTo Reference:=myRangeName
Selection.CopyPicture Appearance:=xlScreen, _
Format:=xlPicture Range(“A1”).Select
PP.ActivePresentation.Slides.Add PP.ActivePresentation.Slides.Count + 1, 11
Set PP_Slide = _
PP_File.Slides(PP.ActivePresentation.Slides.Count)
PP_Slide.Shapes.Title.TextFrame.TextRange.Text = _
ReportDate & myTitle
NextShape = PP_Slide.Shapes.Count + 1
PP_Slide.Shapes.PasteSpecial 2
PP_Slide.Shapes(NextShape).ScaleHeight myScaleHeight, 1
PP_Slide.Shapes(NextShape).ScaleWidth myScaleWidth, 1
PP_Slide.Shapes(NextShape).Left = _
PP_File.PageSetup.SlideWidth \ 2 – _
PP_Slide.Shapes(NextShape).Width \ 2
PP_Slide.Shapes(NextShape).Top = 90
End Sub
Sub ExportToPPT()
Dim ActFileName As Variant
Dim ScaleFactor As Single
On Error GoTo ErrorHandling
ActFileName = Application.GetOpenFilename _
(“Microsoft PowerPoint-Files (*.ppt), *.ppt”)
ScaleFactor = Range(“myScaleFactor”).Value
Set PP = CreateObject(“Powerpoint.Application”)
If ActFileName = False Then
PP.Activate
PP.Presentations.Add
Set PP_File = PP.ActivePresentation
Else
PP.Activate
Set PP_File = PP.Presentations.Open(ActFileName)
End If
PP.Visible = True
CopyandPastetoPPT “myDashboard01”, _
Range(“myInputStartTitles”).Offset(1, 0).Value, _
ScaleFactor, ScaleFactor
CopyandPastetoPPT “myDashboard02”, _
Range(“myInputStartTitles”).Offset(2, 0).Value, _
ScaleFactor, ScaleFactor
CopyandPastetoPPT “myDashboard03”, _
Range(“myInputStartTitles”).Offset(3, 0).Value, _
ScaleFactor, ScaleFactor
Set PP_Slide = Nothing
Set PP_File = Nothing
Set PP = Nothing
Worksheets(1).Activate
Exit Sub
ErrorHandling:
Set PP_Slide = Nothing
Set PP_File = Nothing
Set PP = Nothing
MsgBox “Error No.: ” & Err.Number & vbNewLine & _
vbNewLine & “Description: ” & Err.Description, _
vbCritical, “Error”
End Sub
Calling the Sub “ExportToPPT” by clicking on the button displays a file open dialogue where the user can navigate to an existing PowerPoint presentation. After clicking “open”, the three dashboards will be added at the end of this presentation. If the user leaves the dialogue using the ESC key, a new presentation based on the standard PowerPoint template will be created. The sub uses the standard procedure “GetOpenFilename” to select the existing or create a new presentation and then calls the Sub “CopyandPastetpPPT” for all 3 dashboards. This Sub simply copies the defined dashboard in Excel as a picture and pastes it into a new PowerPoint slide.
The Download Link
Download the consolidated software development project workbook (all 3 episodes in one file), including the option to export all dashboards to PowerPoint with one single mouse click:
Download Export Excel Dashboards to PowerPoint (Microsoft Excel 2003, 502.5K)
All data in this workbook is made up.
You need to have Microsoft PowerPoint installed and to enable macros when opening the workbook.The tool is tested using Microsoft Excel and Microsoft PowerPoint in versions 2003 and 2007.
That’s it. Just another way of how to use VBA to export data and visualizations from one Microsoft application to another in order to save some time in your daily work. If you want to see other examples like this I recommend to also visit Gantt Charts are learning to fly or Export Microsoft Project Tasks to Outlook.
What’s next?
One of the next posts will start a new category here on Clearly and Simply: Optimization algorithms, implemented and visualized using Microsoft Excel and VBA.
Stay tuned.
Update on Monday, 6th of July 2015
You may also like the two follow-up articles about the export of Excel dashboards to other applications and file formats:
Leave a Reply