Export Filtered ListBox output to a Excel Sheet

[gokul1242]

Hi Currently i have a form Which has three combo box .then i have a search button which runs a query based on the values selected in the three combo boxes and shows the results in a list box below.see the image for the form here.

Now i want to add a button called "Export to excel" which will export the list box results to a Ms-excel sheet.how to go about doing this in vba. i have gone through threads related to this same issue but unfortunately none of them works ....kindly help

[June7]

I had a similar issue. My solution is VBA code to construct an SQL and uses QueryDefs collection to modify an Access query object then export that query. I do that method because the criteria is more complex.

Does your form have Access query object as RecordSource or is the RecordSource an sql statement? Is your filtering done by parameters in query that refer to comboboxes for input? I expect that a query object with parameters should work in your case. Do you want to export to a new or existing worksheet? Are you using TransferSpreadsheet method?

[gokul1242]

I had a similar issue. My solution is VBA code to construct an SQL and uses QueryDefs collection to modify an Access query object then export that query. I do that method because the criteria is more complex.

Does your form have Access query object as RecordSource or is the RecordSource an sql statement? Is your filtering done by parameters in query that refer to comboboxes for input? I expect that a query object with parameters should work in your case. Do you want to export to a new or existing worksheet? Are you using TransferSpreadsheet method?

Hi my filtering is done by parameters in query that refers to the combo box input only. And i am exporting the data to a new worksheet everytime.

[June7]

So what is not working? Do you have code? Why doesn't it work - error message, wrong results, nothing happens? Post code for analysis.

[rpeare]

Save the query you are using as for the list box as a database object (an actual query) then just use the transferspreadsheet function to export that query. You don't need to export the list box (though it can be done) it'd be a little painful and way more time consuming than just exporting a query with the exact same setup as the rowsoure of the listbox. Then if you want to export to a differently named file each time you can use the parameters of your search to create it for instance

dim sExportFilename as string


sExportfilename = "c:\test\Exported_" & [SkillsArea] & "-" & [Skill] & ".xls"

so your export name would end up being: Exported_Multi Language Skill_French.xls

which you could then stick into your transferspreadsheet statement.