Thread: Basic XML assistance
View Single Post
  #5  
Old 23rd January 2012, 01:32 PM
Shaun Shaun is offline
Member
  
Join Date: Jan 1970
Location: Western Australia
Posts: 3,457
Default
I did create a sheet to do this but can't it right now as i have another sheet running and it will stuff of my macros if i open another sheet.

Will try and help with looking at it, here is the original code.
Code:

Sub LoadRaceField()
    
    Dim xmldoc As MSXML2.DOMDocument
   
    Set xmldoc = New MSXML2.DOMDocument

    xmldoc.async = False
    xmldoc.Load ("http://tatts.com/pagedata/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

Here is the part of the exml page we need.
Code:
Runner RunnerNo="1" RunnerName="MAHAZ KINTA" Scratched="N" Rider="J LYON" RiderChanged="N" Barrier="6" Handicap="0" Weight="58.0" LastResult="77X" Rtng="84"
WinOdds Odds="16.80" Lastodds="13.70" LastCalcTime="2012-01-23T12:28:35" CalcTime="2012-01-23T12:47:22" Short="N"/
PlaceOdds Odds="10.20" Lastodds="7.20" Short="N"/


This is i would be looking at doing.
Code:

Sub LoadRaceField()
    
    Dim xmldoc As MSXML2.DOMDocument
   
    Set xmldoc = New MSXML2.DOMDocument

    xmldoc.async = False
    xmldoc.Load ("http://tatts.com/pagedata/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
    Set winoddsList = xmldoc.selectNodes("//WinOdds")
        
          
        For i = 0 To (winoddsList.Length - 1)
            
            Set winodds = winoddsList.Item(i)
            
            Set Wodds = winodds.Attributes.getNamedItem("Odds")
                    
                        
            If Not Wodds Is Nothing Then
                Sheet1.Cells(i + 1, 1) = Wodds.Text
            
            End If
            
        Next
 Set placeoddsList = xmldoc.selectNodes("//PlaceOdds")
        
          
        For i = 0 To (placeoddsList.Length - 1)
            
            Set placeodds = placeoddsList.Item(i)
            
            Set Podds = placeodds.Attributes.getNamedItem("Odds")
                    
                        
            If Not Podds Is Nothing Then
                Sheet1.Cells(i + 1, 1) = Podds.Text
            
            End If
End If
    
End Sub


This is just an idea with no testing you would need to debug it.

I will see if i can find my sheet later today after the races.
Reply With Quote