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

 

[Ken Snell \(MVP\)]

See if either of these two articles help:

Create a query and export multiple "filtered" versions of the query (based
on data in another table) to separate EXCEL files via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#FilterExportSepFiles


Create a query and export multiple "filtered" versions of the query (based
on data in another table) to separate worksheets within one EXCEL file via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#FilterExportSameFile