删除XML文件中的多余引号

Andy Do 发表于 Dev

安迪

我正在制作一个快速而肮脏的XML生成器。下面的代码很好用，但是当我打开它时，它带有附加的双引号，即<“ something”>会变成“ <”“ something”“>。下面的代码有办法删除它们吗？ xlTextPrinter，因为某些单元格的字符数超过255个，我们将不胜感激！

Sub test()
    Dim Desktop As String
    Dim FileName As String
    Desktop = CreateObject("Wscript.Shell").Specialfolders("Desktop")
    With ActiveSheet
        FileName = .Range("B1").Value
        .Range("H2:K33").Copy
        Workbooks.Add
        ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End With
    With ActiveWorkbook
        .SaveAs FileName:=Desktop & Application.PathSeparator & FileName  _
            , FileFormat:=xlTextMSDOS, CreateBackup:=False
        .Close SaveChanges:=False
    End With
End Sub

扎伊德

由于excel在导出为文本时在某些情况下会在文件中添加引号，因此您有两种选择。

Chr(9) 是标签

Chr(34) 是报价

vbCrLf 是换行符

除了保存文件，您还可以直接将其写为文本：

Dim Desktop As String
Dim FileName As String
Dim fs As Object
Dim f As Object
Dim i As Integer

Set fs = CreateObject("Scripting.FileSystemObject")

Desktop = CreateObject("Wscript.Shell").Specialfolders("Desktop")
With ActiveSheet
    FileName = Desktop & Application.PathSeparator & .Range("B1").Value
    Set f = fs.createTextFile(FileName & ".txt", True, False)

    For i = 2 To 33
        f.write (.Cells(i, 8) & Chr(9) & .Cells(i, 9) & Chr(9) & .Cells(i, 10) & Chr(9) & .Cells(i, 11) & vbCrLf)
    Next

    f.Close
    Set f = Nothing
    Set fs = Nothing
End With

或者像Siddarth Rout建议的那样，您可以在创建文件后打开文件并替换其添加的其他引号：

Dim Desktop As String
Dim FileName As String
Desktop = CreateObject("Wscript.Shell").Specialfolders("Desktop")
With ActiveSheet
    FileName = Desktop & Application.PathSeparator & .Range("B1").Value
    .Range("H2:K33").Copy
    Workbooks.Add
    ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End With
With ActiveWorkbook
    .SaveAs FileName:=FileName _
        , FileFormat:=xlTextMSDOS, CreateBackup:=False
    .Close SaveChanges:=False
End With

Dim fs As Object
Dim f As Object
Dim content As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(FileName & ".txt", 1, -2)

content = f.readall

f.Close

content = Replace(content, Chr(34) & "<", "<")
content = Replace(content, ">" & Chr(34), ">")
content = Replace(content, Chr(34) & Chr(34), Chr(34))

Set f = fs.createTextFile(FileName & ".txt", True, False)

f.write (content)

f.Close
Set f = Nothing
Set fs = Nothing