通过VBA将MS Access表导出到同一目录中的Excel电子表格

Sub ExportTableToExcel()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim fld As DAO.Field
    
    'Open the Access database
    Set db = OpenDatabase("C:\path\to\your\database.accdb")
    
    'Open the table in a recordset
    Set rs = db.OpenRecordset("SELECT * FROM YourTableName")
    
    'Create a new Excel workbook and worksheet
    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Add
    Set xlWS = xlWB.Worksheets(1)
    
    'Write the field names to the first row of the worksheet
    For Each fld In rs.Fields
        xlWS.Cells(1, fld.OrdinalPosition).Value = fld.Name
    Next fld
    
    'Write the recordset data to the worksheet
    xlWS.Range("A2").CopyFromRecordset rs
    
    'Save the workbook to the same directory as the database
    xlWB.SaveAs "C:\path\to\your\output.xlsx"
    
    'Close the workbook and quit Excel
    xlWB.Close
    xlApp.Quit
    
    'Release the objects from memory
    Set xlWS = Nothing
    Set xlWB = Nothing
    Set xlApp = Nothing
    Set fld = Nothing
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub