Rookie Rookie - 1 year ago 150 Question

Get Excel Chart datasource

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


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

Answer Source

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))
                Set rReturn = Union(rReturn, Range(vaArgs(i)))
            End If
        Next i
    Next srs

    Set GetSourceData = rReturn

End Function

Use as:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download