Automating Excel from Access VBA: Export, Format, and Send

Access and Excel are natural partners. Access stores and queries the data; Excel presents it with formatting, charts, and pivot tables that Access reports cannot match. With VBA, you can automate the entire workflow — query the data, create an Excel workbook, populate it with formatted output, add charts, and email it — all with a single button click.

Setting Up Excel Automation

Add a reference to the Excel Object Library:

1. In the VBA editor, go to Tools → References
2. Check Microsoft Excel XX.X Object Library
3. Click OK

Creating a New Excel Workbook

Sub CreateExcelReport()
    On Error GoTo ErrorHandler
    
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    Dim xlWs As Excel.Worksheet
    
    ' Create a new Excel instance
    Set xlApp = New Excel.Application
    xlApp.Visible = False  ' Run in background
    
    ' Create a new workbook
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets(1)
    xlWs.Name = "Sales Report"
    
    ' ... populate the worksheet ...
    
    ' Save and close
    xlWb.SaveAs "C:\Reports\SalesReport_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
    xlWb.Close
    xlApp.Quit
    
    Set xlWs = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing
    
    MsgBox "Report created successfully."
    Exit Sub

ErrorHandler:
    If Not xlApp Is Nothing Then xlApp.Quit
    MsgBox "Error: " & Err.Description
End Sub

Populating Cells from a Query

Sub PopulateFromQuery(xlWs As Excel.Worksheet)
    Dim db As Database
    Dim rs As Recordset
    Dim row As Integer
    Dim col As Integer
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryMonthlySales")
    
    ' Write header row
    row = 1
    For col = 0 To rs.Fields.Count - 1
        xlWs.Cells(row, col + 1).Value = rs.Fields(col).Name
        xlWs.Cells(row, col + 1).Font.Bold = True
    Next col
    
    ' Write data rows
    row = 2
    Do While Not rs.EOF
        For col = 0 To rs.Fields.Count - 1
            xlWs.Cells(row, col + 1).Value = rs.Fields(col).Value
        Next col
        row = row + 1
        rs.MoveNext
    Loop
    
    rs.Close
    
    ' Auto-fit columns
    xlWs.Columns.AutoFit
End Sub

Formatting the Worksheet

Sub FormatWorksheet(xlWs As Excel.Worksheet, lastRow As Integer, lastCol As Integer)
    ' Format header row
    With xlWs.Range(xlWs.Cells(1, 1), xlWs.Cells(1, lastCol))
        .Interior.Color = RGB(46, 109, 164)  ' Steel blue
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
        .Font.Size = 11
    End With
    
    ' Alternate row shading
    Dim i As Integer
    For i = 2 To lastRow
        If i Mod 2 = 0 Then
            xlWs.Rows(i).Interior.Color = RGB(235, 242, 250)
        End If
    Next i
    
    ' Format currency columns (assuming column 3 is Amount)
    xlWs.Columns(3).NumberFormat = "$#,##0.00"
    
    ' Format date columns (assuming column 2 is Date)
    xlWs.Columns(2).NumberFormat = "mm/dd/yyyy"
    
    ' Add borders to data range
    With xlWs.Range(xlWs.Cells(1, 1), xlWs.Cells(lastRow, lastCol)).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(200, 200, 200)
    End With
    
    ' Freeze the header row
    xlWs.Rows(2).Select
    xlWs.Application.ActiveWindow.FreezePanes = True
End Sub

Adding a Chart

Sub AddChart(xlWs As Excel.Worksheet, dataRange As String)
    Dim xlChart As Excel.ChartObject
    
    ' Add a chart to the worksheet
    Set xlChart = xlWs.ChartObjects.Add(Left:=400, Top:=10, Width:=400, Height:=250)
    
    With xlChart.Chart
        .SetSourceData xlWs.Range(dataRange)
        .ChartType = xlColumnClustered
        .HasTitle = True
        .ChartTitle.Text = "Monthly Sales by Region"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Month"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Sales ($)"
    End With
End Sub

Opening an Existing Workbook

Sub UpdateExistingWorkbook(filePath As String)
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    
    Set xlApp = New Excel.Application
    xlApp.Visible = False
    
    ' Open existing workbook
    Set xlWb = xlApp.Workbooks.Open(filePath)
    
    ' Clear old data (keep headers in row 1)
    xlWb.Worksheets(1).Range("A2:Z10000").ClearContents
    
    ' Repopulate with fresh data
    ' ... your population code here ...
    
    xlWb.Save
    xlWb.Close
    xlApp.Quit
End Sub

Using TransferSpreadsheet for Simple Exports

For straightforward exports without custom formatting, DoCmd.TransferSpreadsheet is faster to code:

' Export a query to a new Excel file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "qryMonthlySales", "C:\Reports\Sales.xlsx", True

' Import from Excel
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
    "tblImportedData", "C:\Data\import.xlsx", True, "Sheet1$A1:F100"

TransferSpreadsheet does not support custom formatting, but it is perfect for quick data exports.

Combining with Email

After creating the Excel file, send it automatically:

' After creating the workbook:
Dim reportPath As String
reportPath = "C:\Reports\SalesReport_" & Format(Date, "yyyy-mm-dd") & ".xlsx"

' Send via Outlook
Dim olApp As New Outlook.Application
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)

With olMail
    .To = "[email protected]"
    .Subject = "Monthly Sales Report - " & Format(Date, "MMMM yyyy")
    .Body = "Please find the monthly sales report attached."
    .Attachments.Add reportPath
    .Send
End With

Conclusion

Excel automation from Access VBA is one of the most powerful productivity tools available. The combination of Access's data management capabilities and Excel's presentation and charting features — automated end-to-end with VBA — can replace hours of manual work with a single button click. Build these patterns into your reporting workflows and your stakeholders will receive polished, formatted reports automatically, on schedule, without any manual effort.