Get Excel Chart datasource

Question

It is easy to set a datasource for an Excel Chart with Visual Studio, like

Chart.SetSourceData(Source:=SomeRange)

But how do I GET (retrieve) the datasource (Range) for an already exisiting Chart in an Excel file ?

score 0 · Answer 1 · edited May 23 '17 at 12:01

0

I think its been solved here already: Excel VBA - Get chart data range https://stackoverflow.com/a/28391220/6868389

edited May 23 '17 at 12:01

Community

1
1

answered Nov 15 '16 at 23:02

lllpratll

368
1
3
8

score 0 · Accepted Answer · answered Nov 15 '16 at 23:11

Here's a function that will parse the ranges out of the series. If you have custom series formulas that don't use ranges, it will probably break.

Public Function GetSourceData(ByRef cht As Chart) As Range

    Dim srs As Series
    Dim vaArgs As Variant
    Dim i As Long
    Dim rReturn As Range

    For Each srs In cht.SeriesCollection
        vaArgs = Split(Split(srs.Formula, "SERIES(")(1), ",")
        For i = 0 To UBound(vaArgs) - 1
            If rReturn Is Nothing Then
                Set rReturn = Range(vaArgs(i))
            Else
                Set rReturn = Union(rReturn, Range(vaArgs(i)))
            End If
        Next i
    Next srs

    Set GetSourceData = rReturn

End Function

Use as:

?getsourcedata(activechart).Address
$B$2:$C$2,$A$3:$C$14

score 0 · Answer 3 · answered Nov 15 '16 at 23:55

The Visual Studio code looks like this:

Dim g As String
Dim gg() As String
Dim a As Excel.ChartObject

    a = Globals.ThisAddIn.Application.ActiveSheet.chartobjects(1)
    g = a.Chart.SeriesCollection(1).formula
    gg = g.Split(",")

    MsgBox(gg(2))

Get Excel Chart datasource

3 Answers3