Thanks Thanks:  0
Likes Likes:  0
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 41

Thread: Excel vba scraper - guidance part II

  1. #1
    New Member
    Join Date
    Oct 2012
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel vba scraper - guidance part II

    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.


    Thank you in advance.
    Last edited by Anka; Jan 6th, 2017 at 11:49 AM.

  2. #2
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    2,834
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel vba scraper - guidance part II

    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).

  3. #3
    New Member
    Join Date
    Oct 2012
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel vba scraper - guidance part II

    Thank you for your attention, Sektor.
    Can you make an example. I'm a newbie in this...

  4. #4
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    2,834
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel vba scraper - guidance part II

    Please, read the private message.

  5. #5
    New Member
    Join Date
    Oct 2012
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel vba scraper - guidance part II

    Unfortunately Sektor can not help, the site is banned in Russia...

  6. #6
    New Member
    Join Date
    Oct 2012
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel vba scraper - guidance part II

    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

  7. #7
    Board Regular Ombir's Avatar
    Join Date
    Oct 2015
    Location
    India
    Posts
    421
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel vba scraper - guidance part II

    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.
    I don't care what you think of me! Unless you think I'm awesome in which case, you're right! Carry on

  8. #8
    New Member
    Join Date
    Oct 2012
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel vba scraper - guidance part II

    Thanks for trying, Ombir.

    I think that must to be something similar with this.

    pgc01 - http://www.mrexcel.com/forum/excel-q...into-cell.html

    John_w - http://www.mrexcel.com/forum/excel-q...preciated.html

    And the odds, i think, have to be extracted from the ajax page.

  9. #9
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,067
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel vba scraper - guidance part II

    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

  10. #10
    New Member
    Join Date
    Oct 2012
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel vba scraper - guidance part II

    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.








Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •