Trying to export listbox result to excel

[Mikeauz]

Hi,

I'm trying to export a listbox to excel which is populated from a search screen, (Users select their criteria and the sql is built from that and then applied as the rowsource of the listbox) so I'm grabbing that same sql and applying it to a query which I am then exporting to excel and I'm getting the error "Too many fields defined"

This is kinda weird as my linked table only has about 15 fields in it. (Tried refreshing linked table and deleting/recreating it, to no avail.)

In trying to find the problem I ran the code in debug and found that this error happen every time the sql had more than one field in the select statement.

The SQL for the listbox and the query (If the user doesn't select any criteria) is:

SELECT * FROM tblDispute WHERE 1 = 1

and my code for the excel export is:
(ExportType is the sql from the search screen as two seperate search screens use this code)

Public Function ExcelExport(ExportType As String)
On Error GoTo errHandler
Dim strSaveFileName, stAppName As String
Dim db As Variant

Set db = CurrentDb
db.QueryDefs("QrylstExport").sql = ExportType
db.Close

stAppName = "C:\Documents and Settings\" & Environ("username") & "\Desktop\" & "CMDExport.xls"

DoCmd.TransferSpreadsheet acExport, , "QrylstExport", stAppName, True
Application.FollowHyperlink stAppName

Exit Function
errHandler:
If Err.Description = "Data Type Conversion Error." Then
MsgBox "There are no Search Results", vbInformation, "Validation error"
Exit Function
End If
MsgBox Err.Description
End Function

Thanks for anhy help

 

[bitmite]

you use a query to populate the list box right.
So use that same query to expot to excel.

the easiest way to do this is by useing the following:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, [b]SQLSTATEMENT[/b],[B]FULL PATH FILENAME[/B], True

this line will transfer the query or sqlstatement to excel.


Brought to you By Nedaineum
|
|
The port to Geektron