Extract data from delinked Excel plot

Question

I have a Word file which has some Excel plots in it. Unfortunately I lost the original excel plots and the word file is now 'de-linked'.

Is there a way to retrieve the lost data for the plots? Just copying the plot back to Excel does not seem to work, but I can see that the data is still there somewhere, because hovering over a dot on the plot shows the values.

Answer 1

I've had to do the same with a PowerPoint 2010 presentation. I think as a long-term solution, VBA script is a good way to go. However I could not make it work in my case for some reason, and I only needed to get data once. (As a structural solution, you should probably not loose your original data in the first place.) This is what can be done:

1. Rename the PPT file to have the .zip extension
2. Unzip the resulting archive to a folder.
3. The folder should contain a ppt/charts/ subfolder.
4. Open chart1.xml with Excel as an XML table. (Just open the file and Excel will ask you how to open it.)
5. The document will have lots of columns, many of them with the same value in each row. One column will be the x-values of your chart. You can find it by comparing values to what you see in the presentation. The y-values are in another column and in my case also way below the x-values (so the first rows in the y-data column are empty).
6. I found it easiest to copy-paste the x and y-values next to each other in a new document.
7. To verify the data, select the pasted data and do Insert > Charts > Scatter > With smooth lines. The first column should be x-values, the second y-values.
8. I've found it easier to create a new presentation with just one curve from the original file. However, if there are many charts, you will probably see also chart2.xml etc. in the unzipped folder. If a chart contains more than 1 line, you will have to find the additional data in the XML.

Answer 2

You have to use VBA to extract the data. Here's one possible VBA solution: http://peltiertech.com/WordPress/extract-chart-data/. It includes this code:

Sub ExtractChartData()
   Dim iSrs As Long
   Dim cht As Chart
   Dim srs As Series
   Dim ws As Worksheet

   If ActiveChart Is Nothing Then Exit Sub

   Set cht = ActiveChart
   Set ws = Worksheets.Add
   For iSrs = 1 To cht.SeriesCollection.Count
     Set srs = cht.SeriesCollection(iSrs)
     On Error Resume Next
     ws.Cells(1, 2 * iSrs).Value = srs.Name
     ws.Cells(2, 2 * iSrs - 1).Resize(srs.Points.Count).Value = _
         WorksheetFunction.Transpose(srs.XValues)
     ws.Cells(2, 2 * iSrs).Resize(srs.Points.Count).Value = _
         WorksheetFunction.Transpose(srs.Values)
   Next
 End Sub

Here is another possible solution, direct from Microsoft: http://support.microsoft.com/kb/300643 (Also involves VBA)

Answer 3

There is a pretty down and dirty solution if there isn't a massive amount of data.

Select the series, select the layout tab in chart tools, add data labels, you can then simply read the values and type them where you need them.