Exporting data from MS Access to Excel using VBA

Yes, there are many cases when the DoCmd.TransferSpreadsheet command is inadaquate.

The easiest way is to reference the Excel xx.x Object model within Access (Early Binding). Create and test your vba export function that way. Then once you are satisfied with your output, remove the Excel object model reference, then change your objects to use use Late Binding using CreateObject. This allows you to easily have other machines that are using different versions of Excel/Access to use it just the same.

Here is a quick example:

Sub ExportRecordsetToExcel(outputPath As String, rs As ADODB.Recordset)
'exports the past due report in correct formattig to the specified path

On Error GoTo handler:

    Const xlUP              As Long = -4162 'excel constants if used need to be referenced manually!
    Dim oExcel              As Object
    Dim oBook               As Object
    Dim oSheet              As Object
    Dim row                 As Long

    If rs.BOF And rs.EOF Then
        Exit Sub 'no data to write
    Else
        rs.MoveFirst
    End If

    row = 1

    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = False 'toggle for debugging

    Set oBook = oExcel.Workbooks.Add 'default workbook has 3 sheets

    'Add data to cells of the first worksheet in the new workbook.
    Set oSheet = oBook.worksheets(1)

    Do While rs.EOF = False

        oSheet.range("A" & row).value = rs.Fields("MyField").value

        'increase row
        row = row + 1
    Loop

    oBook.SaveAs (outputPath)

'tidy up, dont leave open excel process
    Set oSheet = Nothing
    Set oBook = Nothing
    oExcel.Quit
    Set oExcel = Nothing
Exit Sub
handler:

    'clean up all objects to not leave hanging processes
End Sub