使用vba将参数化查询导出为ex​​cel

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 sdate and 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 ?

但不幸的是它不起作用。在导出之前如何将参数放入我的查询中？

3 个解决方案

#1

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

#2

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

#3

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 ............