Excel VBA宏导出CSV行尾出现空格，如何去除或避免？

我用VBA编写宏将Sheet1的A、B列数据导出为CSV文件，但导出后每行末尾出现大量空格，尝试用Trim函数未解决，求可行的去空格方案。原代码如下：

'object to use as folder
Dim fld As Object
Set fld = CreateObject("Scripting.FileSystemObject")

Dim myFile As Object
Set myFile = fld.CreateTextFile("C:\path\filename.csv", True)
 
End Sub

Sub data_to_text_file()

'variables
Dim TextFile As Integer
Dim iCol As Integer
Dim cVal As Range
Dim i As Integer
Dim myFile As String
Dim myRange As Range, lr As Long

'defines range

lr = Cells(Rows.Count, "A").End(xlUp).Row

    Set myRange = Sheets("Sheet1").Range("A1:A" & lr)

iCol = myRange.Count

'path to the text file
myFile = "C:\path\filename.csv"

'define FreeFile to the variable file number
TextFile = FreeFile

'using append command to add text to the end of the file
Open myFile For Output As TextFile

'loop to add data to the text file
For i = 1 To iCol
Print #TextFile, Cells(i, 1),
Print #TextFile, Cells(i, 2)
Next i

'close command to close the text file after adding data
Close #TextFile

End Sub

问题根源 #

你代码中使用的Print #TextFile, Cells(i, 1),里的逗号是输出列表分隔符，VBA会按固定宽度输出内容（类似制表对齐），不足的部分自动补空格填充，这就是行尾大量空格的直接原因。另外你开头定义的FileSystemObject对象没有实际用到，代码存在冗余。

可行解决方案 #

方案1：用Write语句替代Print（推荐）

Write是专门为结构化文本（如CSV）设计的输出语句，它会自动用逗号分隔字段，不会补空格，还能自动为包含特殊字符（逗号、引号）的内容添加引号，符合CSV标准格式：

Sub data_to_text_file()
    Dim TextFile As Integer
    Dim i As Integer
    Dim myFile As String
    Dim lr As Long

    ' 获取A列最后一行行号
    lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    ' CSV文件路径
    myFile = "C:\path\filename.csv"

    ' 分配空闲文件号
    TextFile = FreeFile
    ' 打开文件准备写入
    Open myFile For Output As TextFile

    ' 循环写入每行数据
    For i = 1 To lr
        Write #TextFile, Sheets("Sheet1").Cells(i, 1).Value, Sheets("Sheet1").Cells(i, 2).Value
    Next i

    ' 关闭文件
    Close #TextFile
End Sub

方案2：手动拼接字符串+Trim处理

如果坚持使用Print，可以手动拼接A、B列内容，同时用Trim清除单元格内容前后的空格，避免行尾补位：

Sub data_to_text_file()
    Dim TextFile As Integer
    Dim i As Integer
    Dim myFile As String
    Dim lr As Long
    Dim lineText As String

    lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    myFile = "C:\path\filename.csv"

    TextFile = FreeFile
    Open myFile For Output As TextFile

    For i = 1 To lr
        ' 拼接A、B列内容，用逗号分隔，同时Trim每个单元格的前后空格
        lineText = Trim(Sheets("Sheet1").Cells(i, 1).Value) & "," & Trim(Sheets("Sheet1").Cells(i, 2).Value)
        Print #TextFile, lineText
    Next i

    Close #TextFile
End Sub

方案3：利用FileSystemObject写入文件

你开头定义了FileSystemObject但未使用，用它的WriteLine方法也能避免空格问题，代码更简洁：

Sub data_to_text_file()
    Dim fld As Object
    Dim myFile As Object
    Dim i As Integer
    Dim lr As Long
    Dim lineText As String

    ' 创建文件系统对象
    Set fld = CreateObject("Scripting.FileSystemObject")
    ' 创建/覆盖CSV文件
    Set myFile = fld.CreateTextFile("C:\path\filename.csv", True)

    lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

    ' 循环写入每行
    For i = 1 To lr
        lineText = Trim(Sheets("Sheet1").Cells(i, 1).Value) & "," & Trim(Sheets("Sheet1").Cells(i, 2).Value)
        myFile.WriteLine lineText
    Next i

    ' 关闭文件并释放对象
    myFile.Close
    Set myFile = Nothing
    Set fld = Nothing
End Sub

额外注意事项 #

• 所有操作要明确指定Sheets("Sheet1")，避免使用无前缀的Cells（默认指向当前激活工作表）导致数据错误。
• 如果单元格内容包含逗号、双引号等特殊字符，优先选择方案1的Write语句，它会自动处理CSV格式的转义规则，避免导出的CSV文件格式混乱。

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