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.

Answer 1

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 2

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

&

and you should solve that particular problem.