Macro to Extract Data from a Chart

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.

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: