What is a DomDocument?

A DomDocument is a container (variable/object) for holding an XML document in your VBA code.

Just as you use a String variable to hold a strings value, you can use a DomDocument to hold an XML document.

(for a complete list of a DomDocuments properties, see halfway down this page)

Why do I care what a DomDocument is?

Excel has some very intuitive ways for moving XML into a spreadsheet (XML Maps), but:

•What if you want to manipulate the data after you’ve retrieved it, but before the data appears in your cells?

•What if you want to import XML data into controls instead of mapped cells, like comboboxes, labels, or textboxes?

Both of these tasks are difficult (if not impossible) to accomplish using XML maps. We can however import the XML data to a DomDocument, then pull out the data we need, write to controls, filter the data, or manipulate the data before it appears in a spreadsheet.

How do I Load XML into a DomDocument?

The following example uses Excel 2003. In the Visual Basic Editor Goto Tools->References and place a checkmark in the box for “Microsoft XML v5.0”

Now we need some XML. Recently I created a Google sitemap for this site, and it’s in XML, so lets use that: AutomateExcel Google XML Sitemap

To load my SiteMap XML document into a DomDocument object in Excel, use the following code (which is commented explaining things):

Sub DomDocumentBasic()



Dim oDom As MSXML2.DOMDocument



    'Create the DomDocument Object

    Set oDom = CreateObject("MSXML2.DOMDocument")



    'Load entire Document before moving on

    oDom.async = False



    'Don't Validate

    oDom.validateOnParse = False



    oDom.Load ("AutomateExcel Google XML Sitemap")



    MsgBox oDom.XML



End Sub

The code simply loads the XML and displays it in a message box:

domdocument

Note: If you load XML from a URL and are having trouble, make sure you didn’t forget the line “oDom.async = False“.

How do I traverse the DomDocument?

Now that I’ve got the XML data in an object, how do I “do something” with it?

LOL, I’m still learning this part. I’ll post the basics in a future post and drop a link to it here.

The post title was “What is a DomDocument”, hopefully you have an idea now.