使用VBA移除.csv转.txt后多余的逗号行

从源系统提取的CSV文件需要转成TXT格式上传到其他系统，已编写VBA实现批量转换，但转换后的TXT文件末尾存在大量仅含逗号的空行（无真实数据、无空格），导致无法正常上传。

转换后问题示例 #

File name,dim1,blank,dim2
file 1,1,,apple
file 1,2,,orange
file 1,3,,banana
,,
,,
,,
,,

期望效果 #

File name,dim1,blank,dim2
file 1,1,,apple
file 1,2,,orange
file 1,3,,banana

之前尝试的无效方法 #

曾用正则表达式处理，但仅能移除逗号，空行仍保留，代码如下：

fn = Application.GetOpenFilename(FolderPath & FileName & ".txt")
If fn = "" Then Exit Sub
txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
With CreateObject("VBScript.RegExp")
    .Global = True: .MultiLine = True
    .Pattern = ",+$"
    Open Replace(fn, ".txt", "_Clean.txt") For Output As #1
        Print #1, .Replace(txt, "")
    Close #1
End With

修改后的VBA解决方案 #

直接调整原转换逻辑，在生成TXT时过滤掉仅含逗号的行，无需先复制再二次处理：

Sub CreateTextFiles()
    Application.ScreenUpdating = False
    
    Dim rng As Range
    Set rng = Range("A1:A4")
    Dim FileName As String
    Dim FolderPath As String
    FolderPath = Range("C1").Value
    
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim inputFile As Object, outputFile As Object
    Dim lineText As String
    
    For Each cell In rng
        FileName = cell.Value
        ' 打开源CSV和目标TXT文件
        Set inputFile = fso.OpenTextFile(FolderPath & FileName & ".csv", 1) ' 1=只读模式
        Set outputFile = fso.CreateTextFile(FolderPath & FileName & ".txt", True) ' True=覆盖已有文件
        
        ' 逐行读取并过滤无效行
        Do Until inputFile.AtEndOfStream
            lineText = inputFile.ReadLine
            ' 判断：去掉所有逗号后内容为空的行即为仅含逗号的无效行，跳过写入
            If Trim(Replace(lineText, ",", "")) <> "" Then
                outputFile.WriteLine lineText
            End If
        Loop
        
        ' 关闭文件释放资源
        inputFile.Close
        outputFile.Close
    Next cell
    
    Set fso = Nothing
    Set inputFile = Nothing
    Set outputFile = Nothing
    
    Application.ScreenUpdating = True
    MsgBox "Text files have been created"
End Sub

说明：这段代码替换了原有的FileCopy逻辑，改为逐行读取CSV内容，直接过滤掉仅由逗号组成的无效行后写入TXT，一步到位生成符合上传要求的文件。

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