In Microsoft Excel, you can retrieve data from a chart even
when the data is in an external worksheet or workbook. This is useful in
situations where the chart was created from, or linked to, another file that is
unavailable or has been damaged in some way. When the source data to a chart is
lost, the data can still be retrieved from the chart itself, by using a
Microsoft Visual Basic for Applications macro.
Microsoft provides programming examples for illustration only,
without warranty either expressed or implied. This includes, but is not limited
to, the implied warranties of merchantability or fitness for a particular
purpose. This article assumes that you are familiar with the programming
language that is being demonstrated and with the tools that are used to create
and to debug procedures. Microsoft support engineers can help explain the
functionality of a particular procedure, but they will not modify these
examples to provide added functionality or construct procedures to meet your
The following sample macro places the chart's
source data in a worksheet called "ChartData" in the active workbook, beginning
in the first column and first row.
- Enter the following macro code in a module sheet:
Dim NumberOfRows As Integer
Dim X As Object
Counter = 2
' Calculate the number of rows of data.
NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values)
Worksheets("ChartData").Cells(1, 1) = "X Values"
' Write x-axis values to worksheet.
.Range(.Cells(2, 1), _
.Cells(NumberOfRows + 1, 1)) = _
' Loop through all series in the chart and write their values to
' the worksheet.
For Each X In ActiveChart.SeriesCollection
Worksheets("ChartData").Cells(1, Counter) = X.Name
.Range(.Cells(2, Counter), _
.Cells(NumberOfRows + 1, Counter)) = _
Counter = Counter + 1
- Insert a new worksheet into your workbook and rename it to
"ChartData" (without the quotation marks).
- Select the chart from which you want to extract the
underlying data values.
NOTE: The chart can either be embedded on a worksheet or on a separate
- Run the GetChartValues macro.
The data from the chart is placed in the
Steps to Link the Chart to the Recovered Data
To have the chart be interactive with the recovered data, you
need to link the chart to the new data sheet rather than retain the links to
the missing or damaged workbook.
- Select the chart, and click a series to find the sheet name
to which the chart is linked in the damaged or missing workbook. The sheet name
appears in the series formula in the formula bar.
NOTE: The sheet name may follow the workbook name, which is enclosed
in square brackets such as "[Book1]," and precede the exclamation point "!" (or
apostrophe and exclamation point "'!") that indicates the beginning of a cell
reference. The sheet name includes only the characters between the closed
square bracket symbol "]" and the exclamation point (or apostrophe and
exclamation point). Leave out any apostrophe if it is immediately before the
exclamation mark, because an apostrophe can not be the last character in a
- Double-click the tab of the new sheet called ChartData.
- Type the original sheet name from step 1 over the
highlighted "ChartData" and press ENTER. This name must be the same as the
sheet name from the damaged or missing workbook.
- If you have not saved this file with the chart and data
sheet, save the file.
Excel 2003 or Excel 2002, click Links on
the Edit menu, and then click Change Source.
Excel 2007, click the Data tab, click Edit Links
in the Connenctions group, and then click
- In the Source File box, select the link to change, and then click Change Source.
- In the Change Links dialog box, select the new file with the recovered data and
chart, and then click OK.
- If you receive the following error message
it is likely that the sheet name that you typed in step 3
is not the same as the original. Go back to step 1.
Your formula contains an invalid external reference to a
- The Source File box may now be blank. This indicates that all links point to the
active file rather than the missing or damaged file. Click Close.
The chart now references and interacts with the recovered data
on the renamed sheet in the active workbook.
Article ID: 300643 - Last Review: January 31, 2007 - Revision: 4.1
- Microsoft Office Excel 2007
- Microsoft Office Excel 2003
- Microsoft Excel 2002 Standard Edition
|kbdtacode kbhowto kbprogramming KB300643|