不同Excel设置下CSV格式兼容问题的VBA实现问询

问题背景 #

• 需要将他人生成的.xlsx文件导出为逗号分隔的CSV文件
• 现有VBA另存代码在部分设备正常，但部分同事因Excel区域设置差异，导出的CSV默认使用分号作为分隔符，导致后续流程无法识别文件
• 计划通过「CSV改TXT→替换分号为逗号→改回CSV」的流程解决，同时需修复替换后被错误修改的"ns=2,s"，恢复为"ns=2;s"

现有代码与文件片段 #

修改文件后缀的VBA代码 #

Name "C:\...\Test_csv_saved.csv" As "C:\...\Test_csv_saved.txt"

字符串替换语句 #

sTemp = Replace(sTemp, "; ", ",")

目标CSV的原始内容片段 #

Tus;W;;;;;;;;;NONE;0;0;0;0;;;;;0;0;0;0;;;;;;;;;;0;;;;;;;;0;;;;0;1;;0;;0;0;0;;1;0;0;0;;EQU;NONE;;;;;
0;0;0;;1;0;0;0;;EQU;NONE;;;;;;;;;;;;;;d;7;1;
;;;;"ns=2;s=MAIN";;;0;;;;;;;;0;;;;0;1;;0;;0;0;0;;1;0;

现有核心另存为VBA代码 #

Source_Path = "C:...\Test_xlsx_File.xlsx" 
Set WB_Source = Workbooks.Open(Source_Path)
WB_Source.Application.DisplayAlerts = False 'Don't show pop-ups
WB_Source.SaveAs Filename:= _ 
  "C:...\Test_csv_saved.csv" _ 
  ,FileFormat:=xlCSV, CreateBackup:=False
WB_Source.Application.DisplayAlerts = True 'show pop-ups again
WB_Source.Close (True) 
Application.ScreenUpdating = True 'set to false above

完整解决方案代码 #

Sub ConvertXlsxToCommaCSV()
    Dim Source_Path As String
    Dim CSV_Path As String
    Dim TXT_Path As String
    Dim FileNum As Integer
    Dim sTemp As String
    
    ' 配置文件路径（请根据实际路径修改）
    Source_Path = "C:\...\Test_xlsx_File.xlsx"
    CSV_Path = "C:\...\Test_csv_saved.csv"
    TXT_Path = Replace(CSV_Path, ".csv", ".txt")
    
    ' 步骤1：将XLSX另存为CSV（忽略当前区域的分隔符设置）
    Application.ScreenUpdating = False
    Dim WB_Source As Workbook
    Set WB_Source = Workbooks.Open(Source_Path)
    WB_Source.Application.DisplayAlerts = False
    WB_Source.SaveAs Filename:=CSV_Path, FileFormat:=xlCSV, CreateBackup:=False
    WB_Source.Close True
    WB_Source.Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    ' 步骤2：将CSV重命名为TXT，避免Excel自动解析分隔符
    Name CSV_Path As TXT_Path
    
    ' 步骤3：读取TXT文件内容并执行替换
    FileNum = FreeFile()
    Open TXT_Path For Input As #FileNum
    sTemp = Input$(LOF(FileNum), FileNum)
    Close #FileNum
    
    ' 先替换所有分号为逗号
    sTemp = Replace(sTemp, ";", ",")
    ' 修复被错误替换的特定字符串
    sTemp = Replace(sTemp, "ns=2,s", "ns=2;s")
    
    ' 步骤4：将修改后的内容写回TXT文件
    FileNum = FreeFile()
    Open TXT_Path For Output As #FileNum
    Print #FileNum, sTemp
    Close #FileNum
    
    ' 步骤5：将TXT重命名回CSV
    Name TXT_Path As CSV_Path
    
    MsgBox "转换完成，已生成逗号分隔的CSV文件！", vbInformation
End Sub

代码说明 #

1. 先按原有逻辑将XLSX另存为CSV，无论当前区域生成的是分号还是逗号分隔
2. 通过重命名将CSV转为TXT，绕开Excel对分隔符的自动解析
3. 一次性读取TXT全部内容，先统一替换分号为逗号，再针对性修复特定字符串"ns=2,s"
4. 最后将修改后的TXT改回CSV格式，确保文件格式符合要求
5. 全程关闭屏幕更新和弹窗提示，提升运行效率

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