Export Listbox data

[Samantha]

I am using vba to redefine the rowsource of a listbox on the form as the user
selects specific criteria. My question is how can I export the results from a
list box to Excel? Has anyone done this before? Any pointers are very much
appreciated.

 

[Klatuu]

There are two possible ways to do this. One would be to use automation.
That would be to create an Excel object withing Access and write the data
directly to the spreadsheet. The other would be to use the List Box data to
create a recordset you could then export using TransferSpreadsheet.

 

[Samantha]

Hi Klatuu,
Both options sound intriquing. Can you elaborate a bit more on how I can do
this or point me to any reference that I look up?
This is my first time coming across your suggested first option.
For option 2, I'm not sure what to put as the tablename argument of
theTransferSpreadsheet. The way the form is setup, I have one command button
(cmd1) to reset ListBox rowsource via sql query and another command button
(cmd2) to export the results to Excel.

This is what I have so far (that does not work):
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Me!DataList, ,
True

Thanks for your help.

 

[Samantha]

I tried the following and it is able to create a query and export the
results, but now I don't know how to over-write the query created each time
or delete after the export. Can any one help?
Here's what I have so far:

Dim dbs As Database
Dim qdf As QueryDef
Dim varName As String, varSQL As String

Set dbs = CurrentDb

varName = "MyNewQuery"
varSQL = Me!DataList.RowSource
Set qdf = dbs.CreateQueryDef(varName, varSQL)

DoCmd.OutputTo acOutputQuery, "MyNewQuery", acFormatXLS, , False
Set qdf = Nothing
dbs.Close

Can anyone tell me how I can overwrite the query created or delete the query
created?

 

[Klatuu]

To delete a query in your case it would be:

dbs.QueryDefs.Delete qdf.Name