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: stackoverflow.com/questions/11305/how-to-parse-xml-in-vba — Anders Lindahl
– Anders Lindahl, Commented May 15, 2011 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
– Jean-François Corbett, Commented May 15, 2011 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.
– Bogdan P., Commented May 15, 2011 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
– Jean-François Corbett, Commented May 16, 2011 at 13:07

Jean-François Corbett · Accepted Answer · 2011-05-16 13:04:41Z

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 :(

Collectives™ on Stack Overflow

How to parse XML in VBA Excel 2003?

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related