How to generate an XML document from within VBA

This FAQ contains code to use MSXML6 to generate a template XML document. To be of much use, it will need to be expanded to include your own content, and adapted for your own node name and namespace, and saved to your own preferred location, or adapted to store it in a database for example.

Although presented in the general VBA forum, this should also work in Access VBA and Visual Basic 6.0. The MSXML6 library is included with all versions of Windows since XP Service pack 3 so should be present on most Windows computers in use today. This can be added by using the VBA References menu, see item 3 in the Acknowledgements section.

It should be fairly easily adaptable to meet your own needs.

The code to generate the XML is:
[code VBA]
Public Function GenerateXML(strPath As String) As Integer

' Constant values for XML namespace code and value
Const XML_NAMESPACE_ATTR As String = "xmlns:wow"
Const XML_NAMESPACE_VALUE As String = "http://www.example.com/ns/1.0/"
Const QUOTE_MARK As Integer = 34 ' ASCII code for double quote mark

Dim xmlDom As MSXML2.DOMDocument60 ' Represents the DOM document
Dim xmlRootNode As MSXML2.IXMLDOMElement ' Represents the XML root node

Dim xmlVersion As MSXML2.IXMLDOMProcessingInstruction ' This will become the <xml version="1.0"?> processing instruction
Dim xslStylesheet As MSXML2.IXMLDOMProcessingInstruction ' Optional XSL Stylesheet processing instruction

Set xmlDom = New MSXML2.DOMDocument60

' Create the <?xml version="1.0"?> processing instruction header at the top of the file.
Set xmlVersion = xmlDom.createProcessingInstruction("xml", "version=" & Chr(QUOTE_MARK) & "1.0" & Chr(QUOTE_MARK))
xmlDom.appendChild xmlVersion

' Create the <?xml-stylesheet type="text/xsl" href="stylesheet.xsl" version="1.0" ?> processing instruction. Uncomment the following lines to activate the XSL stylesheet reference (the XSL must exist in the specified folder)
'Set xslStylesheet = xmlDom.createProcessingInstruction("xml-stylesheet", "type=" & Chr(QUOTE_MARK) & "text/xsl" & Chr(QUOTE_MARK) & " href=" & Chr(QUOTE_MARK) & "stylesheet.xsl" & Chr(QUOTE_MARK) & " version=" & Chr(QUOTE_MARK) & "1.0" & Chr(QUOTE_MARK))
'xmlDom.appendChild xslStylesheet

' Define the Database root node and append to Dom
Set xmlRootNode = xmlDom.createElement("RootNode")
xmlDom.appendChild xmlRootNode

' set the root node namespace attribute
' this adds the "http://www.example.com/ns/1.0/" attribute to the main root node
' so that it is not running in open space
xmlDom.documentElement.setAttribute XML_NAMESPACE_ATTR, XML_NAMESPACE_VALUE

' Put your code in here to expand the document to your own requirements
' 1. Create an element, eg
' Dim xmlElement As MSXML2.IXMLDOMElement
' Set xmlElement = xmlDom.createElement("YourElementName")
' 2. Assign it a value
' xmlElement.Text = "Hello XML World"
' 3. Now append it to the root node
' xmlRootNode.appendChild xmlElement

' now save the data to the specified path on disk
xmlDom.Save (strPath)
GenerateXML = xmlRootNode.childNodes.length ' return number of direct child nodes off the root
End Function
[/code]

The XML document produced is:
[code XML]
<xml version="1.0">
<RootNode xmlns:wow="http://www.example.com/ns/1.0" />
[/code]

or with the stylesheet line added
[code XML]
<xml version="1.0">
<?xml-stylesheet type="text/xsl" href="stylesheet.xsl" version="1.0" ?>
<RootNode xmlns:wow="http://www.example.com/ns/1.0" />
[/code]

Acknowledgements
This is the culmination of a lot of work, and it is only fair that credit is given where due to items that helped me get this done:
1. Create XML using the MSXML6 object library: http://www.freevbcode.com/ShowCode.asp?ID=1919
2. YouTube videos using MSXML6 in Visual Basic 6:
https://www.youtube.com/watch?v=HZm-xaaiGag and https://www.youtube.com/watch?v=cYsnWoMCTUM
3. Referencing MSXML withing VBA projects:
http://msdn.microsoft.com/en-us/library/ms763701%28v=vs.85%29.aspx