XMLHTTP Data extract in excel - unable to extract the correct data

Question

My VBA knowledge is limited and I am trying hard to learn but I am stuck with the code below. My issue is that I am trying extract the results table on this webpage which contained in the table class "Standard_tabelle".

Sub seasonres()

Sheets("Vs").Select
Range("A1").Select
Dim url As String, links_count As Integer
Dim i As Integer, j As Integer, row As Integer
Dim XMLHTTP As Object, html As Object
Dim tr_coll As Object, tr As Object
Dim td_coll As Object, td As Object

  url = "http://www.worldfootball.net/teams/manchester-united/2011/3/"

    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.send

    Set html = CreateObject("htmlfile")
    html.body.innerHTML = XMLHTTP.ResponseText

    Set tbl = html.getelementsbytagname("Standard_tabelle")

    Set tr_coll = tbl(0).getelementsbytagname("TR")

    For Each tr In tr_coll
        j = 1
        Set td_col = tr.getelementsbytagname("TD")

        For Each td In td_col
            Cells(row + 1, j).Value = td.innerText
            j = j + 1
        Next
        row = row + 1
    Next
End Sub

but the code below keeps giving me a run-time error and when I debug it points to:

Set tr_coll = tbl(0).getelementsbytagname("TR")

Is there someone who can help me correct this code to extract the table or show me another code that would help with this?

Many thanks in advance. Shahid

Answer 1

I think maybe htmlfile doesn't support getElementsByclassname...

Try this work-around

Sub seasonres()

    Dim url As String, links_count As Integer
    Dim i As Integer, j As Integer, row As Integer
    Dim XMLHTTP As Object, html As Object
    Dim tr_coll As Object, tr As Object
    Dim td_coll As Object, td As Object, tbl, td_col, objT

    url = "http://www.worldfootball.net/teams/manchester-united/2011/3/"

    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.send

    Set html = CreateObject("htmlfile")
    html.body.innerHTML = XMLHTTP.ResponseText

    Set tbl = html.body.getelementsbytagname("table")

    For Each t In tbl
        If t.classname = "standard_tabelle" Then
            For Each tr In t.getelementsbytagname("TR")
                j = 1
                For Each td In tr.getelementsbytagname("TD")
                    Cells(row + 1, j).Value = td.innerText
                    j = j + 1
                Next
                row = row + 1
            Next
            row = row + 3
        End If
    Next


End Sub