How to parse XML in VBA Excel 2003?

Question

I am trying to parse a general XML file, via VBA. What I want to do with it: extract the values of the xml nodes, write them into a XML file and export it.

Do you know any library that actually lets me read one node at a time, for me to process with a understandable documentation, and some examples, even minimal ones.

So far:

Sub Go()

    Dim xmlDoc As MSXML2.DOMDocument
    Dim xmlElement As MSXML2.IXMLDOMElement
    Dim xmlNode As MSXML2.IXMLDOMElement

    Set xmlDoc = New MSXML2.DOMDocument
    xmlDoc.Load ("E:\cdCatalog.xml")

    Set xmlElement = xmlDoc.documentElement
    Set xmlNode = xmlElement.FirstChild

    parseNodes xmlElement, 1, 1
    'parseNodes xmlNode, 1, 1

End Sub

Sub parseNodes(node As MSXML2.IXMLDOMElement, i As Integer, j As Integer)
    Dim child As MSXML2.IXMLDOMNode

    'result = node.baseName & " : " & node.Text
    result = node.nodeName

    Sheet1.Activate
    ' text if...
    Cells(i, j) = result

    j = j + 1
    If (node.hasChildNodes) Then

        For Each child In node.childNodes
            i = i + 1
            'MsgBox child.Text
            MsgBox TypeName(node.childNodes)
            parseNodes child, i, j
        Next
    End If

End Sub

This might be helpful: http://stackoverflow.com/questions/11305/how-to-parse-xml-in-vba — Anders Lindahl, May 15 '11 at 17:58
Show us what you've tried so we can help you correct it. Else type "VBA DOM XML tutorial" in your favourite search engine. — Jean-François Corbett, May 15 '11 at 18:36
I have edited the question. I am now struggling to understand why " Dim gReader As New XmlTextReader(FileName) " is an error. Thanks. — Bogdan P., May 15 '11 at 18:41
I see you've changed your question pretty extensively. You should really accept an answer and start a new question if you want to get help. — Jean-François Corbett, May 16 '11 at 13:07

Jean-François Corbett · Accepted Answer · 2011-05-16T13:04:41.043

Addressing your updated question as specified in your comment:

You can't instance objects in VBA like that, with an argument in the Dim statement. Try:

Dim gReader As XmlTextReader
gReader = New XmlTextReader

Also, I suggest you read the XmlTextReader documentation here:

http://msdn.microsoft.com/en-us/library/1af7xa52.aspx

The examples illustrate how to use XmlTextReader.

EDIT: As far as I can tell from a cursory internet search, XmlTextReader is implemented for .NET but not for VBA.

You may want to consider using DOM instead of XmlTextReader. I find DOM relatively easy to use. The downside is that it is inefficient for very large XML files. Unless you are manipulating large files, DOM should work fine for you.

Dim xlmDoc As Object
Set xlmDoc = CreateObject("Msxml2.DOMDocument.6.0")
xmlDoc.Load fileName

So.. first of all.. Thank you for your help. But I couldn't use XmlTextReader. Maybe I am a too noob, but it doesn't work (for me). I've updated my code so far.. Now i have a reference mismatch :( — Bogdan P., May 16 '11 at 09:30

How to parse XML in VBA Excel 2003?

1 Answers1