Add a Chart when exporting queries from Access to Excel

Question

I have created 4 queries formatted them and able to export them from access to excel format. My only question is - How can I add a chart to my queries once exported in Excel. I recorded a macro and copied the vba code in Access but unfortunately it didn't work. Please help.

Please note this question is lined to my previous one found in this link: Export and format multiple sheets from Access to Excel

Thanks Evan for helping me out thus far.

Answer 1

The following function is taken from a book called "Professional Access 2013 Programming" by WROX. You should consider buying it, as it would help you

Function AccessToExcelChartAutomation()

    Dim rsProducts As Recordset
    Dim wbk As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim rngCurr As Excel.Range
    Dim rangeChart As Range
    Dim chartNew As Chart

    On Error GoTo Err_AccessToExcelChartAutomation:

    '-- Open a recordset based on the qselProductSalesSummary query.
    Set rsProducts = CurrentDb.OpenRecordset("qselProductSalesSummary")

    '-- Open Excel, then add a workbook, then the first worksheet
    Set appExcel = New Excel.Application
    Set wbk = appExcel.Workbooks.Add
    Set wks = wbk.Worksheets(1)

    '-- In order to see the action!
    appExcel.Visible = True


    With wks
        .Name = "Raw Data"
        '-- Create the Column Headings
        .Cells(1, 1).Value = "Product"
        .Cells(1, 2).Value = "Cost"

        rsProducts.MoveLast
        rsProducts.MoveFirst

        '-- Specify the range to copy data into.
        Set rngCurr = .Range(wks.Cells(2, 1), _
             .Cells(2 + rsProducts.RecordCount, 3))

        rngCurr.CopyFromRecordset rsProducts

        '-- Format the columns
        .Columns("A:B").AutoFit
        .Columns(2).NumberFormat = "$ #,##0"

    End With

    rsProducts.Close
    Set rsProducts = Nothing

    '-- Specify the range to chart
    Set rangeChart = appExcel.ActiveSheet.Range("A:B")

    '== Add a chart to Excel
    Set chartNew = appExcel.Charts.Add

    '-- Create the chart by specifying the chart's source data.
    With chartNew
        .SetSourceData rangeChart
        .ChartType = xl3DColumn
        .Legend.Delete
   End With

   Exit Function

Err_AccessToExcelChartAutomation:

   Beep
   MsgBox "The Following Automation Error has occurred:" & _
                vbCrLf & Err.Description, vbCritical, "Automation Error!"
   Set appExcel = Nothing
   Exit Function

End Function

Answer 2

Before you go through considerable hassle creating VBA code to create a chart in excel, consider whether creating the chart in Access would be acceptable.

This video will show you what charts can do in access and how VBA can be used to manipulate them.

https://www.youtube.com/watch?v=YhgNX6BWWmk

If you do need to create an excel chart from access there are a number of methods.

one is discussed here

I think this is the method that will best meet your needs.

All methods involve writing code that references objects.

The following function from the above post is useful as it can be used from access to open a workbook that has already been created with code that builds your charts and it can then run them... leaving you with a opened but chnaged excel workbook.

Harvey

Function RunExcelMacros( _
  ByVal strFileName As String, _
  ParamArray avarMacros()) As Boolean

Debug.Print "xl ini", Time

  On Error GoTo Err_RunExcelMacros

  Static xlApp      As Excel.Application
  Dim xlWkb         As Excel.Workbook

  Dim varMacro      As Variant
  Dim booSuccess    As Boolean
  Dim booTerminate  As Boolean

  If Len(strFileName) = 0 Then
    ' Excel shall be closed.
    booTerminate = True
  End If

  If xlApp Is Nothing Then
    If booTerminate = False Then
      Set xlApp = New Excel.Application
    End If
  ElseIf booTerminate = True Then
    xlApp.Quit
    Set xlApp = Nothing
  End If

  If booTerminate = False Then
    Set xlWkb = xlApp.Workbooks.Open(FileName:=strFileName, UpdateLinks:=0, ReadOnly:=True)

    ' Make Excel visible (for troubleshooting only) or not.
    xlApp.Visible = False 'True

    For Each varMacro In avarMacros()
      If Not Len(varMacro) = 0 Then
  Debug.Print "xl run", Time, varMacro
        booSuccess = xlApp.Run(varMacro)
      End If
    Next varMacro
  Else
    booSuccess = True
  End If

  RunExcelMacros = booSuccess

Exit_RunExcelMacros:

  On Error Resume Next

  If booTerminate = False Then
    xlWkb.Close SaveChanges:=False
    Set xlWkb = Nothing
  End If

Debug.Print "xl end", Time
  Exit Function

Err_RunExcelMacros:
  Select Case Err
    Case 0      'insert Errors you wish to ignore here
      Resume Next
    Case Else   'All other errors will trap
      Beep
      MsgBox "Error: " & Err & ". " & Err.Description, vbCritical +
vbOKOnly, "Error, macro " & varMacro
      Resume Exit_RunExcelMacros
  End Select

End Function