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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,982
Messages
5,834,698
Members
430,310
Latest member
Excelorate3305

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