Extract Chart Data With VBA

Introduction

Have you ever needed to extract chart data? If this question makes you wonder, you are not alone. At first, this sounds quite irrelevant. Why somebody will ever need to extract data from a chart that he/she probably created. But, let’s consider the following scenario:

You receive an email with an attachment from one of your colleagues. The attachment is just a workbook that contains a single chart showing the products sold every month. The chart, in fact, has been copied from another workbook, but you don’t get the data, but only the chart.

When you open the workbook, you receive the annoying message shown in the image below:

You need the chart data to process them further and to create additional charts. What will you do? You will probably ask your colleague to send you the workbook with the data, or you can save some time and use VBA to extract the chart data.

The above scenario is not fictional. It has happened to me several times. If you are dealing with many spreadsheets, it is not surprising to receive dashboards that contain a lot of charts. However, some people often forget to include the data in these workbooks, so you end up having charts without the actual data (e.g., the data are in other workbooks).

VBA code to extract chart data 

If you want to avoid situations like the one described above, you can use the following VBA code. Just select the chart from which you want to extract the data and run the macro. Automatically, all the data from all the chart series will be written to a new worksheet named “Chart Data.”

Option Explicit

Sub ExtractChartData()

    '-----------------------------------------------------------------
    'Extracts all the data from all the series of the selected chart.
    'It writes the data to a new worksheet named "Chart Data".
        
    'Written By:    Christos Samaras
    'Date:          12/06/2013
    'Last Updated:  21/06/2020
    'E-mail:        [email protected]
    'Site:          https://myengineeringworld.net
    '-----------------------------------------------------------------
    
    'Declaring the necessary variables.
    Dim myChart     As Chart
    Dim sht         As Worksheet
    Dim i           As Integer
    Dim mySeries    As Series
    
    'Check if a chart was selected.
    If ActiveChart Is Nothing Then
        MsgBox "No chart was selected!" & vbCr & "Please select a chart and retry...", vbCritical, "Chart Error"
        Exit Sub
    End If
    
    'Stop the screen flickering.
    Application.ScreenUpdating = False
    
    'Asign the active chart to the myChart variable.
    Set myChart = ActiveChart
    
    'Add a new worksheet.
    Set sht = Worksheets.Add
        
    'Rename the new worksheet and change its tab's color to red.
    On Error Resume Next
    With sht
        .Name = "Chart Data"
        .Tab.Color = vbRed
        .Activate
    End With
        
    'Loop through all the chart series and extract their data.
    For i = 1 To myChart.SeriesCollection.Count
        
        Set mySeries = myChart.SeriesCollection(i)
        
        With sht
            
            'For the x-values, use the axis title (if it exists).
            If myChart.Axes(xlCategory, xlPrimary).HasTitle = True Then
                .Cells(1, 2 * i - 1).Value = myChart.Axes(xlCategory, xlPrimary).AxisTitle.Caption
                .Cells(1, 2 * i - 1).Font.Bold = True
            End If
            
            'Series name.
            .Cells(1, 2 * i).Value = mySeries.Name
            .Cells(1, 2 * i).Font.Bold = True
            
            'Actual data.
            .Cells(2, 2 * i - 1).Resize(mySeries.Points.Count).Value = Application.Transpose(mySeries.XValues)
            .Cells(2, 2 * i).Resize(mySeries.Points.Count).Value = Application.Transpose(mySeries.Values)
            
        End With
        
    Next i
    
    'Fix the columns' width that contain the data.
    Columns("1:" & myChart.SeriesCollection.Count * 2).EntireColumn.AutoFit
    
    'Re-enable the screen.
    Application.ScreenUpdating = True
    
   'Inform the user that the macro finished.
    MsgBox "Chart data were extracted successfully!", vbInformation, "Done"
    
End Sub 

The sample workbook you will find in the Downloads section contains a chart with two series but without their data. However, by pressing the “Extract Data” button, all the data are extracted.

If you have ever faced this “extract chart data situation,” just let me know in the comments.

Downloads

The file can be opened with Excel 2007 or newer. Please enable macros before using it.

Note that if you receive the “We can’t update some of the links in your workbooks right now.” error shown in the above image, click the “Continue” button and then run the macro to extract chart data.

Read also

Create An Excel Chart With Dynamic Data Range
Daily Schedule Charts
Export Chart(s) As TIFF Image(s) Using Adobe Professional XI

Page last updated: 21/06/2020