Screenshot of sample Access database

Exporting Access tables to Excel

There are pre-built Access functions to export data to Excel, such as the DoCmd.TransferSpreadsheet or DoCmd.OutputTo method, but anything but the most basic reports will require you to customise the Excel output more than these functions will allow. This code shows you how to create an instance of Excel and export the contents of the table into a new Excel workbook.  You can then format and manipulate the contents within Excel.

Public Sub ExportTableToExcel(strTableName As String)
     Dim oApp     As Object
     Dim xlSH     As Object
     Dim xlWB     As Object
     Dim rs       As Recordset
     Dim i        As Long

     'Check to see if Excel is open and get a handle to the Application
     On Error Resume Next
     Set oApp = GetObject(, "Excel.Application")
     On Error GoTo errorHandler
     'If oApp has not been set then there is no instance of Excel open.
     'Late binding the Excel application prevents issues with
     'compatibility as users may have different versions of Excel installed.
     If oApp Is Nothing Then Set oApp = CreateObject("Excel.Application")

     Set xlWB = oApp.Workbooks.Add
     Set xlSH = xlWB.Sheets(1)
 
     Set rs = CurrentDb.TableDefs(strTableName).OpenRecordset
 
    'Add in the header field names, the CopyFromRecordset command only
    'copies the data.
     For i = 0 To rs.Fields.Count - 1
          xlSH.Cells(1, i + 1).Value = rs.Fields(i).Name
     Next i

    'Paste in the data.
    xlSH.Range("A2").CopyFromRecordset rs

    'Format the worksheet
    With xlSH
        .Rows(1).Cells.Font.Bold = True
        .Columns.ColumnWidth = 100
        .Columns.AutoFit
        .Rows.AutoFit
    End With

exitRoutine:
    On Error Resume Next
    '-----------------------------------------------------------------------
    'When the Excel.Application object is created it is hidden by default.
    'You need to make sure it is set to visible as if there are errors or the
    'User runs the macro a number of times, they can end up with multiple hidden
    'Instances of Excel running in the background, which will only show up when
    'They are force quit.
    '------------------------------------------------------------------------
    oApp.Visible = True

    'Clear object variables and make sure recordset is closed
    rs.Close
    Set rs = Nothing
    Set xlSH = Nothing
    Set xlWB = Nothing
    Set oApp = Nothing
Exit Sub
errorHandler:
    MsgBox ("An error occured: " & Err.Number & " " + Err.Description), vbCritical, "Error"
    Err.Clear
    Resume exitRoutine
    Resume
End Sub

Download Sample Access database with code to export tables to Excel

Leave a Reply