Exporting a parameterized query to excel using vba

Question

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 ?

Solution 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:

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

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

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

Comments

[Tom]

If I delete my Parameters section, I won't be able to run my query in other forms no ? I tried the second option, but it does a Compile error when it runs my query. It doesn't seem to recognize sdate() and edate()

[Tom]

Works Great. But I thought the .Execute command was only applicable to Update, Add and Delete queries.

[Gord Thompson]

@Tom SELECT ... INTO is also an action query. In the Access application UI is it referred to as a "make-table query".