trypots.nethomeCreating an XML document from scratch with VBA
This is a sample vba routine to create a simple XML document using VBA.
A few notes:
The Selectionlanguage property for XPath is optional, as XPath is the default selection language for MSXML version 6.0 and higher. Also, this particular example does not use an xsl transform and so we don't need to set the SelectionNamespaces for xsl. I simply include these two statements in all my XML document creations as a habit so that I won't forget them when I do need them.
A free and fairly good XML Editor is XML Copy Editor. A number of programming-oriented text editors also provide XML plugins. With tools like these you can apply xsl transforms, pretty print your XML documents, validate the XML syntax, and generally have a more pleasant experience working with your XML code. Naturally, there are many other IDE's and text editors that provide tools for working with XML.
Dim s As String
Dim fPath As String
Dim i As Long
Dim arr()
Dim dom As MSXML2.DOMDocument
Dim root As MSXML2.IXMLDOMNode
Dim child As MSXML2.IXMLDOMNode
Dim node As MSXML2.IXMLDOMNode
Dim att As MSXML2.IXMLDOMAttribute
'IMPORTANT:
'This code requires setting a reference to the MSXML object library
'In the VB editor under the Tools | References menu, select the
' checkbox for "Microsoft XML, v6.0" (or higher)
On Error GoTo ErrHandler:
'//Sample data for our XML document
ReDim arr(0 To 4)
arr(0) = Array("The Ghost Map", "2006", "Steven Johnson")
arr(1) = Array("The Magicians", "2010", "Lev Grossman")
arr(2) = Array("A Brilliant Darkness", "2009", "Joao Magueijo")
arr(3) = Array("Longitude", "2007", "Dava Sobel")
arr(4) = Array("Bill Bryson", "2003", "A Short History of Nearly Everything")
'//Create an XML Document
Set dom = New MSXML2.DOMDocument
dom.SetProperty "SelectionLanguage", "XPath"
dom.SetProperty "SelectionNamespaces", "xmlns:xsl='http://www.w3.org/1999/XSL/Transform'"
'//Append an xml processing instruction
dom.appendChild dom.createProcessingInstruction("xml", "version='1.0' encoding='UTF-8'")
'//Create a root node
Set root = dom.createNode(1, "dataroot", "")
'//Book nodes
For i = 0 To UBound(arr)
'//Create a child node of root with two attributes
Set child = dom.createNode(1, "book", "")
child.Text = arr(i)(0)
Set att = dom.createAttribute("year")
att.nodeValue = arr(i)(1)
child.Attributes.setNamedItem att
Set att = dom.createAttribute("author")
att.nodeValue = arr(i)(2)
child.Attributes.setNamedItem att
root.appendChild child
Next i
'//Append root node to the document
dom.appendChild root
'//View the document
'//Note: The UTF-8 encoding will not show here
'// The dom doc will use it's own encoding internally until the document is saved
Debug.Print dom.XML
'//Save the document
fPath = CreateObject("WScript.Shell").SpecialFolders.Item("Desktop") & "\" & "test.xml"
dom.Save fPath
My_Exit:
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume My_Exit
End Sub
Output is a neat, little XML document:
<dataroot>
<book year="2006" author="Steven Johnson">The Ghost Map</book>
<book year="2010" author="Lev Grossman">The Magicians</book>
<book year="2009" author="Joao Magueijo">A Brilliant Darkness</book>
<book year="2007" author="Dava Sobel">Longitude</book>
<book year="2003" author="A Short History of Nearly Everything">Bill Bryson</book>
</dataroot>