时间戳

首页 > 解决方案 > How to update the range in powerpoint chart from excel

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

标签： excelvbapowerpoint

解决方案

要更改图表的范围，您必须更改标签和数据的范围。

XValues 是标签的范围，值是数据的范围

如果 oSh 是 PPTPres.Slides(2).Shapes("Diagram1")

那么这个的一些变体应该可以工作

  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

推荐阅读