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 ?

Gord Thompson · Accepted Answer · 2013-11-08 11:20:09Z

4

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

answered Nov 8, 2013 at 11:20

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Over a year ago

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

Gord Thompson Over a year ago

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

ExcelUStudent · Accepted Answer · 2013-12-20 13:21:18Z

1

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

answered Dec 20, 2013 at 13:21

ExcelUStudent

114 bronze badges

Comments

4dmonster · Accepted Answer · 2013-11-08 11:15:23Z

0

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]")

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:

m_sdate = Me.sdate.Value
m_edate = Me.edate.Value

DoCmd.OutputTo ............

edited Nov 8, 2013 at 11:15

answered Nov 8, 2013 at 10:52

4dmonster

3,0411 gold badge16 silver badges25 bronze badges

1 Comment

Tom Over a year ago

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()

Collectives™ on Stack Overflow

Exporting a parameterized query to excel using vba

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related