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

Question

First of all, I am a complete newbie when it comes to VBA, but unfortunately I have reset this code and have to deal with it ...

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

The fact is that everything goes very smoothly until I hit an ampersand in one of the Excel cells, i.e.:

I have a cell with "R & D", and when I pass it to an XML file, I get the following error:

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

Remember that I am full trash with VBA. I tried changing the contents in the cells for "R & D", "R & D", but not in cubes.

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

 oCell.Offset(, 0).Value 

but I would like to help avoid ampersands ...

Thanks a lot in advance if you need more information let me know.

Answer 1

What you are looking for is a way to create html objects in a string, this includes using & and code for any special characters. '& Amp;' is a special char.

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

 & 

with

 & 

and you have to solve this specific problem.

Answer 2

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 

Answer 3

I found these two helper functions here:

 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