如何获取VBA中所有系列图表的源数据？ [英] How to get the the source data of all the series of a chart in VBA?

问题描述

With ActiveWorkbook.Sheets(1)
INSPECT_CHARTS_NUMBER = .ChartObjects.Count
For c= 1 To .ChartObjects.Count
Set INSPECT_CHART = .ChartObjects(c).Duplicate

.ChartObjects(c).Chart.SetSourceData Source:=.Range("e4:h4")

Next lngC
End With

我在VBA中更改图表的源数据此行 .Chart.SetSourceData Source：=。Range（e4：h4）做工作，但如果该图表中有多个系列，则不会工作。

I do the above to change the source data of a chart in VBA this line .Chart.SetSourceData Source:=.Range("e4:h4")does do job, but this will not work if there are multiple series in that chart.

如何获取所有系列的源数据，然后如何分别更改它们？

How can I get the sourcedata of all the series and then how can I change them separately?

推荐答案

p>这是一个例子

假设活动图表的系列集合从 A1：A5 和 C1：C5 。因此，下面的代码将源代码更改为 A8：A12 和 C8：C12

Let's say the series collection of the active chart is picking the values from A1:A5 and C1:C5. So the below code will change the source to A8:A12 and C8:C12

Dim sc As SeriesCollection
Dim i as Long, j as Long

j = 1

For i = 1 To ActiveChart.SeriesCollection.Count
    ActiveChart.SeriesCollection(i).Values = "=Sheet1!R8C" & j & ":R12C" & j
    j = j + 2 '<~~ Adding 2 for Col C
Next

截图

之前

之后

>

这篇关于如何获取VBA中所有系列图表的源数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！