How to update the range in powerpoint chart from excel

Question

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")

Note that PowerPoint can update charts automatically without using VBA: Automatic Updating of Excel Tables in PowerPoint Slides — Pᴇʜ
– Pᴇʜ, Commented Feb 15, 2019 at 10:48
I know, but then it becomes an image. I need a standard chart in the powerpoint presentation that other users are familiar with and can interact with. In other words: click on it, change colors etc. — Python-0543
– Python-0543, Commented Feb 15, 2019 at 12:10

mooseman · Accepted Answer · 2019-02-20 20:26:35Z

1

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

answered Feb 20, 2019 at 20:26

mooseman

2,0052 gold badges17 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Python-0543 Over a year ago

Thanks! it works perfectly using your suggestion! For anyone struggling with this, just use the solution above. This will give you an empty chart in powerpoint. This chart however is adjusted with the new range so just add one line updating the chart with the code below and it works perfectly, PPTPres.Slides(2).Shapes("Diagram1").LinkFormat.Update

abdullah · Accepted Answer · 2022-01-04 16:08:28Z

0

mooseman answers works but you need to update chart AfterWords. For me the LinkFormat.Update was not picking up any shape so i added this to resolve it

With oSH.Chart.ChartData
    .Activate
    .Workbook.Close
End With

answered Jan 4, 2022 at 16:08

abdullah

1261 silver badge9 bronze badges

Collectives™ on Stack Overflow

How to update the range in powerpoint chart from excel

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related