如何使用VBA从Excel文件中编写一个& XML文件?

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.

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

3 个解决方案

#1

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

与

&amp;

and you should solve that particular problem.

你应该解决这个问题。

#2

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

#3

I found here these 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

#1