Using VBA To Extract Data from an XML File

[ransomedbyfire]

I am trying to get data from an XML file into a spreadsheet using VBA. I have looked up how to do this. And I get the main gist, but it seems that the example I have been looking at is too simple to answer my questions.

I am dealing with an XML file that appears to have the structure:

Document

AsOfDate

DataSetName

Models
​

Model
​

#id

CategoriesOrSymbols
​

SecuritySymbol
​

CodeDescription

ModelSecurities
​

SectorCodeDescription
​

and so on

I've read how to use VBA to get data from a child node of the root using code like this:

<code>
Set root = doc.DocumentElement
For Each element In root.ChildNodes
Set attributes = group.attributes
Set langcode = attributes.getNamedItem("langcode")

ActiveSheet.Cells(int1, 1).Value = langcode.Text
Set children = group.ChildNodes
For Each child In children

If child.nodeName = "GroupID" Then
ActiveSheet.Cells(int1, 2) = child.nodeTypedValue
End If
</code>

But how do you get data from a child of a child of a child of a child of the root? (I am particularly interested in securitysymbol and securitycodedescription.

 

[arkusM]

I know this is quite late... by a year..

But What I end up doing is setting up a branching loops for the child

         Set oChildren = oIndexPrice.childNodes 'This looks from the root
        For Each oChild In oChildren
            Set oChildren2 = oChild.childNodes 
            If oChild.hasChildNodes Then ' If the first tier of childnodes have childnodes

                For Each oChild2 In oChildren2
                    Set oChildren3 = oChild2.childNodes 'This is for the childnodes of the second teir of childnodes
                    If oChild2.nodeName = "index" Then 'I had to play with this so that the worked with out giving eird results, you may be able to just test if the ochild2 has child nodes.
                        For Each oChild3 In oChildren3
                            'more code to do whatever
                        Next oChild3
                    End If
                        'Code to do more stuff
                Next oChild2
                           End If
        Next oChild