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.