I was working on a spreadsheet that submitted XML data to a web service and the XML that was being generated by the XmlDocument object was not very tidy. Normally with C# I use Tidy.NET to clean up the HTML or XML, but since this was being used in Excel I didn't want to have any external dependencies. I found the following code online and it seems to work pretty well:
Option Explicit ' http://www.vb-helper.com/howto_formatted_xml_document.html ' Add formatting to the document. Public Sub FormatXmlDocument(ByVal xml_doc As DOMDocument) FormatXmlNode xml_doc.documentElement, 0 End Sub ' Add formatting to this element. Indent it and add a ' carriage return before its children. Then recursively ' format the children with increased indentation. Private Sub FormatXmlNode(ByVal node As IXMLDOMNode, _ ByVal indent As Integer) Dim child As IXMLDOMNode Dim text_only As Boolean ' Do nothing if this is a text node. If TypeOf node Is IXMLDOMText Then Exit Sub ' See if this node contains only text. text_only = True If node.hasChildNodes Then For Each child In node.childNodes If Not (TypeOf child Is IXMLDOMText) Then text_only = False Exit For End If Next child End If ' Process child nodes. If node.hasChildNodes Then ' Add a carriage return before the children. If Not text_only Then node.insertBefore _ node.ownerDocument.createTextNode(vbCrLf), _ node.FirstChild End If ' Format the children. For Each child In node.childNodes FormatXmlNode child, indent + 2 Next child End If ' Format this element. If indent > 0 Then ' Indent before this element. node.parentNode.insertBefore _ node.ownerDocument.createTextNode(Space$(indent)), _ node ' Indent after the last child node. If Not text_only Then _ node.appendChild _ node.ownerDocument.createTextNode(Space$(indent)) ' Add a carriage return after this node. If node.nextSibling Is Nothing Then node.parentNode.appendChild _ node.ownerDocument.createTextNode(vbCrLf) Else node.parentNode.insertBefore _ node.ownerDocument.createTextNode(vbCrLf), _ node.nextSibling End If End If End Sub
Comments are closed.