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

Question

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...

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

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

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

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.

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.

Can you please post some of the code? Particularly, the line (and some relevant lines before it) where you get the error? Also, you can look at the locals window to find out the status of certain variables which can help you with debugging (in VBE, go to View->Locals Window) — Joseph, Dec 17 '12 at 21:29
the code breaks here: `Set oNamedNodeMap = oDOM.ChildNodes(0).Attributes` oDOM is a MSXML2.DOMDocument and oNamedNodeMap is a MSXML2.IXMLDOMNamedNodeMap. — pteixeira, Dec 17 '12 at 22:34

iDevlop · Answer 1 · 2013-01-17T07:02:31.900

I wrote the following function for Access, but it should work well with 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

Thanks but the code actually works now for some reason, even though I didn't change anything since yesterday... Thank you anyway, though. This might come in handy for anyone who has the same issue :D — pteixeira, Dec 18 '12 at 16:04

score 1 · Accepted Answer · answered Dec 17 '12 at 18:19

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.

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.

answered Dec 17 '12 at 18:19

Toby Allen

10,997
11
73
124

I did, still got the same error. I edited the original post to reflect that, my bad. – pteixeira Dec 17 '12 at 18:20
in that case its not the fact your missing the first param in the call `oCell.Offset(, 0).Value` is it? – Toby Allen Dec 17 '12 at 18:22
don't think so. if I don't have an ampersand in the cell, the VBA code works perfectly, so it might not be the issue. – pteixeira Dec 17 '12 at 18:25
can you show more of the code surrounding where your issue is? – Toby Allen Dec 17 '12 at 18:26
This is where I (think) get the cell value from the xls file: `For Each oCell In Worksheets("Dependencies").ListObjects(1).DataBodyRange.Columns(0).Cells 'sXML = sXML & " " sXML = sXML & " "` and the code breaks here: `Set oNamedNodeMap = oDOM.ChildNodes(0).Attributes` oDOM is a `MSXML2.DOMDocument` and oNamedNodeMap is a `MSXML2.IXMLDOMNamedNodeMap` – pteixeira Dec 17 '12 at 18:33
I just thought of something, did you change the text in the cell to &? If so that will still cause the error, you need to convert the text from the cell before sending to xml. Pull it out of your above code as a seperate variable. – Toby Allen Dec 17 '12 at 20:13
Yes, I tried changing it in the cell (what the app does is merge two different excel files into one) for `&` but I still get the error. When I'm reading the elements from the source files I check for an ampersand and tried changing it to `&` when I'm writing the destination file. What do you mean by changing the text? – pteixeira Dec 17 '12 at 22:28
Well, changing & to `&` works today, even though I didn't change anything whatsoever in the VBA code or Excel files. So I'm setting @tobyallen response as the correct one. Guessing the Patron Saint of Programming had his hand on this one... Thanks a million! – pteixeira Dec 18 '12 at 14:54

score 0 · Answer 3 · answered Oct 28 '13 at 08:10

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

These functions require an additional reference and do not convert all predefined entities: apostrophe (') is not translated for instance — iDevlop, Aug 11 '16 at 08:21

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

3 Answers3

Linked