Today, I thought I talk a little about exporting data from Access to Excel and generate a chart from it.
My hope today is actually 2 fold:

• Show you how to export data to Excel and generate charts from that data
• Show you how code can evolve from primitive to reusable user defined procedures (UDP)

Exporting Data To Excel

Built-In Approach With No Control

Most developers know that they can export data to Excel by using the DoCmd.TransferSpreadsheet method

DoCmd.TransferSpreadsheet method (Access)

The TransferSpreadsheet method carries out the TransferSpreadsheet action in Visual Basic.

I’m not going to cover this, it is very straightforward and in my opinion a waste as you have no control on formatting, can’t set freeze panes or sorting, can’t automate charts, …

This technique is only good if you only want to get out raw data and don’t care at all about the user experience, say it is being used to feed another system. The minute the Excel file is to be used by mere mortals, using Excel Automation is definitely the way to go, so keep reading.

Using Excel Automation With Full Control

I have already covered the basics of exporting data to Excel in a couple previous posts

So today, I’m assuming that the above is in place and functional. I’m going to instead demonstrate how we can add to the process and create a chart from the data.

Creating an Excel Chart Using VBA Automation

Creating a chart via Excel automation is quite easy, we simply need to use the AddChart2 method

Shapes.AddChart2 method (Excel)

Adds a chart to the document. Returns a Shape object that represents a chart and adds it to the specified collection.

So I exported my data to Excel, started up the Macro Recorder and created a chart. Then reviewed the generated code to give me a starting point. What I got was:

    Range("A1:B6").Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$6")

And so, I was off to the races!

Baby Steps

So the first step in developing code to generate a chart after exporting the data is to take the Macro Recorder code and insert it into the existing code.

(for simplification purposes, I’m only showing a segment of the original Export2XLS function)

So, we get:

‘…    
    End With

    'Add Chart
    Range("A1:B6").Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$6")

    oExcelWrSht.Range("A1").Select
‘…

Now the problem being, I use Late Binding primarily, so Access doesn’t understand Excel constants, nor does it know things like Range, ActiveSheet, … Thus, we have to massage the code slightly. Hence, I end up with:

‘…
    End With

    'Add Chart
    Const xlColumnClustered = 51
    
    oExcelWrSht.Range("A1:B6").Select
    oExcelWrSht.Shapes.AddChart2(201, xlColumnClustered).Select
    oExcelWrkBk.ActiveChart.SetSourceData Source:=oExcelWrSht.Range("$A$1:$B$6")

    oExcelWrSht.Range("A1").Select
‘…

So we now have basic code that works reliably embedded within our original function.

Now Were Walking

Now that we have basic, functional code, let’s look at converting it over to being a user defined procedure that we can call, as required, where required.

Why would we do this you may ask?

It is never a good idea to build a single never ending procedure. You want to build components that you can reuse as required throughout your solution. This, amongst other things, minimizes code, increases reliability, standardizes things and makes things more legible …

It’s kind of like a library, you don’t have a single big book of all things combined. No, you have individual books covering individual subjects that you take out as you need them. Same is true of good coding!

Now that we got that out of the way, let’s see how we can thus take the code we previously got working into a reusable procedure that we can call.

So the first thing to do is to create a new procedure and move the code over. So we get something like:

Public Sub Excel_CreateChart()
    Const xlColumnClustered = 51
    
    oExcelWrSht.Range("A1:B6").Select
    oExcelWrSht.Shapes.AddChart2(201, xlColumnClustered).Select
    oExcelWrkBk.ActiveChart.SetSourceData Source:=oExcelWrSht.Range("$A$1:$B$6")

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Excel_CreateChart" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Next, we have to add the necessary input arguments so the Sub actually knows what oExcelWrkBk , oExcelWrSht … are. So we’d do:

Public Sub Excel_CreateChart(ByVal oExcelWrkBk As Object, ByVal oExcelWrSht As Object)
    Const xlColumnClustered = 51
    
    oExcelWrSht.Range("A1:B6").Select
    oExcelWrSht.Shapes.AddChart2(201, xlColumnClustered).Select
    oExcelWrkBk.ActiveChart.SetSourceData Source:=oExcelWrSht.Range("$A$1:$B$6")

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Excel_CreateChart" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Lastly, we switch the code in the Export2XLS procedure to call our new Sub:

‘…
    End With

    'Add Chart
    Call Excel_CreateChart(oExcelWrkBk, oExcelWrSht)

    oExcelWrSht.Range("A1").Select
‘…

Okay, so now we have made a functional procedure we can call in any of our excel automation procedures, right? Yes and no, we still have one big issue, that is the fact that the range used to generate the data is hardcoded and that’s a no-no.
So we can modify our code to accept the range as an input argument making it fully flexible for anything we throw at it. This would result in :

Public Sub Excel_CreateChart(ByVal oExcelWrkBk As Object, ByVal oExcelWrSht As Object, oRng As Object)
    Const xlColumnClustered = 51
    
    oRng.Select
    oExcelWrSht.Shapes.AddChart2(201, xlColumnClustered).Select
    oExcelWrkBk.ActiveChart.SetSourceData Source:=oRng

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Excel_CreateChart" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Which you then call using

Call Excel_CreateChart(oExcelWrkBk, oExcelWrSht, oExcelWrSht.Range("A1:B" & iRecCount + 1))

Where I use +1 because of a header row in the Excel worksheet.

Now, we have a Sub that can be called reliably from any Excel automation we create.

Going For A Run

Okay, so we’ve made some nice progress and have a reusable piece of code, but let’s take it to the next level!

Let’s be serious for one moment, it will be very rare that the following would truly be useful in a real-world application because it is very limited in its capacity: limited to Clustered Column chart and we don’t set any other elements like the title, axes, legend …

So how can we take the basics we created above and build a truly useful piece of code?

Let me simply show you the final product and then we can discuss it briefly.

Public Enum XlChartType
    xlColumnClustered = 51
    xlColumnStacked = 52
    xlColumnStacked100 = 53
    xl3DColumnClustered = 54
    xl3DColumnStacked = 55
    xl3DColumnStacked100 = 56
    xlBarClustered = 57
    xlBarStacked = 58
    xlBarStacked100 = 59
    xl3DBarClustered = 60
    xl3DBarStacked = 61
    xl3DBarStacked100 = 62
    xlLineStacked = 63
    xlLineStacked100 = 64
    xlLineMarkers = 65
    xlLineMarkersStacked = 66
    xlLineMarkersStacked100 = 67
    xlPieOfPie = 68
    xlPieExploded = 69
    xl3DPieExploded = 70
    xlBarOfPie = 71
    xlXYScatterSmooth = 72
    xlXYScatterSmoothNoMarkers = 73
    xlXYScatterLines = 74
    xlXYScatterLinesNoMarkers = 75
    xlAreaStacked = 76
    xlAreaStacked100 = 77
    xl3DAreaStacked = 78
    xl3DAreaStacked100 = 79
    xlDoughnutExploded = 80
    xlRadarMarkers = 81
    xlRadarFilled = 82
    xlSurface = 83
    xlSurfaceWireframe = 84
    xlSurfaceTopView = 85
    xlSurfaceTopViewWireframe = 86
    xlBubble = 15
    xlBubble3DEffect = 87
    xlStockHLC = 88
    xlStockOHLC = 89
    xlStockVHLC = 90
    xlStockVOHLC = 91
    xlCylinderColClustered = 92
    xlCylinderColStacked = 93
    xlCylinderColStacked100 = 94
    xlCylinderBarClustered = 95
    xlCylinderBarStacked = 96
    xlCylinderBarStacked100 = 97
    xlCylinderCol = 98
    xlConeColClustered = 99
    xlConeColStacked = 100
    xlConeColStacked100 = 101
    xlConeBarClustered = 102
    xlConeBarStacked = 103
    xlConeBarStacked100 = 104
    xlConeCol = 105
    xlPyramidColClustered = 106
    xlPyramidColStacked = 107
    xlPyramidColStacked100 = 108
    xlPyramidBarClustered = 109
    xlPyramidBarStacked = 110
    xlPyramidBarStacked100 = 111
    xlPyramidCol = 112
    xl3DColumn = -4100
    xlLine = 4
    xl3DLine = -4101
    xl3DPie = -4102
    xlPie = 5
    xlXYScatter = -4169
    xl3DArea = -4098
    xlArea = 1
    xlDoughnut = -4120
    xlRadar = -4151
End Enum

Public Enum XlDataLabelPosition
    msoElementDataLabelBestFit = 210    'Use best fit for data label.
    msoElementDataLabelBottom = 209    'Display data label at bottom.
    msoElementDataLabelCallout = 211    'Display data label as a callout.
    msoElementDataLabelCenter = 202    'Display data label in center.
    msoElementDataLabelInsideBase = 204    'Display data label inside at the base.
    msoElementDataLabelInsideEnd = 203    'Display data label inside at the end.
    msoElementDataLabelLeft = 206    'Display data label to the left.
    msoElementDataLabelNone = 200    'Do not display data label.
    msoElementDataLabelOutSideEnd = 205    'Display data label outside at the end.
    msoElementDataLabelRight = 207    'Display data label to the right.
    msoElementDataLabelShow = 201    'Display data label.
    msoElementDataLabelTop = 208    'Display data label at the top.
End Enum

Public Enum XlLegendPosition
    msoElementLegendBottom = 104    'Display legend at the bottom.
    msoElementLegendLeft = 103    'Display legend on the left.
    msoElementLegendLeftOverlay = 106    'Overlay legend at the left.
    msoElementLegendNone = 100    'Do not display legend.
    msoElementLegendRight = 101    'Display legend at the right.
    msoElementLegendRightOverlay = 105    'Overlay legend at the right.
    msoElementLegendTop = 102    'Display legend at the top.
End Enum

'---------------------------------------------------------------------------------------
' Procedure : Excel_CreateChart
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Procedure to generate a chart in Excel
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' oExcelWrSht       : Excel workSheet object
' oRng              : Data series range object used to generate the chart from
' sTopLeftRange     : Top left range to start the chart at
' sBottomRightRange : Bottom right range to end the chart at
' lChrtType         : Type of chart to create
' lDataLabelPosition: Data label position
' lLegendPosition   : Legend position
' sChartTitle       : Chart title
' sXAxisLabel       : X-axis label
' sYAxisLabel       : Y-axis label
'
' Usage:
' ~~~~~~
' Call Excel_CreateChart(oExcelWrSht, oExcelWrSht.Range("A1:B" & iRecCount + 1))
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2019-07-11              Initial Release
' 2         2022-04-18              Initial Public Release
'---------------------------------------------------------------------------------------
Public Sub Excel_CreateChart(oExcelWrSht As Object, _
                       oRng As Object, _
                       sTopLeftRange As String, _
                       sBottomRightRange As String, _
                       lChrtType As XlChartType, _
                       Optional lDataLabelPosition As XlDataLabelPosition = msoElementDataLabelNone, _
                       Optional lLegendPosition As XlLegendPosition = msoElementLegendNone, _
                       Optional sChartTitle As String, _
                       Optional sXAxisLabel As String, _
                       Optional sYAxisLabel As String)
    Dim oChrt                 As Object
    'Chart Axis
    Const xlCategory = 1 'x-axis
    Const xlValue = 2 'y-axis

    On Error GoTo Error_Handler

    Set oChrt = oExcelWrSht.Shapes.AddChart2(-1, lChrtType)    'Column Chart
    With oChrt
        With .Chart
            'Data Source/Series
            .SetSourceData Source:=oRng
            'Chart Title
            .HasTitle = (sChartTitle <> "")
            If sChartTitle <> "" Then .ChartTitle.Text = sChartTitle
            'Axis
            '   X-Axis label
            If sXAxisLabel <> "" Then
                With .Axes(xlCategory)
                    .HasTitle = True
                    .AxisTitle.Text = sXAxisLabel
                End With
            End If
            '   Y-Axis label
            If sYAxisLabel <> "" Then
                With .Axes(xlValue)
                    .HasTitle = True
                    .AxisTitle.Text = sYAxisLabel
                End With
            End If
            'Data Labels
            .SetElement lDataLabelPosition
            'Legend
            .SetElement lLegendPosition
        End With
        
        'Position and Size of Chart
        .Left = oExcelWrSht.Range(sTopLeftRange).Left
        .Top = oExcelWrSht.Range(sTopLeftRange).Top
        .Height = oExcelWrSht.Range(sBottomRightRange).Offset(0, 1).Top - .Top
        .Width = oExcelWrSht.Range(sBottomRightRange).Offset(0, 1).Left - .Left
    End With

Error_Handler_Exit:
    On Error Resume Next
    If Not oChrt Is Nothing Then Set oChrt = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Excel_CreateChart" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Which we can simply call by doing:
Line Chart

          Call Excel_CreateChart(oExcelWrSht, _
                           oExcelWrSht.Range("A1:B" & iRecCount + 1), _
                           "H2", _
                           "R20", _
                           xlLine, _
                           msoElementDataLabelNone, _
                           msoElementLegendNone, _
                           "Quantity by Produce Type", _
                           "Produce", _
                           "Quantity")

OR
Bar Chart

          Call Excel_CreateChart(oExcelWrSht, _
                           oExcelWrSht.Range("A1:B" & iRecCount + 1), _
                           "H22", _
                           "R40", _
                           xlBarClustered)

OR
Pie Chart

          Call Excel_CreateChart(oExcelWrSht, _
                           oExcelWrSht.Range("A1:B" & iRecCount + 1), _
                           "H42", _
                           "R60", _
                           xl3DPie, _
                           msoElementDataLabelBestFit, _
                           msoElementLegendRight)

Now, we can control the size of the chart, the type of chart, the title, axes, legend, … making this a useful procedure to have.

I truly hope:

• The Excel_CreateChart function helps some of you out there. It was requested on more than one occasion.
• The article helps you understand the process of taking some very basic code and turning it into a valuable reusable procedure. It is a step by step process. So just persevere and you’ll be able to do the same with whatever you are working on.