使用ACCESS的VBA创建SQL并导出excel

可以使用以下代码来实现：

Sub ExportDataToSQL()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim i As Integer, j As Integer
    
    '连接数据库
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\mydatabase.accdb;"
    conn.Open
    
    '构建SQL语句
    strSQL = "SELECT * FROM mytable"
    
    '执行SQL语句
    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn
    
    '将结果导出到Excel
    Dim xlApp As Excel.Application
    Dim xlWorkbook As Excel.Workbook
    Dim xlWorksheet As Excel.Worksheet
    Set xlApp = New Excel.Application
    Set xlWorkbook = xlApp.Workbooks.Add
    Set xlWorksheet = xlWorkbook.Worksheets(1)
    
    '写入表头
    For j = 0 To rs.Fields.Count - 1
        xlWorksheet.Cells(1, j + 1).Value = rs.Fields(j).Name
    Next j
    
    '写入数据
    i = 2
    While Not rs.EOF
        For j = 0 To rs.Fields.Count - 1
            xlWorksheet.Cells(i, j + 1).Value = rs.Fields(j).Value
        Next j
        i = i + 1
        rs.MoveNext
    Wend
    
    '保存Excel文件
    xlWorkbook.SaveAs "C:\mydata.xlsx"
    
    '关闭Excel和数据库连接
    xlApp.Quit
    Set xlWorksheet = Nothing
    Set xlWorkbook = Nothing
    Set xlApp = Nothing
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

在这个例子中，我们首先连接到一个名为 `mydatabase.accdb` 的Access数据库，然后执行一个简单的SQL查询，并将结果导出到一个名为 `mydata.xlsx` 的Excel文件中。请注意，在使用此代码之前，您需要在项目引用中添加对 `Microsoft ActiveX Data Objects` 的引用。