Help and Support

Macro to Extract Data from a Chart

Article ID:137016
Last Review:October 11, 2006
Revision:2.3
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.

Back to the top

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.

Back to the top

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.

Back to the top

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 (http://support.microsoft.com/kb/139401/EN-US/) XL: "Subscript Out of Range" Error When XValues Referenced

Back to the top


APPLIES TO
Microsoft Excel 97 Standard Edition
Microsoft Excel 95 Standard Edition
Microsoft Excel 5.0 Standard Edition
Microsoft Excel 98 for Macintosh

Back to the top

Keywords: 
kbdtacode kbhowto kbprogramming KB137016

Back to the top

Article Translations

 

Related Support Centers

Other Support Options

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