如何使用 VBA 从 Excel 文件将 & 符号写入 XML 文件？

How to write an ampersand to an XML file from an Excel file using VBA?

提问by pteixeira

First of all, I'm a complete newbie when it comes to VBA, but unfortunately I was dumped this code and I have to deal with it...

首先，当谈到 VBA 时，我是一个完全的新手，但不幸的是我被抛弃了这段代码，我不得不处理它......

What the application does is copy the information inside an Excel xlsm file and paste it in an XML file for further processing.

该应用程序所做的是复制 Excel xlsm 文件中的信息并将其粘贴到 XML 文件中以供进一步处理。

The thing is, it all goes very smooth until I hit an ampersand in one of the Excel cells, i.e.:

问题是，这一切都非常顺利，直到我在其中一个 Excel 单元格中遇到一个与号，即：

I have a cell which has "R&D" and when I'm passing it to the XML file I get the following error:

我有一个带有“R&D”的单元格，当我将它传递给 XML 文件时，出现以下错误：

Run-time error '91':
Object variable or With block variable not set

Bear in mind I'm complete garbage with VBA. I tried changing the contents in the cells for "R&&D", "R&D" but no dice.

请记住，我对 VBA 完全是垃圾。我尝试更改“R&&D”、“R &D”的单元格中的内容，但没有骰子。

I believe the value of the cell comes from this line of code:

我相信单元格的值来自这行代码：

oCell.Offset(, 0).Value

but I would like some help as to how escape the ampersands...

但我想要一些关于如何逃避＆符号的帮助......

Many thanks in advance, if you need more information, let me know.

非常感谢，如果您需要更多信息，请告诉我。

采纳答案by Toby Allen

What your looking for is a way to create html entities in the string, this involves using &and a code for any special characters. '&' is a special char itself.

您要寻找的是一种在字符串中创建 html 实体的方法，这涉及使用&任何特殊字符的代码。'&' 本身就是一个特殊的字符。

There may be code out there to change to do this, but in the meantime in your code replace

可能有代码可以更改以执行此操作，但同时在您的代码中替换

&

with

和

&

and you should solve that particular problem.

你应该解决那个特定的问题。

回答by Patrick Honorez

I wrote the following function for Access, but it should work well with Excel.

我为 Access 编写了以下函数，但它应该适用于 Excel。

Function CleanupStr(strXmlValue) As String  
 'description: Replace forbidden char. &'"<> by their Predefined General Entities   
 'author:      Patrick Honorez - www.idevlop.com   
   Dim sValue As String  
   If IsNull(strXmlValue) Then  
     CleanupStr = ""  
   Else  
     sValue = CStr(strXmlValue)  
     sValue = Replace(sValue, "&", "&amp;") 'do ampersand first !  
     sValue = Replace(sValue, "'", "&apos;")  
     sValue = Replace(sValue, """", "&quot;")  
     sValue = Replace(sValue, "<", "&lt;")  
     sValue = Replace(sValue, ">", "&gt;")  
     CleanupStr = sValue  
   End If  
End Function 

回答by Stef Heyenrath

I found herethese two helper functions:

我在这里找到了这两个辅助函数：

Public Function HTMLToCharCodes(ByVal iString As String) As _
    String
    With New MSXML2.DOMDocument30
        .loadXML "<p>" & iString & "</p>"
        HTMLToCharCodes = _
            .selectSingleNode("p").nodeTypedValue
    End With
End Function

and

和

Public Function CharCodesToHTML(ByVal iString As String) As _
    String
Dim iXml As New MSXML2.DOMDocument30

    With iXml.createTextNode(iString)
        CharCodesToHTML = .xml
    End With
End Function