retrieving data from a graph

opinsky

New Member
Joined
Sep 26, 2006
Messages
21
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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
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

New Member
Joined
Sep 26, 2006
Messages
21
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

Board Regular, The other chart guy
Joined
Dec 31, 2003
Messages
348
Not sure it will work on every chart but appears to work on your example file.
Code:
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

New Member
Joined
Sep 26, 2006
Messages
21
Thanks, perfect!

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

OP
 

Watch MrExcel Video

Forum statistics

Threads
1,113,919
Messages
5,545,027
Members
410,647
Latest member
bernardazar
Top