excel - 如何使用 VBA 将 Excel 表格的单元格值写入 Txt 文件？

Question

我正在尝试创建一个 CSV 文件，该文件将以下列方式导出 Excel 表格列单元格值:

row number "Tab" cell values

但在下面的脚本中，它只导出表中第一个单元格的值(行数和顺序正确)..如何解决？

Private Sub ExportAsCSV()
'Export current sheet as a CSV TXT file on the same location

Dim ThisPathName, CSVFileName, ThisFileName, ThisSheetName As String
Dim SeriesRange As Range
Dim i As Integer

ThisPathName = ThisWorkbook.Path                        ' Generate workbook current path
ThisFileName = ThisPathName & "/" & ThisWorkbook.Name   ' Generate file name & path
ThisSheetName = ActiveSheet.Name                        ' Generate sheet name

CSVFileName = ThisPathName & "/Wren Kitchens " & ThisSheetName & ".txt"
' Sets CSV txt file name and location

If ActiveSheet.Name = "00 Kitchen Series" Then
    'if active table is "00 Kitchen Series"

    Open CSVFileName For Output As #1

    Set SeriesRange = ActiveSheet.ListObjects("KitchenLinesTable").ListColumns(1).DataBodyRange
    
    For i = 1 To SeriesRange.Count
        Print #1, i & "   " & ActiveSheet.ListObjects("KitchenLinesTable").DataBodyRange(1, i).Value
    Next i

    Close #1
End If

结束子

Answer 1

请尝试下一个紧凑的方法来创建 CSV 字符串:
替换此代码:

If ActiveSheet.Name = "00 Kitchen Series" Then
    'if active table is "00 Kitchen Series"

    Open CSVFileName For Output As #1

    Set SeriesRange = ActiveSheet.ListObjects("KitchenLinesTable").ListColumns(1).DataBodyRange
    
    For i = 1 To SeriesRange.Count
        Print #1, i & "   " & ActiveSheet.ListObjects("KitchenLinesTable").DataBodyRange(i, 1).Value
    Next i

    Close #1
    End If
End Sub

与下一个:

    Set SeriesRange = ActiveSheet.ListObjects("KitchenLinesTable").ListColumns(1).DataBodyRange
    Open CSVFileName For Output As #1
        Print #1, Join(Application.Transpose(Evaluate("row(1:" & SeriesRange.count & ")&""    ""&" & SeriesRange.Address)), vbCrLf)
    Close #1

数组 VBA 部分和 Evaluate如果您理解它们，方法可能会很神奇...我将尝试展示必须了解的内容，以便理解上面的代码。打开即时窗口 ( Ctrl + G , being in VBE) and press F5 when code stops (on Stop` 命令)并可以看到返回:

Sub TestToUnderstandAboveCode()
    Dim SeriesRange As Range
    Set SeriesRange = ActiveSheet.ListObjects("KitchenLinesTable").ListColumns(1).DataBodyRange
    
    '1. Placing a range in an array:
    Dim arr: arr = SeriesRange.Value 'it creates a 2D array
          Debug.Print arr(1, 1)      'returns the first array element
    'Make the above array 1D:
    arr = Application.Transpose(arr)
       'or doing it dirrectly:
       arr = Application.Transpose(SeriesRange.Value)
       'it can be tested so:
       Debug.Print Join(arr, "|")
       'another way to create an array is using Evaluate (very powerfull method):
       arr = Evaluate(SeriesRange.Address)                                '2D array
       arr = Application.Transpose(Evaluate(SeriesRange.Address)) '1D array
       Debug.Print Join(arr, "|"): Stop 'it returns the same as above. Press F5 to continue the code
       
    'now we need to  build another 1D array to keep the range rows:
    Dim arrRows: arrRows = Application.Evaluate("row(1:10)") '2D array keeping numbers from 1 to 10
      Debug.Print Join(Application.Transpose(arrRows), "|")  'You can join only a 1D array to see the jonned string
      'Now, let us personalize it according to the necessary string to be processed:
      arrRows = Application.Transpose(Evaluate("row(1:" & SeriesRange.cells.count & ")"))
      Debug.Print Join(arrRows, "|")
      
     'Now, putting all pieces together:
     arr = Evaluate("row(1:" & SeriesRange.count & ")&""    ""&" & SeriesRange.Address) 'it creates a 2D array separating arrays by "   "
        Debug.Print Join(Application.Transpose(arr), "|") ': Stop
       'having a 1D array and needing a string having end lines for each array element we need to build it
       'for doing it we need to firstly join the array elements by vbCrLf (end of line) separator:
       Dim strArr As String
         strArr = Join(Application.Transpose(arr), vbCrLf)
         Debug.Print strArr : Stop 'it returns the string showing all elemnts one bellow the other.
       'and finally doit it at once:
       strArr = Join(Application.Transpose(Evaluate("row(1:" & SeriesRange.count & ")&""    ""&" & SeriesRange.Address)), vbCrLf)
       Debug.Print strArr
End Sub