0

I have a PowerPoint presentation containing several charts. Each of these charts have an embedded PowerPoint Excel file behind the chart. The embedded Excel file source its data from an Excel (Master) file. When I make any changes in the Excel (Master) file these changes are not automatically displayed in the chart. In order to get the change reflected in the chart I need to right click the chart and then choose "Edit data". I found below code which helps me to automatically open all the embedded Excel files in the presentation.

My question is now, if I do not want the macro to open all the embedded Excel files but perhaps only a specific chart (chart 1). If so, how should I then re-write the code so it only open the embedded Excel file for Chart 1?

Update PowerPoint chart using VBA

Sub update2()

Dim myPresentation As PowerPoint.Presentation
Dim sld As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim myChart As PowerPoint.Chart
Dim Wb As Object
Dim App As Object

Set myPresentation = ActivePresentation

{For Each sld In myPresentation.Slides
    For Each shp In sld.Shapes
        If shp.HasChart Then
            Set myChart = shp.Chart
            myChart.ChartData.Activate
            myChart.Refresh
            Set Wb = myChart.ChartData.Workbook
            Set App = Wb.Application
            Wb.Close (0)
        End If
    Next
Next
App.Quit
End Sub
jqx204s
  • 1
  • 1
  • Is there some reason you're embedding the charts rather than linking them? If they're linked, they'll automatically update from the Excel source file when you open the presentation (or via VBA using UpdateLinks) – Steve Rindsberg May 30 '22 at 16:38
  • @Steve, apologize for late reply. Reason for preferring the embedding solution is due to it increase the possibility to share the PowerPoint presentation with other people. If the data to the charts are embedded in the PowerPoint then anybody who gets the presentation can access/change the data behind the charts. If the chart is linked to an Excel file, then people will need to have access to that specific Excel file to access/change the data. – jqx204s Nov 21 '22 at 11:04
  • OK, that makes sense. So you'll need some means of identifying the chart you want to update and add that condition as another If/Then statement after If shp.HasChart For example, it might be the fact that the chart's on Slide 42, or that it has a specific .Tag on it. – Steve Rindsberg Nov 21 '22 at 15:34

0 Answers0