Export to Excel

[djwatts]

Hello,

I need to find the best way to export a selection of data to excel. Using the docmd.transferspreadsheet command doesn't seem to be an option as I need to export filtered results.

At the moment this data can be viewed on screen using a form in data sheet view. This is important as I can apply the Where condition at runtime as the user builds up the criteria from several options.

Is there an easy way to export this dataset? Would the best option be to use the Analyse with Excel command? Anyone know how to call this from VBA???

Thanks

 

[M8KWR]

Could you build a Query based around the selections that the user uses on the form, and then export the Query....

IF you are able to apply a filter on the form you should be able to replicate this in a query!!!!

 

[djwatts]

Sure, I was thinking somewhere along those lines. What would be the best way to build an SQL query at run-time and save it so it can be exported?

Thanks for your help

 

[M8KWR]

what i would do is to create a query based on selection on the form....

For example if you have a field to display all Customer, so you may have a simple combo box displaying either Yes or No....

In the query under the customer field in the criteria, you would need to enter something like [forms]![NAME OF FORM]![NAME OF FIELD]

then you would keep on doing this for all the option the users can change on the form, this way you can cover all possibilities and future requirements.

Without knowing exactly what your form looks like and the corect field name, its hard for me to give you an exact solution.

 

[djwatts]

Thanks for your help M8KWR, I really am restricted to generating the SQL clause on the fly as it is particularly complex. With a huge selection of fields for people to filter on a number of tables.

The quick n easy solution for me was to keep it using a datasheet form and running the

DoCmd.RunCommand acCmdOutputToExcel

which seems to be working fine.