the High Seas of Information Technology

Creating 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.

Sub XML_Document_SAMPLE()
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

    '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=''"
'//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
Exit Sub

MsgBox Err.Description
Resume My_Exit

End Sub

Output is a neat, little XML document:

<?xml version="1.0" encoding="UTF-8"?>
<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>
last modified: 28-Jan-2015
Copyright © 2015