You are currently offline, waiting for your internet to reconnect

Macro to Extract Data from a Chart

This article was previously published under Q137016
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
For a Microsoft Excel 2002 version of this article, see 300643.
For a Microsoft Excel 2000 version of this article, see 213814.
SUMMARY
In Microsoft Excel, you can retrieve data from a chart even when the datais in an external worksheet or workbook. This is useful in situations wherethe chart was created from, or linked to, another file that is unavailableor 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 MicrosoftVisual Basic for Applications macro.
MORE INFORMATION
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 aworksheet called "ChartData" in the active workbook, beginning in the firstcolumn 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.

Microsoft Excel version 5.0 or 7.0

  1. Enter the following macro code in a module sheet:
          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 thevalues returned, please see the following article in the MicrosoftKnowledge Base:
139401 XL: "Subscript Out of Range" Error When XValues Referenced
8.00 link unlink recover corrupt damage XL
Properties

Article ID: 137016 - Last Review: 10/11/2006 01:38:14 - Revision: 2.3

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 98 for Macintosh
  • kbdtacode kbhowto kbprogramming KB137016
Feedback