Using VBA To Extract Data from an XML File

ransomedbyfire

Board Regular
Joined
Mar 9, 2011
Messages
121
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.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

arkusM

Well-known Member
Joined
Apr 12, 2007
Messages
560
I know this is quite late... by a year..

But What I end up doing is setting up a branching loops for the child
Code:
         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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top