Search code examples
excelvbapowerpoint

How to update the range in powerpoint chart from excel

I run a VBA script from an excel workbook which also holds all data. The script automatically updates a chart in excel if I add new datapoints. However when I put the same chart in powerpoint it no longer updates when new data points are added.

I have tried using various linked versions of charts. One of them does the job but then it is only an image in powerpoint and I need the actual chart to be updated when I run the VBA script.

In the code below I try to access the powerpoint chart directly and just update it's range. I have tried various versions of this but can't get it to work. It opens the powerpoint and identifies the shape but can't change the range. 

Dim PPTApp As Object
Dim PPTPres As Object
Dim ppSlide As PowerPoint.Slide

Set PPTApp = CreateObject("PowerPoint.Application")
PPTApp.Visible = True

Set PPTPres = PPTApp.Presentations.Open("file:///C:\Users\user.name\Desktop\pptest111.pptx")
PPTPres.Windows(1).Activate

PPTPres.Slides(2).Shapes("Diagram1").Chart.ChartData.Sheets("sht2").Range ("A5:A15")
Solution

  • To change the range of a chart you have to change the range of the labels and data.

    XValues is the range for labels and values is the range for data

    if oSh is PPTPres.Slides(2).Shapes("Diagram1")

    Then some variant of this should work

      oSH.Chart.SeriesCollection(1).XValues = "sht2!$A$5:$A15"  ' change the series range for labels
  oSH.Chart.SeriesCollection(1).values = "sht2!$B$5:$B15"   ' change the series range for data