Help downloading specific data from a webpage!

[danssainz]

Hi everyone,

Could somebody help me out with a very small problem that I have. I am simply trying to grab some data from a particular website, a fantasy football website. I don't want to use the functionality in Excel of adding a connection to a webpage, because this downloads everything on the website and I find it not exact and highly unreliable. I simply want to grab the table of data, in the link below. If someone has a code to do this I don't know if it's JavaScript or VBA I would be really grateful. The link is below and this information updates weekly as games are played.

http://fantasy.premierleague.com/stats/elements/?element_filter=te_4&stat_filter=total_points

I have attached a picture to give you an idea of what the data looks like.

I am using excel 2010.


Thank you so much!

[Kyle123]

Here you go

Sub GetData()
    Dim x As Long, y As Long
    Dim htm As HTMLDocument

    Set htm = CreateObject("htmlFile")

    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "http://fantasy.premierleague.com/stats/elements/?element_filter=te_4&stat_filter=total_points", False
        .send
        htm.body.innerhtml = .responsetext
    End With

    With htm.getElementsByTagName("table")(0)
        For x = 0 To .Rows.Length - 1
            For y = 0 To .Rows(x).Cells.Length - 1
                Sheets(2).Cells(x + 1, y + 1).Value = .Rows(x).Cells(y).innertext
            Next y
        Next x
    End With

End Sub

[danssainz]

Thanks Kyle for the fast response and code! I'll test this out once I get back to my PC shortly.

[danssainz]

I'm guessing I need the microsoft dao 3.6 object library?
However I am getting a message:
error in loading dll
when I am adding the library in the reference.

[Kyle123]

No, you don't need DAO library, for that code you'd have needed the Microsoft HTML Object Library, the code below shouldn't need any

Sub GetData()
    Dim x As Long, y As Long
    Dim htm As Object
 
    Set htm = CreateObject("htmlFile")
 
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "http://fantasy.premierleague.com/stats/elements/?element_filter=te_4&stat_filter=total_points", False
        .send
        htm.body.innerhtml = .responsetext
    End With
 
    With htm.getElementsByTagName("table")(0)
        For x = 0 To .Rows.Length - 1
            For y = 0 To .Rows(x).Cells.Length - 1
                Sheets(2).Cells(x + 1, y + 1).Value = .Rows(x).Cells(y).innertext
            Next y
        Next x
    End With
 
End Sub

[danssainz]

Hi Kyle,
That is brilliant, that works perfectly! Thanks so much for creating the code and persisting.
I will be able to pull off so much data with this code and a loop. I can't believe it. Thanks again
Daniel

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.