Export multiple worksheets to a single PowerPoint slideshow with VBA

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.