Search code examples
jqueryexcelvbaexcel-web-query

getting Data from a page that doesn't show table

I'm trying to get live Data from a website into my Excel spreadsheets.The only problem is when I go to the webpage via Get Data in Excel, the table is not recognizable. I'm only able to import the whole page, and even then it does not get the Data I need. I would like to run a Macros to get me the Data if provided the Url, since there are too many pages to just do it manually.

The Data I'm trying to import is all the odds from different bookmakers for comparisons sake and this is an example https://www.oddschecker.com/tennis/kaitlyn-christian-sabrina-santamaria-v-tatjana-maria-heather-watson/winner

Or this website https://easyodds.com/tennis/atp/acapulco-mexico/928015/match-result Either one would work

I would really appreciate some insight into this. Can anyone help please!!

It's really weird why your solution is not working for me:

enter image description here enter image description here

Solution

  • Ok. Here is a solution for your first link.

    I could tidy up output a bit but it gets you started.

    With an activesheet selected and references added to MS XML and MS HTML Library.

    References

    References

    Code

    Option Explicit

Sub test4()

    Dim html As New HTMLDocument, http As New XMLHTTP60

    With http
        .Open "GET", "https://www.oddschecker.com/tennis/kaitlyn-christian-sabrina-santamaria-v-tatjana-maria-heather-watson/winner", False
        .Send
        html.body.innerHTML = .ResponseText
    End With

    Dim allRowOfData As Object

    Set allRowOfData = html.getElementsByClassName("at-12 standard-list")(0)

    Dim r As Long, c As Long

    Dim curHTMLRow As Object

    Dim counter As Long
    counter = 1

    For r = 0 To allRowOfData.Rows.Length - 1

        Set curHTMLRow = allRowOfData.Rows(r)

        Dim tblCell As HTMLTableCell

        For Each tblCell In curHTMLRow.Cells

          If Not tblCell.innerText = "1" And Not tblCell.innerText = "2" And Len(tblCell.innerText) > 0 Then
           ActiveSheet.Range("A" & counter).Value = Application.WorksheetFunction.Substitute(tblCell.innerText, "/", " over ")
           counter = counter + 1
          End If

        Next tblCell

    Next r

End Sub

    Results:

    Results