将DAO QueryDef保存到临时查询并将结果输出到Excel

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.CreateQueryDef("tempQuery", "SELECT * FROM tableName")

Dim rs As DAO.Recordset

Set rs = qdf.OpenRecordset()

Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook

Set xlApp = New Excel.Application
Set xlWorkbook = xlApp.Workbooks.Add

Dim xlWorksheet As Excel.Worksheet
Dim rowNum As Long
Dim colNum As Long

Set xlWorksheet = xlWorkbook.Sheets.Add
rowNum = 1
colNum = 1

' 写入字段名称
For Each fld In rs.Fields
    xlWorksheet.Cells(rowNum, colNum).Value = fld.Name
    colNum = colNum + 1
Next fld

' 写入查询结果
rowNum = rowNum + 1
rs.MoveFirst

Do Until rs.EOF
    colNum = 1
    For Each fld In rs.Fields
        xlWorksheet.Cells(rowNum, colNum).Value = fld.Value
        colNum = colNum + 1
    Next fld
    rowNum = rowNum + 1
    rs.MoveNext
Loop

xlWorkbook.SaveAs "output.xlsx"
xlWorkbook.Close
xlApp.Quit

Set xlWorksheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing