如何使用VBA将Excel表格单元格值写入TXT文件并修复仅导出首格问题

核心问题原因 #

• 循环读取单元格时索引参数写反：原有代码ActiveSheet.ListObjects("KitchenLinesTable").DataBodyRange(1, i).Value的索引规则是(行号, 列号)，这段代码实际一直读取第一行的第i列值，自然只能拿到第一列的首个单元格数据
• 分隔符不符合要求：原有代码用硬编码空格分隔，不符合制表符分隔的需求

优化点说明 #

• 修正索引逻辑，改为读取第1列的第i行值
• 用VBA内置常量vbTab替代空格作为分隔符
• 修正变量声明的类型遗漏问题，避免Variant类型隐式转换隐患

修复后完整代码 #

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

Dim ThisPathName As String, CSVFileName As String, ThisFileName As String, 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 & vbTab & SeriesRange(i).Value
    Next i

    Close #1
End If
End Sub

内容的提问来源于stack exchange，提问作者Nader Belal