使用 vba 将参数化查询导出到 excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19856990/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exporting a parameterized query to excel using vba
提问by Tom
I want to export the results of my query to an excel file by clicking on a button in a form.
我想通过单击表单中的按钮将查询结果导出到 excel 文件。
For this I used this code and it works well:
为此，我使用了这段代码，它运行良好：
Private Sub Command9_Click()
On Error GoTo ProcError
DoCmd.OutputTo _
ObjectType:=acOutputQuery, _
ObjectName:="Contract Type Billing", _
OutputFormat:=acFormatXLSX, _
Autostart:=True
ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2501 'User clicked on Cancel
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
"Error in cmdExportQuery_Click event procedure..."
End Select
Resume ExitProc
End Sub
But my query uses 2 parameters sdateand edate, I don't want access to ask me for these value but I want the user to enter them in the form with the appropriate textboxes.
但是我的查询使用了 2 个参数sdate和edate，我不想访问向我询问这些值，但我希望用户在带有适当文本框的表单中输入它们。
So I added this bit to the code before DoCMD.OutputTo
所以我在 DoCMD.OutputTo 之前的代码中添加了这一点
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("Contract Type Billing")
qdf.Parameters("sdate") = sdate.Value
qdf.Parameters("edate") = edate.Value
But unfortunately it doesn't work. How can put the parameters into my query before I export it ?
但不幸的是它不起作用。在导出之前如何将参数放入我的查询中？
采纳答案by Gord Thompson
If you wanted to keep your original parameter query intact you could create a temporary QueryDef to dump the data into a temporary table, and then output the temporary table to Excel:
如果您想保持原始参数查询不变，您可以创建一个临时 QueryDef 将数据转储到临时表中，然后将临时表输出到 Excel：
Dim cdb As DAO.Database, qdf As DAO.QueryDef
Const tempTableName = "_tempTbl"
Set cdb = CurrentDb
On Error Resume Next
DoCmd.DeleteObject acTable, tempTableName
On Error GoTo 0
Set qdf = cdb.CreateQueryDef("")
qdf.SQL = "SELECT * INTO [" & tempTableName & "] FROM [Contract Type Billing]"
qdf.Parameters("sdate").Value = DateSerial(2013, 1, 3) ' test data
qdf.Parameters("edate").Value = DateSerial(2013, 1, 5)
qdf.Execute
Set qdf = Nothing
Set cdb = Nothing
DoCmd.OutputTo acOutputTable, tempTableName, acFormatXLSX, "C:\__tmp\foo.xlsx", True
回答by ExcelUStudent
I've bump into same problem and instead of using parameters i'd rather insert WHERE criteria in the sql script and export the query result into excel directly (off course you'll have to define a target file). Assuming that date field in Contract Type Billing named dDate.
我遇到了同样的问题，而不是使用参数，我宁愿在 sql 脚本中插入 WHERE 条件并将查询结果直接导出到 excel（当然你必须定义一个目标文件）。假设合同类型计费中的日期字段名为 dDate。
Set qdf = CurrentDb.CreateQueryDef("qTempQuery")
qdf.SQL = "SELECT * FROM [Contract Type Billing] WHERE ((([Contract Type Billing].dDate)>#" _
& cdate(sdate.value) & "# And ([Contract Type Billing].dDate)<#" & cdate(edate.value) & "#));"
DoCmd.OutputTo acOutputQuery, "qTempQuery", "ExcelWorkbook(*.xlsx)", OutputFileName, False
DoCmd.DeleteObject acQuery, "qTempQuery"
Set qdf = Nothing
回答by 4dmonster
One way is: Assume form name is [MyForm], and textboxes are still [sdate] and [edate], then remove PARAMETERS section if exists in your query. Replace [sdate] and [edate] in your query to eval("Forms![MyForm]![sdate]") and eval("Forms![MyForm]![edate]")
一种方法是：假设表单名称是 [MyForm]，并且文本框仍然是 [sdate] 和 [edate]，然后删除 PARAMETERS 部分（如果存在于您的查询中）。将查询中的 [sdate] 和 [edate] 替换为 eval("Forms![MyForm]![sdate]") 和 eval("Forms![MyForm]![edate]")
The other way is to create Public functions in module:
另一种方法是在模块中创建公共函数：
Global m_sdate as date
Global m_edate as date
Public Function sdate() as date
sdate = m_sdate
end function
Public Function edate() as date
edate = m_edate
end function
Replace [sdate] and [edate] in your query to calls sdate() and edate(). And add assigment before export:
将查询中的 [sdate] 和 [edate] 替换为调用 sdate() 和 edate()。并在导出前添加赋值：
m_sdate = Me.sdate.Value
m_edate = Me.edate.Value
DoCmd.OutputTo ............
