Best Way to Automate Between Excel and PowerPoint?

I am trying to think of the best way to automate between Excel and PowerPoint. I have an Excel sheet with multiple tabs and over 40 graphs that I copy/paste into a PPT.

I would link the graphs, however, in the final version of the PowerPoint i cannot have the recipient see the underlying data. Also, when pasting the graph it does not look as good as the picture view since then I can resize it. This process will happen for 5 PowerPoint decks and be quarterly.

Any suggestions?
kwarden13Asked:
Who is Participating?
 
Jamie GarrochConnect With a Mentor PowerPoint Consultant & DeveloperCommented:
I misunderstood the question which was "Best Way to Automate Between Excel and PowerPoint?". I hadn't realised you were expected  code-based answer and thought it was more a "what approach?" type of question. This code example will get you going in the right direction:

' ==========================================
' Module : ExcelToPowerPoint
' VBE : PowerPoint
' ==========================================

Option Explicit

' Set to true to use IntelliSense
' True requires adding a Reference to Microsoft Exel XX.X Object Library
#Const DevMode = False

' ===============================================================================
' PowerPoint Macro
' Purpose: Copies the first chart from the active sheet of the open Excel file
'          and then Pastes the chart as a PNG image on the slide in view in the
'          active presentation.
' Author: Jamie Garroch of YOUpresent.co.uk
' Example calls:
'                 Raster : CopyRangeFromExcel ppPastePNG
'                 Vector : CopyRangeFromExcel ppPasteEnhancedMetafile
'                 Excel  : CopyRangeFromExcel ppPasteOLEObject
' ===============================================================================
Sub CopyRangeFromExcel(PasteType As PpPasteDataType)
#If DevMode Then
  ' Early bound Excel objects
  Dim oXL As Excel.Application
  Dim oWB As Workbook
  Dim oWS As Worksheet
#Else
  ' Late bound Excel objects
  Dim oXL As Object
  Dim oWB As Object
  Dim oWS As Object
#End If
  
  ' PowerPoint objects
  Dim oPres As Presentation
  Dim oSld As Slide
  Dim oShp As Shape
  
  On Error Resume Next
  
  ' Get a reference to the Excel instance
  Set oXL = GetObject(, "Excel.Application")
  If oXL Is Nothing Then
    MsgBox "Couldn't find Excel.", vbCritical + vbOKOnly, "Excel not open"
    Exit Sub
  End If
  
  ' Get a reference to the active sheet in Excel
  Set oWS = oXL.ActiveSheet
  If oWS Is Nothing Then
    MsgBox "Couldn't find Excel sheet.", vbCritical + vbOKOnly, "Excel sheet not found"
    Exit Sub
  End If
  
  ' Copy a chart from the active sheet
  oWS.ChartObjects(1).Copy
  
  ' Get a reference to the active presentation
  Set oPres = ActivePresentation
  If oPres Is Nothing Then
    MsgBox "Couldn't find presentation.", vbCritical + vbOKOnly, "Presentation not found"
    Exit Sub
  End If
  
  ' Get a reference to the slide in view
  Set oSld = ActiveWindow.View.Slide
  If oSld Is Nothing Then
    ' Slide not in view, use slide 1 instead
    Set oSld = oPres.Slides(1)
    If oSld Is Nothing Then
      MsgBox "Couldn't find slide.", vbCritical + vbOKOnly, "Slide not found"
      Exit Sub
    End If
  End If
  
  ' Paste the range that was copied from Excel to the PowerPoint slide in the required format
  oSld.Shapes.PasteSpecial PasteType
  
  ' Clean up
  Set oXL = Nothing: Set oWB = Nothing: Set oWS = Nothing
  Set oPres = Nothing: Set oSld = Nothing: Set oShp = Nothing
End Sub

Open in new window

0
 
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
Firstly, when the target content is in PowerPoint, I would always start writing the code in that VBE. Why? Because you can use IntelliSense to access the PowerPoint OM without having to mess around with library references and/or early/late binding considerations.

Secondly, if you're pasting pictures, you need to be aware of the pros and cons of using raster versus vector formats. If you want to scale beyond 100% and don't want to loose resolution then you need to use a vector format such as EMF or maybe even the new SVG format. That's another reason for coding in the PowerPoint VBE because PasteSpecial behaves differently and you have more "presentation-centric" formats in the PowerPoint library as follows:

PowerPoint 2016 (Feb 2018) PasteSpecial Method
Note too that if you go for a raster format, you'll be better off with PNG, usually, because it's a lossless format and PowerPoint has lots of "compression" peculiarities under the hood, more so when dealing with JPG images.

Table correct as of Office 365, PowerPoint 2016, Version 1803, Build 9031.2004
1
 
kwarden13Author Commented:
I am a little lost in your solution as I am not sure where/how to code it. Do you have an example or is there an article you could point me to in the right direction.

Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.