getting Data from a page that doesn't show table

Question

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

and what if I got an iFrame widget from a website, there's this website that offers exactly what I need but to get Xml feed it's paid. They do offer tho an iframe showing all the data that I need for free; would I be able to import that to excel theoretically? I know I would have to set it up on a website first... — Issy the kid, Mar 03 '18 at 18:58
It would take some time to get it because I would need to apply for it, so for now I'm only interested in one of the above website. Do you think it's possible? — Issy the kid, Mar 03 '18 at 21:52
It is certainly possible for your first link. You just have to do some fixing on the odds values that excel recognises as dates. You can loop the output to change the dates back to odds with If Not IsEmpty(currCell) And InStr(1, currCell.Text, "/") = 0 And currCell.Row > 2 Then Dim tempString As String tempString = Format$(currCell.Text, "yyyy-mm-dd") Debug.Print Right$(tempString, 2) & "/" & Mid$(tempString, 6, 2) . Where currCell.Row > 2, 2 happens to be the header table row — QHarr, Mar 03 '18 at 22:42
@QHarr Thx a lot, I'm not in a rush right now so take your time. I've also watched the tutorials you referenced so I'll try to implement it as well and compare it perhaps with whatever you got :) — Issy the kid, Mar 03 '18 at 23:22

QHarr · Accepted Answer · 2018-03-04T17:57:26.443

0

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

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:

edited Mar 04 '18 at 17:57

answered Mar 03 '18 at 19:01

QHarr

83,427
12
54
101

Thank you so much QHarr, but I'm getting a runtime error 438 when I run it on the line Set allRowOfData = html.getElementsByClassName("at-12 standard-list")(0) It is weird because I've been getting the same error when I tried to run my script to get element by class name instead of tag name. Any thoughts? – Issy the kid Mar 04 '18 at 12:53
I also couldn't find the class name you referenced so that I can try to change it – Issy the kid Mar 04 '18 at 13:02
Are you using "https://www.oddschecker.com/tennis/kaitlyn-christian-sabrina-santamaria-v-tatjana-maria-heather-watson/winner" – QHarr Mar 04 '18 at 13:16
Yes I am, I'm not really sure what I'm missing. I copied the exact same code as you wrote, just changed to another oddschecker Url coz that one expired. I could be missing a functionality or something like that in Excel maybe? – Issy the kid Mar 04 '18 at 13:27
That URL hasn't expired. I just tried it. – QHarr Mar 04 '18 at 17:03
I'm really not sure! Any idea why I'm getting the run time error? – Issy the kid Mar 04 '18 at 17:37
I have included image of both references (set-up) and results in sheet – QHarr Mar 04 '18 at 17:57
Thank you so much for your help! It's still not working for the moment but it's probably something I'm doing wrong so I'll try to fix it! – Issy the kid Mar 04 '18 at 18:47
What version of Excel are you using? XMLHTTP60 is for my version. Do you have option explicit at the top of the module (i'm guessing yes if using my code as is) – QHarr Mar 04 '18 at 19:04
I do have XML Version 6.0 so that part is good, I do have the option explicit at the top but for some reason it create a line under it as if it was a seperate sub. you can see that in the pictures I just uploaded. – Issy the kid Mar 04 '18 at 19:11
that is normal. It goes at the very top. Has a line under it. All normal. – QHarr Mar 04 '18 at 19:15

getting Data from a page that doesn't show table

1 Answers1