Exporting Excel worksheets (Print Areas) and charts to a single PowerPoint© presentation is not difficult. Yet the VBA code to perform this feat is cumbersome and difficult to decipher.
This post explains how to implement the Export to PP routine which is used in the Total Cost Model. The code will take any number of worksheets from an Excel workbook and export them to a single PowerPoint slideshow. What actually takes place in this routine is content from the Excel workbook is copied and then pasted into a newly created PowerPoint slideshow. This code also needs to differentiate between copying and pasting simple Print Areas and Charts which adds some complexity you may not need. As in the post on Exporting to a PDF document, the selected worksheets are obtained from a VBA form (shown in the following image.)
This export routine does not work with PowerPoint versions prior to 2013 (version 15) due to library incompatibilties. Because of this there is a warning displayed from the toolbar click event which opens the frmToPP. If the PowerPoint version number is less than 15 there is a message shown and no form is opened, otherwise the frmToPP is opened. The version checking code is shown in the following:
Sub mnuExportToPP(control As IRibbonControl) ' Callback for btnExportToPP onAction ' show the Export to PowerPoint form ' the export does not work with Excel versions earlier than 2013, so we check the version number here... On Error Resume Next If Val(Application.Version) < 15 Then ' If version of Excel is before 2013 MsgBox "The export to Powerpoint feature does not work with Excel versions earlier than 2013.", vbInformation, "Feature not enabled" Else ' must be Excel 2013 to 2016 frmToPP.Show End If End Sub
The routine starts after selecting the worksheets to export and clicking the Export button.The Export button click event procedure follows this pseudocode :
- The selected item found flag, bolFound, is set to False and a ActiveSheet index is saved in the variable shtIndex. The active worksheet index will allow us to remain on the current worksheet after the export routine is finished. A form Control object is set to the variable name ctrl.
- A For Each loop moves through all of the controls on the form, frmToPP by reading the ctrl object member’s TypeName property.
- An embedded If statement checks for the control TypeName,”CheckBox”.
- If it finds a checked box, excluding the chkSelectAll checkbox, it sets the flag, bolFound, to True.
- If bolFound is False a message that there was NO selection is displayed,
- Otherwise (else) a message is displayed telling the user to be patient and giving them the chance to halt the export. If the user selects OK it calls the ExportToPP sub procedure..
Remember… all these samples scroll to the right to reveal the complete code→.
Private Sub cmdExport_Click() ' Find the selected documents from the form's checkboxes and send to the export routine Dim bolFound As Boolean Dim ctrl As control Dim shtIndex As Integer On Error Resume Next shtIndex = ActiveSheet.Index bolFound = False ' was a checked box found? For Each ctrl In frmToPP.Controls If TypeName(ctrl) = "CheckBox" Then If ctrl.Name <> "chkSelectAll" Then If ctrl.Value = True Then bolFound = True End If End If Next If bolFound = False Then ' if there is NOT anything selected Call MsgBox("There is nothing selected to export!", vbOKOnly, "Nothing selected...") Else If MsgBox("If you have selected most or all of the worksheets, please be patient this " _ & "export process may take several minutes!", vbOKCancel, "Patience is required...") = vbOK Then Call ExportToPP End If End If ' go back to the originating worksheet Worksheets(shtIndex).Activate End Sub
Since the ExportToPP is difficult to read, and what some might say a little long to scroll through, it will be broken up into snippets. The entire Sub procedure can be seen in the Total Cost Model’s VBA project code. There will be several links to other websites throughout this post which will further explain some of the more difficult commands, use them.
The first code snippet shows the creation of the PowerPoint and Excel objects along with the usual housekeeping. Early binding is used because the code is easier to write (think Intellisense), debug and faster.
Public Sub ExportToPP() ' export selected sheets (Print_Area or Graphs) to a Powerpoint presentation Dim PowerPointApp As PowerPoint.Application Dim i As Integer Dim activeSlide As PowerPoint.Slide Dim cht As Excel.ChartObject On Error GoTo errHandler With Application .ScreenUpdating = False .EnableEvents = False End With ' If PowerPoint already opened use it On Error Resume Next Set PowerPointApp = GetObject(, "PowerPoint.Application") On Error GoTo 0 ' If not create a new PowerPoint instance If PowerPointApp Is Nothing Then Set PowerPointApp = New PowerPoint.Application ' Create a presentation in PowerPoint If PowerPointApp.Presentations.Count = 0 Then PowerPointApp.Presentations.Add ' Show PowerPoint PowerPointApp.Visible = True
The ExportToPP sub routine starts off as explained in this pseudocode:
- Initiate a variable for the loop counter and 3 objects, a PowerPoint.Application, a PowerPoint.Slide and an Excel.ChartObject..
- Change the Application properties for EnableEvents and ScreenUpdating to False.
- Check to see If PowerPoint is already opened, if it is we use it,
- If it isn’t open (PowerPointApp Is Nothing) we create a new PowerPoint instance.
- Next a new PowerPoint presentation is created (PowerPointApp.Presentations.Add.)
- Finally Powerpoint is opened and made the active window.
Next comes the really difficult to read code, the actual copying and pasting to a new slideshow. Taken a piece at a time it is simple:
' Find the selected sheets per the form's selection(s) and add them to the presentation For i = 1 To 6 If Me.Controls("ChkItems" & i).Value = True Then With Worksheets(i) ' Add a new slide where we will paste the content PowerPointApp.ActivePresentation.Slides.Add PowerPointApp.ActivePresentation.Slides.Count + 1, ppLayoutTitleOnly PowerPointApp.ActiveWindow.View.GotoSlide PowerPointApp.ActivePresentation.Slides.Count Set activeSlide = PowerPointApp.ActivePresentation.Slides(PowerPointApp.ActivePresentation.Slides.Count) ' Copy the Excel "Print_Area" range and paste it into the PowerPoint as a Picture If i <> 6 Then ' it is NOT the Charts .Range("Print_Area").Copy activeSlide.Shapes.PasteSpecial(DataType:=ppPasteEnhancedMetafile).Select 'Set the title of the slide the same as the title of the chart activeSlide.Shapes(1).TextFrame.TextRange.Text = .Name 'Adjust the positioning of the Chart on Powerpoint Slide PowerPointApp.ActiveWindow.Selection.ShapeRange.Left = 15 PowerPointApp.ActiveWindow.Selection.ShapeRange.Top = 125 activeSlide.Shapes(2).Width = 200 activeSlide.Shapes(2).Left = 505 Else ' it IS the charts ' Loop through each chart in the Excel worksheet and paste them into the PowerPoint Worksheets(i).Activate For Each cht In ActiveSheet.ChartObjects ' Add a new slide where we will paste the chart PowerPointApp.ActivePresentation.Slides.Add PowerPointApp.ActivePresentation.Slides.Count + 1, ppLayoutTitleOnly PowerPointApp.ActiveWindow.View.GotoSlide PowerPointApp.ActivePresentation.Slides.Count Set activeSlide = PowerPointApp.ActivePresentation.Slides(PowerPointApp.ActivePresentation.Slides.Count) ' Copy the chart and paste it into the PowerPoint as a Metafile Picture cht.CopyPicture Appearance:=xlScreen, Format:=xlPicture activeSlide.Shapes.PasteSpecial(DataType:=ppPasteEnhancedMetafile).Select ' Set the title of the slide the same as the title of the chart activeSlide.Shapes(1).TextFrame.TextRange.Text = cht.Chart.ChartTitle.Text ' Adjust the positioning of the Chart on Powerpoint Slide PowerPointApp.ActiveWindow.Selection.ShapeRange.Left = 15 PowerPointApp.ActiveWindow.Selection.ShapeRange.Top = 125 activeSlide.Shapes(2).Width = 200 activeSlide.Shapes(2).Left = 505 Next cht End If End With End If Next
Errors in selecting the wrong parameter for a method or property can cause the code to freeze PowerPoint and Excel requiring a forced “End Task”.
This code doesn’t actually check the worksheets for the existence of charts. Sheet(6) is the “Chart” sheet in the Cost Model worksheet. You could, however put search for content code there is plenty of code floating around the internet on how to do it.
Finally we end things and cleanup…
MsgBox "Export operation Complete!", vbInformation GoTo cleanup errHandler: If Err.Number = 429 Then MsgBox "A program required to open your document could not be found, terminating the operation." GoTo cleanup Else On Error GoTo 0 Resume Next End If cleanup: With Application .ScreenUpdating = True .EnableEvents = True .CutCopyMode = False End With ' Release memory from Object variables Set PowerPointApp = Nothing Set activeSlide = Nothing Set cht = Nothing End Sub
- Display “Export operation Complete” message then go to the housekeeping code.
- The error handling routine, errHandler, will trigger if there is a legitimate error (>0). If the error number is 429 (This error occurs when the Component Object Model COM cannot create the requested Automation object) the operation terminates. Any other error number will display a message and resume executing the code at the next statement after the error.
- Return the Application properties for EnableEvents and ScreenUpdating to True and set the CutCopyMode to False
- Release memory from the 3 Object variables by setting them to Nothing.
The following image shows the result of the export. The charts and other images could be enlarged by the code and other items (like text areas) added during the procedure. However, this is left to the user to change these things as they see fit. Maybe this slideshow will be shown at a meeting or maybe simply viewed from a PC. The newly created presentation is as customizable as any.
This procedure can be seen in action and the code examined in the workbook, Total Cost Model. You can use this Excel workbook and view all of the VBA code within it free of charge.