Excel vba website tr and td loop

[johnbird1988]

Hello,

I am trying to loop through a website to scrap some data. The HTML look like the below:

< div class="naps-list-contain">< div class="hot-list">< div class="hot-list-item">< table class="dog-list">< thead><tr class="top-header">< td class="center-td silk-td">Silk< /td>< td class="nap-td">Nap< /td>< td class="tipster-td">Tipster< /td>< td class="pos-td">Result< /td>< td class="pos-td">Odds< /td>< td class="center-td odds-td">View< /td>< /tr>< /thead>< tbody>

< tr class="dog-list-item">< td class="runner-td silk-td">< img class="lazy" src="/images/silks/32976.gif"/>< /td>< td class="runner-td nap-td">< a class="runner-name" href="/horses/grapevine.php">Grapevine< /a>< /td>< td class="runner-td list-naps-td tipster-td">< div class="nap-list">< div class="nap-item">< div class="nap-left">< div class="nap-name">Jason Heavey< /div>< div class="nap-source">(< a href="/tipster/sunday-express.php">Sunday Express< /a>)< /div>< /div>< div class="clearfix">< /div>< /div>< /div>< /td>< td>5/8< /td>< td>7/4< /td>< td class="runner-td odds-td">< a class="nap-odds" href="/kempton-results/17th-march-2018/1-30.php">Result< /a>< /td>< /tr>

< tr class="dog-list-item">< td class="runner-td silk-td">< img class="lazy" src="/images/silks/32976.gif"/>< /td>< td class="runner-td nap-td">< a class="runner-name" href="/horses/grapevine.php">Grapevine< /a>< /td>< td class="runner-td list-naps-td tipster-td">< div class="nap-list">< div class="nap-item">< div class="nap-left">< div class="nap-name">Sirius< /div>< div class="nap-source">(< a href="/tipster/the-sun-on-sunday.php">The Sun On Sunday< /a>)< /div>< /div>< div class="clearfix">< /div>< /div>< /div>< /td>< td>5/8< /td>< td>7/4< /td>< td class="runner-td odds-td">< a class="nap-odds" href="/kempton-results/17th-march-2018/1-30.php">Result< /a>< /td>< /tr>

What I would like to do it loop through all the tr and td a arrange them into a table. I have the below code but I am struggling to get the two fields below:

td>5/8< /td>< td>7/4< /td>

Pen Profiles
Private Sub CommandButton1_Click()
    Dim IE As Object
    Dim dd As String
    Dim NAP As IHTMLElement
    Dim NAPS As IHTMLElementCollection
    Dim NAPtd As IHTMLElementCollection
    Dim i As Long, a As Long

    
On Error GoTo error5
    
    With Sheet1
    
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False
    
    IE.Navigate "http://racing.betting-directory.com/naps/17th-march-2018.php"

    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
    
    Set NAPS = IE.Document.getElementsByClassName("dog-list-item")
    i = 0
    For Each NAP In NAPS

    
    If NAP.className = "dog-list-item" Then
    
    
    .Cells(i + 2, 1).Value = IE.Document.getElementsByClassName("runner-name")(i).innerText
    .Cells(i + 2, 2).Value = IE.Document.getElementsByClassName("nap-name")(i).innerText
    .Cells(i + 2, 3).Value = IE.Document.getElementsByClassName("nap-source")(i).innerText
    .Cells(i + 2, 4).Value = IE.Document.getElementsByTagName("td")(51).innerText
    .Cells(i + 2, 5).Value = IE.Document.getElementsByTagName("td")(52).innerText
    
    'dd = IE.Document.DocumentElement.innerText

    'Debug.Print dd
    i = i + 1
    End If
    Next NAP

    Set IE = Nothing
On Error GoTo 0
End With
    Exit Sub
    
error5:

End Sub

I would like to loop through all the row td and column tr in the HTML. Also if possible I would also like to return the .php link at the end of the row such as below.

<td class="runner-td odds-td"><a class="nap-odds" href="/kempton-results/17th-march-2018/1-30.php">Result</a></td>

The code I have provided does work but not full and I can do with some help as it driving me mad.

Thank you

John

 

[daverunt]

Hi John,

don't know what you have done to your post but its like a Russian doll of code tags. I have to scroll through an inch high box to read the post.


This should get the php link:

.Cells(i + 2, 6).Value = IE.Document.getElementsByClassName("runner-name")(i)

Kindly post any other requests again so other people can see it.