从MC访问表单到Excel文件的导出列表框值维护相同数量的列

Question

因此，我设法创建了一个代码，以将和粘贴列表框值复制到新创建的Excel文件。 问题是，我将其全部与逗号相连和分开。它可以正常工作，但是由于它的导出方式，因此我必须将Excel文本使用到列功能上的功能，才能像我想要的那样放置数据。

这是代码：

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

我的问题是：是否有可能出现类似的表格，即。具有相同数量的列并将其填充有正确的值？

更改必须在此处进行（请参见下文），对吗？

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

我尝试过没有运气以下操作：

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

有人知道该怎么办吗？

Answer 1

如我在我的评论中所述，您可以在代码中创建一个Excel文件并将值写入该文件。现在，您创建一个带有代码的文本文件，该文件导致您在帖子（文本助手等）中描述的问题

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