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函数。