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

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?

CodePudding user response：

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