从MS Access中将交叉表查询结果导出到Excel

我有一个用作MS-Access宏。 它使用OutputTo操作:

• 对象名=[WhateverQueryName]
• 输出格式=MicrosoftExcel(*xls)
• (其余全部为空)

我讨厌在MS-Access中使用宏(感觉不干净),但也许可以试试。

如果您愿意使用一点vba而不是只使用宏,下面的内容可能会对您有所帮助。此模块接受向其抛出的任何sql,并将其导出到excel工作表中定义的位置。该模块是使用它的两个例子,一个创建一个全新的工作簿,一个打开现有的工作簿。如果您对使用SQL没有信心,只需创建所需的查询,保存它,然后将“SELECT*FROM[YourQueryName]”作为QueryString参数提供给子查询。

Sub OutputQuery(ws As excel.Worksheet, CellRef As String, QueryString As String, Optional Transpose As Boolean = False)

    Dim q As New ADODB.Recordset
    Dim i, j As Integer

    i = 1

    q.Open QueryString, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly


    If Transpose Then
        For j = 0 To q.Fields.Count - 1
            ws.Range(CellRef).Offset(j, 0).Value = q(j).Name
            If InStr(1, q(j).Name, "Date") > 0 Or InStr(1, q(j).Name, "DOB") > 0 Then
                ws.Range(CellRef).Offset(j, 0).EntireRow.NumberFormat = "dd/mm/yyyy"
            End If
        Next

        Do Until q.EOF
            For j = 0 To q.Fields.Count - 1
                ws.Range(CellRef).Offset(j, i).Value = q(j)
            Next
            i = i + 1
            q.MoveNext
        Loop
    Else
        For j = 0 To q.Fields.Count - 1
            ws.Range(CellRef).Offset(0, j).Value = q(j).Name
            If InStr(1, q(j).Name, "Date") > 0 Or InStr(1, q(j).Name, "DOB") > 0 Then
                ws.Range(CellRef).Offset(0, j).EntireColumn.NumberFormat = "dd/mm/yyyy"
            End If
        Next

        Do Until q.EOF
            For j = 0 To q.Fields.Count - 1
                ws.Range(CellRef).Offset(i, j).Value = q(j)
            Next
            i = i + 1
            q.MoveNext
        Loop
    End If

    q.Close

End Sub

例1:

Sub Example1()
    Dim ex As excel.Application
    Dim wb As excel.Workbook
    Dim ws As excel.Worksheet

    'Create workbook
    Set ex = CreateObject("Excel.Application")
    ex.Visible = True
    Set wb = ex.Workbooks.Add
    Set ws = wb.Sheets(1)

    OutputQuery ws, "A1", "Select * From [TestQuery]"
End Sub

例2:

Sub Example2()
    Dim ex As excel.Application
    Dim wb As excel.Workbook
    Dim ws As excel.Worksheet

    'Create workbook
    Set ex = CreateObject("Excel.Application")
    ex.Visible = True
    Set wb = ex.Workbooks.Open("H:\Book1.xls")
    Set ws = wb.Sheets("DataSheet")

    OutputQuery ws, "E11", "Select * From [TestQuery]"
End Sub

解决方法是先将查询追加到表中,然后导出该表。

DoCmd.SetWarnings False
 DoCmd.OpenQuery "TempTable-Make" 
 DoCmd.RunSQL "DROP TABLE TempTable" 
 ExportToExcel()
DoCmd.SetWarnings True

transportable Make是一个基于交叉表的Make表查询。

Here

以下代码使用excel中的函数导出查询,该函数专门用于导入记录集 CopyFromRecordset 。注意,需要添加字段名,因为此函数只获取实际数据。这段代码甚至可以用于交叉表查询。

'---------------------------------------------------------------------------------------
' Method : MoveQueryToWorksheet
' Author : ROLU
' Date   : 09.05.2018
' Purpose: Moves queries to specific worksheet in an Excel Workbook
'---------------------------------------------------------------------------------------
Function MoveQueryToWorksheet(wkb As Excel.Workbook, wks As Variant, strSQL As Variant) As Boolean
On Error GoTo MoveQueryToWorksheet_Error

'Dim rs As New ADODB.Recordset
'rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim rs
Set rs = dbs.OpenRecordset(strSQL)

Dim lCol As Long
For lCol = 0 To rs.Fields.Count - 1
    wkb.Worksheets(wks).Cells(1, lCol + 1).Value = rs.Fields(lCol).Name
Next lCol
wkb.Worksheets(wks).Range("A2").CopyFromRecordset rs

'Close out and clean
Set rs = Nothing
MoveQueryToWorksheet = True

    Exit Function

MoveQueryToWorksheet_Error:
On Error GoTo 0
Set rs = Nothing
MoveQueryToWorksheet = False

End Function