One of my Insider Circle students, Nicole, has been using my technique for building dynamic SQL statements to populate her list boxes, like I show in my Access Search Seminar.
She has a great SQL statement all built with the right parameters for her search criteria, and her listbox shows the right records, but now she wants to export that set of records to Excel (for several reasons). Here's my solution:
OK, so you've built an SQL statement for your listbox to show exactly the results you want. Let's say:
MyListBox.RowSource = "SELECT ID, FirstName, LastName FROM CustomerT WHERE LastName=""Rost"""
Now what you want to do is export the results of that to an Excel spreadsheet.
Here's a simple solution: build a REPORT that has the same fields in it that your table has, laid out in a spreadsheet-like format (just as you'd want to see in Excel).
Now, open that report with a Docmd.OpenReport command and give it a WhereCondition equal to the WHERE clause of your SQL statement above:
DoCmd.OpenReport "MyReportR", acViewPreview, , "LastName=""Rost"""
Now, you can easily export that report to Excel under EXTERNAL DATA > Export > Excel
This isn't the BEST solution, but it works just fine and has a minimal amount of coding.