How to get the the source data of all the series o

问题:

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.

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

回答1:

Here is an example

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

Screenshot

Before

After

回答2:

Use .seriescollection

Set ChartSeries = ChartObj.Chart.SeriesCollection.NewSeries

With ChartSeries
    .Name = "Chart Series 1"
    .Values = Array(1, 2, 3, 4, 5)
    .XValues = Array("alpha", "beta", "gamma", "delta", "epsilon")
End With

More on this link.

回答3:

You can access the data source of the chart series by retrieving the series .Formula string.

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

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, "$1", "$8") ' Changing the row 1 to row 8
        nF = Replace(nF, "$5", "$12") ' Changing the row 5 to row 12
        ser.Formula = nF
    Next ser

Next chart

Be sure to use the $ sign, because the series .Formula uses numbers for the chart Type representation. The sign helps to omit replacing the chart Type in the formula.

Hope this helps...