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