Filtering a Query then Exporting to Excel using VB

[alan_mitchell]

Hi,

I originally posted this in the 'Import/Export Data' forum but thought it
would be more relevant in here since the main part of my question involves
queries.

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

 

[Marshall Barton]

I originally posted this in the 'Import/Export Data' forum but thought it
would be more relevant in here since the main part of my question involves
queries.

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?

I don't know about "best" way, but you can modify a saved
query's SQL property. If the saved query already exists
whit some kind of where clause, the code might look like:

Set db = CurrentDb()
With db.QueryDefs![name of query]
strSQL = Left(.SQL, InStr(.SQL, "Where") - 1)
.SQL = strSQL & "WHERE " & strWhere
Debug.Print .SQL
End With
'DoCmd.OpenQuery "name of query"
DoCmd.TransferSpreadsheet acExport, , "name of query", . . .