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

时间:2022-12-21 15:38:41

I've been trying with limited success to export a crosstab query result set to Excel using Access 2003. Occasionally, the export works correctly, and Excel shows with no errors. Other times, using the exact same query parameters, I get a 3190 error - too many fields. I am using the TransferSpreadsheet option in a macro that is called from VB code.

我一直在努力尝试使用Access 2003将交叉表查询结果集导出到Excel。有时,导出工作正常,Excel显示没有错误。其他时候,使用完全相同的查询参数,我得到3190错误 - 字段太多。我在从VB代码调用的宏中使用TransferSpreadsheet选项。

The macro has the following parameters: Transfer type: Export Spreadsheet type: Microsoft Excel 8-10 Table Name: (this is my query name) File Name: (Excel output file, which exists in the directory) Has Field Names: Yes

该宏具有以下参数:传输类型:导出电子表格类型:Microsoft Excel 8-10表名称:(这是我的查询名称)文件名:( Excel输出文件,存在于目录中)具有字段名称:是

The query should not produce any more than 14 columns worth of information, so the Excel 255 col limit should not be a problem. Also,the data in the database is not changing during the time I am querying, so the same query will produce the same result set.

查询不应产生超过14列的信息,因此Excel 255 col限制应该不是问题。此外,在我查询期间,数据库中的数据不会更改,因此相同的查询将生成相同的结果集。

One of the only solutions I have read on the net thus far is to close the recordset before running the macro, but this is hit or miss.

到目前为止,我在网上阅读的唯一解决方案之一是在运行宏之前关闭记录集,但这是命中或未命中。

Your thoughts/help are greatly appreciated!

非常感谢您的想法/帮助!

3 个解决方案

#1


1  

I've got one working as an MS Access Macro. It uses an OutputTo Action with:

我有一个工作作为MS Access宏。它使用OutputTo Action:

  • Object Type=Query
  • 对象类型=查询
  • Object Name=[WhateverQueryName]
  • 对象名称= [WhateverQueryName]
  • Output Format=MicrosoftExcel(*.xls)
  • 输出格式= MicrosoftExcel(*。xls)
  • Auto Start=No
  • 自动启动=否
  • (all the rest blank)
  • (其他所有空白)

I hate using Macros in MS Access (it feels unclean), but perhaps give that a try.

我讨厌在MS Access中使用宏(它感觉不干净),但也许尝试一下。

#2


1  

If you're willing to make use of a little vba rather than stick exclusively with macros, the following might help you. This module takes any sql you throw at it and exports it to a defined location in an excel worksheet. After the module are two examples of it's use, one to create a completely new workbook, one which opens an existing one. If you not confident with using SQL just create the query you want, save it and then supply "SELECT * FROM [YourQueryName]" to the Sub as the QueryString parameter.

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

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

Example 1:

例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

Example 2:

例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

Hope that's of some use to you.

希望对你有用。

#3


0  

A workaround would be to append the query to a table first and then export that.

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

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

TempTable-Make is a make-table query based on the crosstab.

TempTable-Make是基于交叉表的生成表查询。

Here is an appropriate ExportToExcel function you can use.

这是您可以使用的适当的ExportToExcel函数。

#1


1  

I've got one working as an MS Access Macro. It uses an OutputTo Action with:

我有一个工作作为MS Access宏。它使用OutputTo Action:

  • Object Type=Query
  • 对象类型=查询
  • Object Name=[WhateverQueryName]
  • 对象名称= [WhateverQueryName]
  • Output Format=MicrosoftExcel(*.xls)
  • 输出格式= MicrosoftExcel(*。xls)
  • Auto Start=No
  • 自动启动=否
  • (all the rest blank)
  • (其他所有空白)

I hate using Macros in MS Access (it feels unclean), but perhaps give that a try.

我讨厌在MS Access中使用宏(它感觉不干净),但也许尝试一下。

#2


1  

If you're willing to make use of a little vba rather than stick exclusively with macros, the following might help you. This module takes any sql you throw at it and exports it to a defined location in an excel worksheet. After the module are two examples of it's use, one to create a completely new workbook, one which opens an existing one. If you not confident with using SQL just create the query you want, save it and then supply "SELECT * FROM [YourQueryName]" to the Sub as the QueryString parameter.

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

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

Example 1:

例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

Example 2:

例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

Hope that's of some use to you.

希望对你有用。

#3


0  

A workaround would be to append the query to a table first and then export that.

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

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

TempTable-Make is a make-table query based on the crosstab.

TempTable-Make是基于交叉表的生成表查询。

Here is an appropriate ExportToExcel function you can use.

这是您可以使用的适当的ExportToExcel函数。