How can I interact with other Office applications (Excel) using VBA in Access?

Need to write your Access data or query to an Excel file? Here is the how to do it:

Most people are familiar with Excel and know how to use it well (enough), and when you start talking about
Access, they get scared off, and don’t know what to do anymore.

Well, here you are going to find out how write your data to an Excel file and then you could send it out
and then import the results back into your database.

Here is the video on how to write your Access table data to an Excel file:

Our data will be from the “Customers” table, but you could also create a query on multiple tables:

This form only has one button called “Export Customers” with this “Click” event (What happens when you click it).

frmExportCustomer

Private Sub btnExport_Click()
    'MsgBox "Click"
    Dim blnResult As Boolean
    
    blnResult = ExcelExport()
    
    If blnResult = True Then
        MsgBox "Exported"
    Else
        MsgBox "Export Failed"
    End If
    
End Sub

basExcel

Function ExcelExport() As Boolean
    Dim rst As Recordset
    Dim lngRow As Long
    Dim strSQL As String, strTemplate As String

    Dim objExcelApp As Excel.Application
    Dim objExcelBook As Excel.Workbook
    Dim objExcelSheet As Excel.Worksheet
    
    On Error GoTo errhandler
    
    DoCmd.Hourglass True
    
    Set rst = CurrentDb.OpenRecordset("SELECT [CustomerID],[CompanyName] FROM Customers")
    
    If Not rst.EOF Then
        Set objExcelApp = New Excel.Application
        strTemplate = CurrentProject.Path & "\file.xlsx"
        
        Set objExcelBook = objExcelApp.Workbooks.Add(strTemplate)
        Set objExcelSheet = objExcelBook.Worksheets("Sheet1")
        
        objExcelApp.Visible = True
        
        lngRow = 2
        
        Do Until rst.EOF
            objExcelSheet.Range("A" & lngRow) = rst.Fields("CustomerID")
            objExcelSheet.Range("B" & lngRow) = rst.Fields("CompanyName")
            
            lngRow = lngRow + 1
            rst.MoveNext
        Loop
        
        'clean up
        rst.Close
        Set rst = Nothing
        
        Set objExcelApp = Nothing
        Set objExcelBook = Nothing
        Set objExcelSheet = Nothing
        
        DoCmd.Hourglass False
        
        ExcelExport = True
    Else
        MsgBox "No Records"
        ExcelExport = False
    End If
    
    
Exit Function
errhandler:
    Select Case Err.Number
        Case "1004"
            MsgBox "We couldn't find your file.xlsx file. Put it in the same folder as this database.", vbExclamation, "ExcelExport"
            DoCmd.Hourglass False
        Case Else
            MsgBox Err.Number & " " & Err.Description, vbExclamation, "ExcelExport"
            DoCmd.Hourglass False
    
    End Select
End Function

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!