Macro to Extract Data from a Chart

Article translations Article translations
Article ID: 137016 - View products that this article applies to.
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.
Expand all | Collapse all

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.

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

Properties

Article ID: 137016 - Last Review: October 11, 2006 - Revision: 2.3
APPLIES TO
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 98 for Macintosh
Keywords: 
kbdtacode kbhowto kbprogramming KB137016
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.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com