如何使用VBA将Excel文件中的“&”符号写入XML文件？

Question

首先，就VBA而言，我完全是个新手，但不幸的是，我被扔给了这段代码，并且必须处理它...

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

问题在于，一切都很顺利，直到我在一个Excel单元格中遇到一个&符号，例如：

我有一个单元格包含“R&D”，当我将其传递给XML文件时，我会收到以下错误：

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

记住我在 VBA 方面是个彻底的垃圾。我试过改变单元格中“R&&D”、“R&D”的内容，但没有用。

我认为单元格的值来自以下代码行：

oCell.Offset(, 0).Value

但我希望能够得到一些关于如何转义&符号的帮助...

非常感谢您提前的帮助，如果您需要更多信息，请让我知道。

- pteixeira

---

你能否请发一些代码？特别是出现错误的那一行（以及它之前的相关行）？此外，你可以查看本地窗口以了解某些变量的状态，这可以帮助你进行调试（在VBE中，转到“视图”->“本地窗口”）。 - Joseph

代码在这里出现了错误： Set oNamedNodeMap = oDOM.ChildNodes(0).Attributes oDOM 是一个 MSXML2.DOMDocument，而 oNamedNodeMap 是一个 MSXML2.IXMLDOMNamedNodeMap。 - pteixeira

Answer 1

我为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 

- iDevlop

---

谢谢，但是代码现在出奇的能用了，尽管我自昨天以来什么都没改...不过还是谢谢你。这对于遇到同样问题的人可能会有帮助 :D - pteixeira

Answer 2

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

可能已经有可用的代码来完成此操作，但与此同时您可以在代码中进行替换。

&

使用

&

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

- Toby Allen

---

我已经尝试过了，但仍然出现相同的错误。我已经编辑了原帖以反映这一点，我的错。 - pteixeira

在这种情况下，你不是忘记了调用中的第一个参数 oCell.Offset(, 0).Value 吗？ - Toby Allen

你能展示一下你的问题所在代码周围的更多内容吗？ - Toby Allen

这是我（认为）从xls文件获取单元格值的代码：For Each oCell In Worksheets("Dependencies").ListObjects(1).DataBodyRange.Columns(0).Cells 'sXML = sXML & " " sXML = sXML & " " 代码在此处中断：Set oNamedNodeMap = oDOM.ChildNodes(0).Attributes 其中oDOM是MSXML2.DOMDocument，而oNamedNodeMap是MSXML2.IXMLDOMNamedNodeMap。 - pteixeira

今天将“&”更改为“&”有效，尽管我没有在VBA代码或Excel文件中更改任何内容。因此，我将@tobyallen的回答设置为正确答案。 猜测编程的守护神在这个问题上发挥了作用... 非常感谢！ - pteixeira

Answer 3

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

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

并且

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

- Stef Heyenrath

---

这些函数需要额外的引用，并且不会转换所有预定义实体：例如，撇号（'）没有被翻译。 - iDevlop