Microsoft KB Archive/137016

Knowledge Base

Macro to Extract Data from a Chart

Article ID: 137016

Article Last Modified on 10/11/2006

---

APPLIES TO

• Microsoft Excel 97 Standard Edition
• Microsoft Excel 95 Standard Edition
• Microsoft Excel 5.0 Standard Edition
• Microsoft Excel 98 for Macintosh

---

This article was previously published under Q137016

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 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.

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 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.

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 the values returned, please see the following article in the Microsoft Knowledge Base:

139401 XL: "Subscript Out of Range" Error When XValues Referenced

Additional query words: 8.00 link unlink recover corrupt damage XL

Keywords: kbhowto kbprogramming kbdtacode KB137016

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.