CODEWITHSUNDEEP
xmlvba
Michael St Clair
Michael St Clair

Reputation: 6615

Replace & with & using VBA

Is there a way to find & in the file I am creating with VBA and replace it with &?

Here is a short snippet of my code to show how I am creating the file.

Open myFile For Output As #1

Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"

Print #1, "     <name>" & element & "</name>"

Close #1

One of the elements is C&C which is causing my problem.

Upvotes: 0

Views: 9131

Answers (2)

Tmdean
Tmdean

Reputation: 9299

If you're generating an XML file, unless you're doing something really simple it might be smarter to use an XML library instead of dealing with a million one off little problems like this.

'' Add reference to Microsoft XML, v6.0
Dim doc As New MSXML2.DOMDocument
Dim root As MSXML2.IXMLDOMElement
Dim reader As New SAXXMLReader60
Dim writer As New MXXMLWriter60

Set root = doc.CreateElement("name")
root.Text = element
doc.appendChild root

Set reader.ContentHandler = writer
writer.Indent = True
reader.Parse doc

Print #1, writer.Output

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166156

Print #1, "     <name>" & Replace(element, "&", "&amp;")  & "</name>"

Upvotes: 2

Related Questions