How to get the the source data of all the series of a chart in VBA?

excel vba charts

16,390

Solution 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

enter image description here

After

enter image description here

Solution 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

Solution 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...

16,390

Author by

Eduard Florinescu

Coding my way out of boredom. “If the fool would persist in his folly he would become wise.” (William Blake)

Updated on July 30, 2022

Comments

Eduard Florinescu over 1 year
```
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?
glh about 11 years

Nice one @Sid. I'm on my phone ;)
Siddharth Rout about 11 years

+ 1 :) I'm on my phone ;) – glh 2 mins ago Formatted the post for you :)