vba 如何在VBA中获取图表所有系列的源数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16100955/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the the source data of all the series of a chart in VBA?
提问by Eduard Florinescu
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
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.
我执行上述操作以更改 VBA 中图表的源数据，此行.Chart.SetSourceData Source:=.Range("e4:h4")确实可以工作，但是如果该图表中有多个系列，则这将不起作用。
How can I get the sourcedata of all the series and then how can I change them separately?
如何获取所有系列的源数据，然后如何分别更改它们？
回答by Siddharth Rout
Here is an example
这是一个例子
Let's say the series collection of the active chart is picking the values from A1:A5and C1:C5. So the below code will change the source to A8:A12and C8:C12
假设活动图表的系列集合正在从A1:A5和 中挑选值C1:C5。所以下面的代码会将源更改为A8:A12和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
Screenshot
截屏
Before
前
After
后
回答by glh
回答by LeanStruments
You can access the data source of the chart series by retrieving the series .Formulastring.
您可以通过检索系列.Formula字符串来访问图表系列的数据源。
After that you can change the Formula (with Replace for example) and then reapply the Formula for the series.
之后，您可以更改公式（例如使用替换），然后重新应用该系列的公式。
Code example (provided for the solution from Sid):
代码示例（为 Sid 的解决方案提供）：
Dim chart As ChartObject
For Each chart In ActiveSheet.ChartObjects
Dim ser As Series
For Each ser In chart.Chart.SeriesCollection
Dim oF As String
Dim nF As String
oF = ser.Formula
nF = Replace(oF, "", "") ' Changing the row 1 to row 8
nF = Replace(nF, "", "") ' Changing the row 5 to row 12
ser.Formula = nF
Next ser
Next chart
Be sure to use the $sign, because the series .Formulauses numbers for the chart Typerepresentation. The sign helps to omit replacing the chart Type in the formula.
请务必使用$符号，因为系列.Formula使用数字表示图表类型。该符号有助于省略替换公式中的图表类型。
Hope this helps...
希望这可以帮助...
