Export Variable Query Definition to Excel (1 Viewer)

[epicmove]

Hi guys,

I have a list box with rowsource "QryListBox"

I have SortBy option buttons on my form that change the list box's rowsource depending on the selection made i.e.

strSQL = "SELECT " & Me.List.Rowsource & ".* FROM " & Me.ListBox.Rowsource " & " ORDER BY " & strOrderBy 'where strOrderBy is the field I wish to order my list box by

This works great and gives the user a nice dynamic sort function.

Of course this isnt enough. Rather than printing a report they would like to define their criteria (i.e. use the normal search form) and then click a button to create an Excel Document.

I have looked at both the Transfer Text and Output to Functions but these only seem to work on fixed database objects such as the original query.

Can anyone push me in the right direction for creating a module that takes the list boxes current rowsource (query def) and exports that as an Excel document.
I know it is possible but havent done a lot with DAO (if thats what i need?)

Thanks
Ben

 

[namliam]

You could make a 'fixed' query and export that...
Currentdb.querydefs("DefaultQueryName").sql = strSQL

Then export this defaultqueryname using the Docmd.transfer text command.

There is another way but that is WAY more complex...

 

[Matt Greatorex]

Additional Question

I think this post is the closest to what I need, so I'm hoping someone can elaborate a little. I'm not not sure if this method is what I should actually be using.

I have a number of saved queries. At present, the users view reports as snapshots, each one created based on the data from one of these queries. The users now want the option to view the results either as a snapshot or an Excel file. Exporting the base query to Excel is okay, but I'm having trouble figuring out how to filter results, when doing this. For example, a user can currently choose an area of the country and a date range and I can apply these as filters to the report, as it gets opened.

It's not an option to export all data and allow the user to filter it within Excel, as various info is confidential and exclusive to the area it comes from.

Is such a thing possible when exporting to Excel? If not, does anyone have a suggestion as to an easy way to effectively add a 'where' clause to an existing query and export the results to Excel?

Thanks.

 

[namliam]

Cannot you export the report??

You can also make a query called something like qryReport. In this query using above method you can add all kinds of stuff in the where what ever you want to do...