exporting data from listbox 1

[Mary01]

Hi everybody,

I have a search form that produces the results of the search in a listbox. My customers want to be able to
export the data from the listbox to an excel file. I'm new to access but this job's been assigned to me. Does anybody
know how to do this or at least how I can change the property of the listbox so I can copy the content and paste it in a excel file?

thanks in advance

 

[Remou]

Hi
If you save the rowsource of the listbox as a query, you can use transferspreadsheet.

 

[randy700]

Two possibilities....

1. In code:
DoCmd.TransferSpreadsheet

2. From the menu bar:
Tools|Office Links|Analyze it with Microsoft Excel


Randy

 

[Mary01]

Thank you guys for both responses. I didn't got a new
assignment and didn't get a chance to try your tips until
this morning. I tried this

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "MyTableName", "C:\FileNameIs.xls", True
and it works but since I don't want to transfer the whole table and only the search results, I can't use the table name there and I tried using my listbox name like this

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "Me.Results.RowSource", "C:\FileNameIs.xls", True
I also tried Me.Results.RowSource=myquery
and I tried every thing with and without double quotes and It didn't work.

Any suggestions?
Thank you

 

[Mary01]

I should mention something here, when I say myquery, I mean a sql that I have built up in the module, not a
separate object, which I think is probably the problem because I keep getting an error that tells me Object "myquery" is not found.

Thanks again

 

[randy700]

You're correct -- you need an object to export.

How about something like....

Private Sub ExportResults()
Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim strSQL As String
On Error GoTo ExportError
FixedError:
strSQL = "SELECT fields FROM YourTable"
Set db = CurrentDb
Set qDef = db.CreateQueryDef("YourQueryName", strSQL)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "YourQueryName", "YourPath"
Set qDef = Nothing
Set db = Nothing
ExportError:
If Err.Number = 3012 Then
DoCmd.DeleteObject acQuery, "YourQueryName"
Resume FixedError
Else
MsgBox Err.Number & " " & Err.Description
End If
End Sub


Randy

 

[Mary01]

Thank you so much Randy, it worked!

 

[Mary01]

what happens if I don't set qDef and db to Nothing?