Parsing XML with VBA

[Peter Jamieson]

Using VBA/Excel I would like to parse out data from an XML feed which looks
like:

- <Runner RunnerNo="1" RunnerName="CONTRACT CATERER" Scratched="N" Rider="T
LOVELOCK-WIGGINS(A" RiderChanged="Y" Barrier="5" Handicap="0" Weight="59.0"
Form="D" LastResult="3X0" Rtng="91">
<WinOdds Odds="27.60" Lastodds="28.50" LastCalcTime="2011-05-07T15:40:12"
CalcTime="2011-05-07T16:10:07" Short="Y" />
<PlaceOdds Odds="7.70" Lastodds="7.40" Short="N" />
- <FixedOdds OfferId="889944" RunnerNo="01" RaceNo="08" MeetingCode="SR"
RaceDayDate="2011-05-07T00:00:00" WinOdds="24.0000" PlaceOdds="6.7500"
OfferName="CONTRACT CATERER" Status="e" LateScratching="0">
<Book BookStatus="e" />
</FixedOdds>
</Runner>

... and so on for up to 24 runners

So far I have the following code. It correctly parses out the Runner nodes
and their attributes shown.

However I am stuck trying to work out how to the data in the feed:

<WinOdds Odds="27.60" Lastodds="28.50" LastCalcTime="2011-05-07T15:40:12"
CalcTime="2011-05-07T16:10:07" Short="Y" />

I need "Odds" "Lastodds" etc

Can Anyone point me in the right direction?
Any help much appreciated!
Cheers, Peter


Sub LoadRaceField()

Dim xmldoc As MSXML2.DOMDocument

Set xmldoc = New MSXML2.DOMDocument

xmldoc.async = False
xmldoc.Load ("http://unitab.com/data/racing/2010/6/28/VR6.xml")

If (xmldoc.parseError.errorCode <> 0) Then
MsgBox ("An error has occurred: " & xmldoc.parseError.reason)
Else
Set runnerList = xmldoc.selectNodes("//Runner")

Sheet1.Cells.Clear

For i = 0 To (runnerList.Length - 1)

Set runner = runnerList.Item(i)

Set runnerNumber = runner.Attributes.getNamedItem("RunnerNo")
Set runnerName = runner.Attributes.getNamedItem("RunnerName")
Set runnerWeight = runner.Attributes.getNamedItem("Weight")
Set riderName = runner.Attributes.getNamedItem("Rider")

If Not runnerNumber Is Nothing Then
Sheet1.Cells(i + 1, 1) = runnerNumber.Text
End If

If Not runnerName Is Nothing Then
Sheet1.Cells(i + 1, 2) = runnerName.Text
End If

If Not runnerWeight Is Nothing Then
Sheet1.Cells(i + 1, 3) = runnerWeight.Text
End If

If Not riderName Is Nothing Then
Sheet1.Cells(i + 1, 4) = riderName.Text
End If

Next
End If

End Sub