Mapping Excel Data to PowerPoint Charts for Automatic Update, Possible?

PowerPoint / By /
  1. Loop through all slides in the active presentation
  2. Loop through all shapes on each slide
  3. Check if the shape is a chart object linked to an Excel file
  4. Update the chart’s source workbook path and file name properties
Sub UpdateChartLinks()

Dim sld As Slide
Dim shp As Shape
Dim chr As Chart
Dim oldPath As String
Dim newPath As String

oldPath = "C:\Users\myname\Documents\1-MyDocuments\1-Annetta\OT\SURVEYS\2019 Survey #2 SYMPTOMS & DIAGNOSES\"
newPath = "T:\NEW FILTERED CHARTS AND DATA LINKED SURVEY #2 ANALYSIS.XLSM"

For Each sld In ActivePresentation.Slides
    For Each shp In sld.Shapes
        If shp.HasChart Then
            Set chr = shp.Chart
            chr.ChartData.Workbook.Path = newPath
            chr.ChartData.Workbook.Name = Split(newPath, "\")(UBound(Split(newPath, "\")))
        End If
    Next shp
Next sld

End Sub

The key things this code does:

  • Loops through all slides using For Each sld In ActivePresentation.Slides
  • Loops through all shapes on each slide with For Each shp In sld.Shapes
  • Checks if shape has a chart using shp.HasChart
  • Gets chart object from shape using Set chr = shp.Chart
  • Updates the Path property to the new path
  • Updates the Name property to just the file name part of the path

Let me know if you have any other questions!

Related Posts