P
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
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