retrieving data from a graph

[opinsky]

Hello,

I guess this has been asked a thousand times, but I could not find on the board archive.

I have an excel file that has a graph, linked to data on another spreadsheet. This spreadsheet is lost, so I only have the graph. I want to be able to get the data back from the chart. I could type it in (value labels are enabled), but I am wondering if there is an easier way to get the data that generates a graph from the graph itself.

Thanks in advance.

OP.

 

[MarkAndrews]

Hi & Welcome

Not to my knowledge - If you have a graph, It must have 'source data' in order for it to visualise.

Have you checked to see where the source data is coming from, it might be on a hidden sheet, there again if the source data is read from another seperate excel file - you might be able to locate it using this method

Mark

 

[opinsky]

Thanks for the prompt reply!

No. If you create a chart, move it to a new file and delete the sheet that contained the data, the graph would still be there and can be saved. Excel seems to save the data to a graph with the graph itself.

The graph-source data is pointing to a file that does not exist and there are no hidden (nor very hidden) sheets. One example of such data is on the following spreadsheet posted on the web:

http://www.oecd.org/dataoecd/18/41/16361624.xls

I think getting the data points using a macro would solve, but again, this is not robust nor elegant.

Thanks again for the prompt reply!

OP

 

[Andy Pope]

Not sure it will work on every chart but appears to work on your example file.

Sub x()
    Dim vntData As Variant
    Dim vntLabels As Variant
    Dim strName As String
    Dim objSeries As Series
    Dim lngIndex As Long
    
    With ActiveChart
        For Each objSeries In .SeriesCollection
            strName = objSeries.Name
            vntData = objSeries.Values
            vntLabels = objSeries.XValues
        
            Debug.Print "series ", strName
            For lngIndex = LBound(vntData) To UBound(vntData)
                Debug.Print " "; vntLabels(lngIndex), vntData(lngIndex)
            Next
        Next
    End With
End Sub

 

[opinsky]

Thanks, perfect!

Although it needs some modifications to run on other graphs, the template works pretty well!

OP