How can I select a single table from a webpage?

[shahin]

Although, I have already got guidance from this forum as to how I can parse table data from webpage, I would like to know how can I select a specific table out of 8 from a certain webpage using this bit of code. Thanks in advance for taking this into consideration.

Sub TableData()
Dim xmlpage As New XMLHTTP60
Dim htmldoc As New MSHTML.HTMLDocument
Dim tbl As Object, tRow As Object, tCel As Object

With xmlpage
    .Open "GET", "https://fantasy.premierleague.com/player-list/", False
    .send
    htmldoc.body.innerHTML = .responseText
End With

For Each tbl In htmldoc.getElementsByTagName("table")
    For Each tRow In tbl.getElementsByTagName("tr")
        For Each tCel In tRow.getElementsByTagName("td")
            c = c + 1
            Cells(x, c) = tCel.innerText
        Next tCel
        c = 0
        x = x + 1
    Next tRow
Next tbl
End Sub

Btw, the above code if executed gets the data of all tables available out there.

 

[shahin]

If I give my above code a little twitch, it then only parses the first table. In case of second, third, I meant indexing of table, I can't get any idea.

Sub TableData()
Dim xmlpage As New XMLHTTP60
Dim htmldoc As New MSHTML.HTMLDocument
Dim tbl As Object, tRow As Object, tCel As Object

With xmlpage
    .Open "GET", "https://fantasy.premierleague.com/player-list/", False
    .send
    htmldoc.body.innerHTML = .responseText
End With

For Each tbl In htmldoc.getElementsByTagName("table")
    For Each tRow In tbl.getElementsByTagName("tr")
        For Each tCel In tRow.getElementsByTagName("td")
            c = c + 1
            Cells(x, c) = tCel.innerText
        Next tCel
        c = 0
        x = x + 1
    Next tRow
    GoTo end_of_for
Next tbl
end_of_for:
End Sub

 

[shahin]

Bingo!!! Found the solution already. It's really cool to parse any table using Item property as index. The demo is for second table for that page:

Sub TableData()
Dim xmlpage As New XMLHTTP60
Dim htmldoc As New MSHTML.HTMLDocument
Dim tbl As Object, tRow As Object, tCel As Object

With xmlpage
    .Open "GET", "https://fantasy.premierleague.com/player-list/", False
    .send
    htmldoc.body.innerHTML = .responseText
End With

Set tbl = htmldoc.getElementsByTagName("table").Item(2)
    For Each tRow In tbl.getElementsByTagName("tr")
        For Each tCel In tRow.getElementsByTagName("td")
            c = c + 1
            Cells(x, c) = tCel.innerText
        Next tCel
        c = 0
        x = x + 1
    Next tRow
End Sub

 

[Marc L]

Hi !

As Item(2) means third table …
​

 

[shahin]

Yeah Marc L, right you are. It always starts from 0 as the first count. However, I found the above method very helpful. I have learned the usage of "Item" from you, though!

 

[shahin]

Another way to do the same. Code for scraping first table.

Sub Scrape_premierleague_com()
Dim sResponse As Variant

With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", "https://fantasy.premierleague.com/player-list/", False
    .send
    sResponse = .responseText
End With
sResponse = Split(Split(sResponse, "<tbody>")(1), "</tbody>", 2)(0) ' First table
aRows = Split(sResponse, "<tr>")
For j = 1 To UBound(aRows)
    aCells = Split(aRows(j), "<td>")
    For i = 1 To UBound(aCells)
        Cells(j, i).Value = Split(aCells(i), "</td>", 2)(0)
    Next i
Next j
End Sub