XL: Macro to Extract Data from a Chart

ID: Q137016

The information in this article applies to:

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 using a Microsoft Visual Basic for Applications macro.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/support/supportnet/refguide/
The following sample macros will 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 GetChartVaules97()
         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" 
   (without the quotation marks).
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 GetChartVaules97 macro. 

   The data from the chart will be placed in the "ChartData" worksheet.

Microsoft Excel version 5.0 or 7.0

1. Enter the following macro code in a module sheet: 

      Sub GetChartVaules()
         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" 
   (without the quotation marks).
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 GetChartVaules macro. 

   The data from the chart will be 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: 

   ARTICLE-ID: Q139401
   TITLE     : XL: "Subscript Out of Range" Error When XValues Referenced
Additional query words: 5.00 7.00 8.00 link unlink recover corrupt damage 
Keywords          : kbprg kbdta kbdtacode PgmHowto KbVBA xlchart 
Version           : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999