This article was previously published under Q137016
For a Microsoft Excel 2002 version of this article, see 300643 (http://support.microsoft.com/kb/300643/EN-US/).
For a Microsoft Excel 2000 version of this article, see 213814 (http://support.microsoft.com/kb/213814/EN-US/).
On This Page
SUMMARY
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 specific requirements.
The following sample macros place the chart's source data in a
worksheet called "ChartData" in the active workbook, beginning in the first
column and first row.
Excel 97 for Windows and Excel 98 Macintosh Edition
1.
Enter the following macro code in a module sheet:
Sub GetChartValues97()
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.
With Worksheets("ChartData")
.Range(.Cells(2, 1), _
.Cells(NumberOfRows + 1, 1)) = _
Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
End With
' 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
With Worksheets("ChartData")
.Range(.Cells(2, Counter), _
.Cells(NumberOfRows + 1, Counter)) = _
Application.Transpose(X.Values)
End With
Counter = Counter + 1
Next
End Sub
2.
Insert a new worksheet into your workbook and rename it to ChartData.
3.
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
chart sheet.
4.
Run the GetChartValues97 macro.
The data from the chart is placed in the "ChartData" worksheet.
Sub GetChartValues()
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.
With Worksheets("ChartData")
.Range(.Cells(2, 1), .Cells(NumberOfRows + 1, 1)) _
= ActiveChart.SeriesCollection(1).XValues
End With
' 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
With Worksheets("ChartData")
.Range(.Cells(2, Counter), _
.Cells(NumberOfRows + 1, Counter)) = X.Values
End With
Counter = Counter + 1
Next
End Sub
2.
Insert a new worksheet into your workbook and rename it to ChartData.
3.
Select or activate 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
chart sheet.
4.
Run the GetChartValues macro.
The data from the chart is placed in the "ChartData" worksheet.
For additional information about the Xvalues property and referencing the
values returned, please see the following article in the Microsoft
Knowledge Base:
139401 (http://support.microsoft.com/kb/139401/EN-US/) XL: "Subscript Out of Range" Error When XValues Referenced
Contact Microsoft Phone Numbers, Support Options and Pricing, Online Help, and more.
Customer Service For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
Newsgroups Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.
Back to the top
