Export listbox to Excel - listbox shows null value in immediate window

[shabbaranks]

Hi all,



Im trying to export the results of a VBA query which populates a listbox to Excel. If I use DoCmd.TransferSpreadsheet acExport I get the error invalid use of null but I can see there is data in the listbox. Any ideas please - code below:

Private Sub ExportTotal_btn_Click()
Dim strSQL As String
Dim strQry As String
Dim sQryOutput As String
sQryOutput = Me.TotalHrs_Listbox.Value
strSQL = "SELECT * FROM sQryOutput"
strQry = "TempQueryName"
 
Set db = CurrentDb
Set qdf = db.CreateQueryDef(strQry, strSQL)
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
   strQry, "D:\Folder\output.xlsx", True
 
DoCmd.DeleteObject acQuery, strQry
End Sub

Thanks

[kevinnice]

I'm no expert here Nick but you can't use a variable as a table name without the correct syntax:
'" & sQryOutput & "'
Didnt try it, but hope that helps...
The big brains here will surely give the definitive answer...
best of luck

[June7]

Is this a multi-select listbox and you are trying to export the selected items?

I don't think can reference a listbox as datasource for a query. Queries are to manipulate data of tables and other queries, not data controls on form. The items selected can be used as criteria in an sql statement. This article shows are to build the WHERE clause from selected items http://allenbrowne.com/ser-50.html

[shabbaranks]

Got it working with

Private Sub Exprt2Excel_btn_Click()
    Dim i As Integer
    Dim n As Integer
    Dim strLine As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("Export_location\Export.csv", True)
    For i = 0 To Me.Control_Listbox.ListCount - 1
    
        For n = 0 To Me.Control_Listbox.ColumnCount - 1
        
            strLine = strLine & """" & Me.Control_Listbox.Column(n, i) & ""","
            
        Next n
        
        strLine = Left(strLine, Len(strLine) - 1)
        
        a.writeline (strLine)
        
        strLine = ""
        
    Next i
    
    MsgBox "Your file is exported"
End Sub

Thanks for all your help