Exporting listbox values to Excel - can I use Docmd.TransferSpreadsheet?

[shabbaranks]

Hi all,

As the question states, Im trying to export the value of a listbox (which is populated from an sql statement). My current code is

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, [B][U]what goes here??,[/U][/B] "c:\File location\Export.xlsx"

As Im not exporting from a table but a listbox.

Thanks

 

[shabbaranks]

Found an easier solution

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