使用vba将参数化查询导出为excel(Exporting a parameterized query to excel using vba)

使用vba将参数化查询导出为excel(Exporting a parameterized query to excel using vba)

我想通过单击表单中的按钮将查询结果导出到excel文件。

为此，我使用了这段代码，效果很好：

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

但我的查询使用2参数sdate和edate ，我不希望访问问我这些值，但我希望用户在表单中输入相应的文本框。

所以我在DoCMD.OutputTo之前将这一位添加到代码中

Dim qdf As DAO.QueryDef Set qdf = CurrentDb.QueryDefs("Contract Type Billing") qdf.Parameters("sdate") = sdate.Value qdf.Parameters("edate") = edate.Value

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

I want to export the results of my query to an excel file by clicking on a button in a form.

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.

So I added this bit to the code before 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 ?

最满意答案

如果要保持原始参数查询的完整性，可以创建一个临时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
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:
 
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