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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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

Active Member
Joined
Dec 31, 2003
Messages
349
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
 

Forum statistics

Threads
1,141,011
Messages
5,703,726
Members
421,311
Latest member
tanujath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top