Exporting to Excel - One listbox per sheet

[shabbaranks]

Hi,

Im looking to export my listbox values to a single workbook BUT a new sheet is created per export. There are 6 listboxes in total and I already have the code to export a single listbox but if I try to use this code its going to overwrite the csv each time - I was wondering if anyone could help please?

Dim i As Integer
    Dim n As Integer
    Dim strLine As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("D:\DatabaseHome\ExportCombinedHrs.csv", True)
    For i = 0 To Me.CombinedHrsAdminNC_lb.ListCount - 1
    
        For n = 0 To Me.CombinedHrsAdminNC_lb.ColumnCount - 1
        
            strLine = strLine & """" & Me.CombinedHrsAdminNC_lb.Column(n, i) & ""","
            
        Next n
        
        strLine = Left(strLine, Len(strLine) - 1)
        
        a.writeline (strLine)
        
        strLine = ""
        
    Next i
    
    MsgBox "Your export file is located in D:\DatabaseHome"

 

[namliam]

If you want to use the excel functionality of sheets in excel, then you need to export to an excel file not a CSV. A CSV is a text file that cant handle the sheets idea in any way shape or form....

You are writing the files line by line, which is ill adviced unless you need something very particular....
Look up and into :
docmd.TransferText
docmd.TransferSpreadsheet

 

[shabbaranks]

If you want to use the excel functionality of sheets in excel, then you need to export to an excel file not a CSV. A CSV is a text file that cant handle the sheets idea in any way shape or form....

You are writing the files line by line, which is ill adviced unless you need something very particular....
Look up and into :
docmd.TransferText
docmd.TransferSpreadsheet

Thanks looking at the docmd.TransferSpreadsheet can I use it against a listbox as the only examples I can find seem to be table based?

Thanks

 

[namliam]

Presumably you will want to fill the worksheet with some data? wont you??

 

[shabbaranks]

With the listbox data yes rather than data which is stored within a table - am I assuming correctly that the expression example below "TableName" refers to an actual table rather than a data source in my case a list box?

.TransferSpreadsheet(TransferType, SpreadsheetType, TableName,

Thanks again

 

[shabbaranks]

Converted it to a query instead (just in case the listbox was going to cause an issue). If I try to execute this I get type mismatch on the qdf.name - any ideas?

Dim strSQL As String
Dim qdf As DAO.QueryDef
strSQL = "SELECT NoneChargeable_Admin.EmployeeName, Sum(([TotalHours])) AS [Combined Hours], MonthName([Date(Month)]) AS [Month], NoneChargeable_Admin.[Date(Year)] " & vbCrLf & _
"FROM NoneChargeable_Admin " & vbCrLf & _
"GROUP BY NoneChargeable_Admin.EmployeeName, MonthName([Date(Month)]), NoneChargeable_Admin.[Date(Year)];"
Set qdf = CurrentDb.CreateQueryDef("tmpQdf", strSQL)
CurrentDb.QueryDefs.Append (qdf.Name)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, qdf.Name, "D:\DatabaseHome\Export.xlsx", True
CurrentDb.QueryDefs.Delete (qdf.Name)

 

[namliam]

Set qdf = CurrentDb.CreateQueryDef("tmpQdf", strSQL)
Already creates the query, you do need the .append to do so.