使用VBA PasteSpecial导出文本文件时随机出现引号问题求助

使用VBA的PasteSpecial方法将Excel数据复制到文本文件时，部分行首尾会随机出现引号，添加替换代码后问题仍未解决。

现有VBA代码 #

Windows(fsname).Activate
ActiveSheet.Calculate
Range(d).Copy

Windows(bsname).Activate
Range("a1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
ActiveWorkbook.SaveAs Filename:=loutfile, FileFormat:=xlTextWindows

输出数据示例 #

"OP|99999999999|1|2023-05-10 00:00:00|10111673-2-AC-M-0-0-11-7100297|CATARACT KIT, PREOP|10111673|7100297|AMBULATORY CARE UNIT||MEDICARE||||||MB0||||CAH471305"
OP|08065183150|1|2023-05-10 00:00:00|10111673-2-AC-M-0---7100475|DUOVISC 0.55ML SYRINGE|10111673|7100475|AMBULATORY CARE UNIT||MEDICARE||||||MB0||||CAH471305
OP|00143959525|1|2023-05-10 00:00:00|10111673-2-AC-M-0-0-10-7100803|LIDOCAINE MPF INJ SDV 1% 5ML|10111673|7100803|AMBULATORY CARE UNIT||MEDICARE||||||MB0||||CAH471305
"OP|99999999999|1|2023-05-10 00:00:00|10112610-2-AC-M-0-1-3-7100297|CATARACT KIT, PREOP|10112610|7100297|AMBULATORY CARE UNIT||MEDICARE||||||MB0||||CAH471305"

已尝试的无效替换代码 #

Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

有效解决方案 #

方法1：直接写入文本文件（绕过Excel中转） #

完全控制输出内容，避免Excel自动添加引号的逻辑：

Dim wsSource As Worksheet
Dim rngData As Range
Dim fileNum As Integer
Dim row As Range
Dim cell As Range
Dim cellVal As String
Dim outputLine As String

Set wsSource = Windows(fsname).ActiveSheet
wsSource.Calculate
Set rngData = wsSource.Range(d)

fileNum = FreeFile()
Open loutfile For Output As #fileNum

' 遍历每行数据，拼接成|分隔的字符串
For Each row In rngData.Rows
    outputLine = ""
    For Each cell In row.Cells
        cellVal = Trim(CStr(cell.Value))
        outputLine = IIf(outputLine = "", cellVal, outputLine & "|" & cellVal)
    Next cell
    ' 写入文本文件
    Print #fileNum, outputLine
Next row

Close #fileNum

方法2：优化中转工作簿的处理逻辑 #

若必须保留中转工作簿，修改格式与替换逻辑：

Windows(fsname).Activate
ActiveSheet.Calculate
Range(d).Copy

Windows(bsname).Activate
Range("a1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

' 设置单元格为文本格式，避免格式触发自动引号
Selection.NumberFormat = "@"
' 使用ASCII字符码替换引号，确保覆盖所有情况
Selection.Replace What:=Chr(34), Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
' 强制刷新单元格值，清除潜在的格式缓存
Selection.Value = Selection.Value

ActiveWorkbook.SaveAs Filename:=loutfile, FileFormat:=xlTextWindows

注：Excel保存为文本文件时，若单元格内容包含逗号、换行符等特殊字符会自动添加引号，可检查源数据是否存在这类字符，或直接使用方法1彻底规避。

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