Filtering a Query then Exporting to Excel using VB

[alan_mitchell]

Hi,

I have a form with some combo boxes / check boxes and a 'generate report'
button for the user to generate a report based on the criteria they specify.

Using the DoCmd.OpenReport function and a WHERE condition, it successfully
opens the report for criteria the user selects.

So if the user selects the staff member as 'Joe.Bloggs' the report will only
show records for Joe.Bloggs.

I would now like to take this to the next level and give the user an option
to export the data to Excel. As far as I know, it is not possible to export a
report to Excel, so I think I will have to export the report's query.

In summary, I have a query and would like to filter it based on the forms
selections, then export to Excel. What's the best way to do this using VB?

Unlike DoCmd.OpenReport, DoCmd.OpenQuery doesn't have a WHERE condition so
I'm not sure If it can be filtered. I've tried DoCmd.ApplyFilter and
DoCmd.OutputTo but can't get any of them to work.

Any ideas how I should approach this?

Cheers,
Alan

 

[Bilbo_Baggins_Esq]

Look up TransferSpreadsheet for all the syntax:

DoCmd.TransferSpreadsheet acExport, 8, "Query_or_Table_Name", Output_Path_and_File, False, ""


This will export a query, but you'll need to remember that the underlying query is unfiltered since the user is applying the filter through the form.
If you want to export the filtered result, you'll need to write another query which uses the option the user selected in the form as a criteria.

In the criteria:
forms![your_form]![Filter_control]

 

[Bilbo_Baggins_Esq]

Also, be advised that TransferSpreadsheet also exports the column headings.

You are correct (in general) that you cannot directly export a report anymore.
To a small extent, it is determined by which version of Access you're using, and or your update status.

Other than exporting a report, you can still "build" a report in excel, but you have to do a lot of control and formatting code within Access.