Exporting listbox values from mc access form to excel file maintaining the same number of columns

Question

So I managed to create a code to copy and paste listbox values to a newly created excel file. The thing is, I have it all concatenated and separated by a comma. It works fine but because of how it is exported, then I have to use Excel text to columns functionality to put the data like I want.

Here's the code:

Private Sub button_Export_AMAT_Click()

Dim i As Integer
Dim n As Integer

Dim strLine As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("\\yvavm301\Commun\CF_GDC\base de dados a trabalhar\AM_AT.csv", True)

    For i = 0 To Me.List_AM_AT.ListCount - 1

            For n = 0 To Me.List_AM_AT.ColumnCount - 1

             strLine = strLine & """" & Me.List_AM_AT.Column(n, i) & ""","
           
            Next n

        strLine = Left(strLine, Len(strLine) - 1)
        a.writeline (strLine)
        strLine = ""

    Next i

    MsgBox "Your file is exported"
    
End Sub

My question is: is it possible to export a like for like table, ie. having the same number of columns and having them populated with right values?

The change has to be made here (see below), right?

strLine = strLine & """" & Me.List_AM_AT.Column(n, i) & ""","
a.writeline (strLine)

I've tried without luck the following:

strLine = Me.List_AM_AT.Column(n, i)    
a.cells(i,n).writeline (strLine)

Does anyone have an idea of what to do?

Answer 1

As said in my comment you could create an Excel file in your code and write the values to that file. Right now you create a text file with your code which leads to the issues you describe in your post (text assistant etc.)

Private Sub button_Export_AMAT_Click()

    Dim i As Integer
    Dim n As Integer
    
    ' You might need to add a reference to Excel if your host application is Access
    ' Extra/Reference and select Microsoft Excel Object Library
    Dim xl As Excel.Application
    Set xl = New Excel.Application

    Dim wkb As Workbook
    Set wkb = xl.Workbooks.Add
    
    Dim wks As Worksheet
    Set wks = wkb.Sheets(1)

    'Dim strLine As String
    'Set fs = CreateObject("Scripting.FileSystemObject")
    'Set a = fs.CreateTextFile("\\yvavm301\Commun\CF_GDC\base de dados a trabalhar\AM_AT.csv", True)

    For i = 0 To Me.List_AM_AT.ListCount - 1

        For n = 0 To Me.List_AM_AT.ColumnCount - 1
            
            wks.Cells(i + 1, n + 1).Value = Me.List_AM_AT.Column(n, i)
            'strLine = strLine & """" & Me.List_AM_AT.Column(n, i) & ""","
        Next n
'
'        strLine = Left(strLine, Len(strLine) - 1)
'        a.writeline (strLine)
'        strLine = ""

    Next i
    
    wkb.SaveAs "D:\TMP\EXPORT.XLSX"  ' Adjust accordingly
    wkb.Close False
    xl.Quit
    MsgBox "Your file is exported"
    
End Sub