Need to run a custom query and export it to excel?

[kee2ka4]

Hey Peeps,

I have a form in Microsoft access with some fields and checkboxes with a Search Button. On filling in the fields and ticking the check boxes, it creates a query on the fly and displays it as a report. This all works fine but I want to add an extra button on the form that would create the same query (based on what users click on the form) and then export it to excel. Can anyone guide me on how to do this?

Here is the code in VB for when the query runs and opens the results in a report:

    CurrentDb.QueryDefs("qryExamTake_Two").SQL = strSQL
     
    DoCmd.OpenReport "frmExamDetails", acViewPreview

How do I run the same query and export it to excel?

Thanks,
Ket

 

[boblarson]

You can use this code from my website to send a table or query to Excel. Put the code into a STANDARD module (not form or report) and then save the module with a name like basExcel and then call the function from your code:

CurrentDb.QueryDefs("qryExamTake_Two").SQL = strSQL
     
DoCmd.OpenReport "frmExamDetails", acViewPreview
Call SendTQ2Excel("qryExamTake_Two")

 

[giovaanni]

DoCmd.OutputTo acOutputQuery, "qryname", acFormatXLS, "filename", True