Excel vba scraper - guidance part II

Anka

New Member
Joined
Oct 20, 2012
Messages
45
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hi all,

I'm start from here , but seems that was the easiest part...
In the second part, I try to take the data from all rows that corresponding with the Bet365 bookmaker (for example), from this page.

I'm able, do that but my vba is very slow.
Is there a way to do the task without IE as I have hundreds of similar links and using ie is slower?
I need to use another method to do this job.
This is an example of link that I need to scrape.
How difficult is to scrape ajax sites because this site have this kind of pages (just for the odds)?

And the final result have to be something like this.
image.jpg


Thank you in advance.
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Well, ajax response is JSON which holds HTML page with escaped characters (so that JSON would be valid).
You need combination of XMLHTTP60 (Microsoft XML, v6.0 library) and HTMLDocument (Microsoft HTML Object Library).
 
Upvote 0
Thank you for your attention, Sektor.
Can you make an example. I'm a newbie in this...
 
Upvote 0
Please, read the private message.
 
Upvote 0
Unfortunately Sektor can not help, the site is banned in Russia...
 
Upvote 0
I found here some examples XMLHTTP60 or JSON, made by John_w, pgc01, Ombir, etc.
It is very difficult for a newbie like my, to understand how to make or adapt this to my needs.
But is so hard to modify my code to an XMLHTTP60 ??
This is my code and work fine but is slow.
Code:
Sub GrabUoOdds1()

    Dim objIE As InternetExplorer
    Dim ele As Object
    Dim y As Integer
    Dim URL As Range
    Dim LR As Long, c As Range

    Sheets("Uo_Odds").Activate
    With ActiveSheet
    
    LR = Sheets("MatchDetails").Cells(Rows.Count, "B").End(xlUp).row
    For Each URL In Sheets("MatchDetails").Range("D2:D" & LR)
    On Error GoTo ErrorHandler1
    Set objIE = New InternetExplorer

    objIE.Visible = True
    
    objIE.navigate URL

    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
    
    For Each ele In objIE.document.getElementById("odds-all"). _
      getElementsByTagName("tr")
      If ele.Children(0).innerText Like "bet365" Then

        Debug.Print ele.innerText

        Sheets("Uo_Odds").Range("H" & i).Value = ele.Children(0).innerText

        Sheets("Uo_Odds").Range("I" & i).Value = ele.Children(3).innerText

        Sheets("Uo_Odds").Range("J" & i).Value = ele.Children(4).innerText

        Sheets("Uo_Odds").Range("K" & i).Value = ele.Children(5).innerText

        aa = objIE.document.getElementsByClassName("wrap-section__header__title")(0).innerText
        Sheets("Uo_Odds").Range("B" & i).Value = aa
        ab = objIE.document.getElementById("match-date").innerText
        Sheets("Uo_Odds").Range("C" & i).Value = ab
        ac = objIE.document.getElementsByTagName("h2")(0).innerText
        Sheets("Uo_Odds").Range("D" & i).Value = ac
        ad = objIE.document.getElementsByTagName("h2")(2).innerText
        Sheets("Uo_Odds").Range("E" & i).Value = ad
        ae = objIE.document.getElementById("js-score").innerText
        Sheets("Uo_Odds").Range("F" & i).Value = ae
        af = objIE.document.getElementById("js-partial").innerText
        Sheets("Uo_Odds").Range("G" & i).Value = af
        aw = objIE.document.getElementsByClassName("list-breadcrumb__item")(2).innerText
        Sheets("Uo_Odds").Range("A" & i).Value = aw
        i = i + 1
        End If
ErrorHandler1:
    Resume Next
    Next
    objIE.Quit
    Set objIE = Nothing
    Next
    End With
    Range("A:aa").WrapText = False

End Sub
 
Upvote 0
I'm not so familiar with XMLHTTP object but HTTP request is getting failed due to # symbol in your url.

Get request in XMLHTTP method is converting your # symbol into percent encoding form i.e %23. Hopefully someone else can guide you with this.
 
Upvote 0
Your code is slower than it could be because it creates and destroys the InternetExplorer object for every URL. Create the IE object once and reuse it for every URL by moving these lines outside the outer loop:

Code:
    Set objIE = New InternetExplorer

':
':
    objIE.Quit
    Set objIE = Nothing
 
Upvote 0
Thank you very much for your intervention, John_w.
I have followed your advice and I did a test with 100 links (I have many more).
But unfortunately, in terms of time, there is no difference.







 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top