• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to execute my script successfully without using hardcoded wait?

shahin

shahin

Active Member
I've written some code in vba in combination with IE. The code I've pasted below is working great. However, it works as long as I keep the "Application.Wait (Now + TimeValue("0:00:05"))" line how it is. I was thinking of executing the code successfully without using that hardcoded delay in my script. Any loop or something will do instead.

Code: 
Sub Get_Result()
    Dim IE As New InternetExplorer, html As HTMLDocument
   
    With IE
        .Visible = False
        .navigate "https://greenbet.info/en/display/fortune"
        Do Until .readyState = READYSTATE_COMPLETE: Loop
        Set html = .document
    End With
   
    Application.Wait (Now + TimeValue("0:00:05"))  ''wanted to kick this out

    For Each posts In html.getElementsByClassName("fortune_roulette") 
        row = row + 1: Cells(row + 1, 1) = posts.getElementsByClassName("fortune_round__num")(0).innerText
        Cells(row + 1, 2) = posts.getElementsByClassName("second_digit")(0).innerText
        Cells(row + 1, 3) = posts.getElementsByClassName("first_digit")(0).innerText
    Next posts
End Sub
 
If it works, go for it. I am not sure that it adds much here though as the code does seem to wait due to the loop.

For that site, I am thinking that you would want an infinite loop. e.g. Do Until 1=2: 'routine: Loop. The loop would check that the values don't equal the last ones. It would also check only when the progressbar value was 100.

Of course you need a way to exit the loop. Escape key is the usual way to gracefully exit an infinite loop. https://msdn.microsoft.com/en-us/library/aa214566(office.11).aspx
 
If you are waiting for some specific event that can be measured using Excel in some fashion, Then you can use

Do Until "Some event happens": Loop

As you have already done in your code

Otherwise you may be stuck with the Wait approach
 
shahin said:
I was thinking of executing the code successfully without using that hardcoded delay in my script.
Click to expand...
As always, just inspect / observe the webpage during its loading and
until completed : what element is particular to your goal …

Once this element is found, use statements like Is Nothing or IsObject
in order to check it. See both in this thread …
​
 
Thanks Marc L, for the link. You suggested me once to use IE if it is necessary to scrape documents from any javascript injected sites but i didn't listen to you then. I always tried to avoid any sort of usage of IE when it comes to work with javascript; rather, I was stick to selenium but now I understand how wrong I was. IE is just awesome!!! However, this is the only problem (hardcoded delay) that bothers me. I saw the code (written by you) following your provided link. It looks nice but a bit difficult for me to understand because I'm not familiar with this greater than or less than part yet "While .ReadyState < 4: DoEvents: Wend"
 
In 90% of case, checking for .readySate alone is fine. However, in some cases you will also need to check for .Busy as well.

So I mostly use following code when I need to wait for IE to finish loading.
Code: 
While Browser.readyState <> 4 Or Browser.Busy: DoEvents: Wend
Browser is object holding IE instance.

You can test the difference in result by scraping for Lat & Long on Google map.

See attached. Note that you may not see the difference depending on your set up in small sample provided. You may need to add more location to search and increase iMax to appropriate value in the code.

It's hard coded, since this was used to loop through 36000+ address and did it in 5000 chunks at a time. Looping through 5000 address does take few hours ;)

The code is in Module1 (Sub LatLong).
 

Attachments

  • Pilot_IE_Google_Sample.xlsb
    25.3 KB · Views: 3
Last edited:
Today I tried with another site to check whether my script is able to get all the data from that webpage. What I could find out is that it gets 90 percent data from there if a Hardcoded delay is applied in my script. I could not catch anything without this line "Sleep 6000".
Code: 
Private Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds&)
Sub TableData()
    Dim ie As New InternetExplorer, iedoc As HTMLDocument
    Dim post As Object

    With ie
        .Visible = False
        .navigate "https://www.google.it/flights/?pli=1#search;f=BLQ,IBT;t=FCO;d=2017-11-01;r=2017-11-14;eo=e"
        While ie.readyState <> 4 Or ie.Busy: DoEvents: Wend
        Set iedoc = .document
    End With

    Sleep 6000
  
    For Each post In iedoc.getElementsByClassName("DQX2Q1B-d-X")
        With post.getElementsByClassName("DQX2Q1B-d-Ab")
            If .Length Then row = row + 1: Cells(row, 1) = .item(0).innerText
        End With
        With post.getElementsByClassName("DQX2Q1B-d-Cb")
            If .Length Then Cells(row, 2) = .item(0).innerText
        End With
        With post.getElementsByClassName("DQX2Q1B-d-Zb")
            If .Length Then Cells(row, 3) = .item(0).innerText
        End With
        With post.getElementsByClassName("DQX2Q1B-d-j")
            If .Length Then Cells(row, 4) = .item(0).innerText
        End With
        With post.getElementsByClassName("DQX2Q1B-d-E")
            If .Length Then Cells(row, 5) = .item(0).innerText
        End With
        With post.getElementsByClassName("DQX2Q1B-d-Ib")
            If .Length Then Cells(row, 6) = .item(0).innerText
        End With
        With post.getElementsByClassName("DQX2Q1B-d-Qb")
            If .Length Then Cells(row, 7) = .item(0).innerText
        End With
        With post.getElementsByClassName("DQX2Q1B-d-Z")
            If .Length Then Cells(row, 8) = .item(0).innerText
        End With
    Next post
  
    ie.Quit
End Sub
 
After experimenting a lot I could find out that with adding a particular loop in my script, the explicit wait with fixed timer can be avoided to get the result.

Code: 
Sub roulette()
    Dim ie As New InternetExplorer, iedoc As HTMLDocument

    With ie
        .Visible = False
        .navigate "https://greenbet.info/en/display/fortune"
        Do Until .readyState = READYSTATE_COMPLETE: Loop
        While .readyState < 4:  DoEvents:  Wend  ''with adding this line i get the result every time i run it without hradcoded delay
        Set iedoc = .document
    End With

    For Each posts In iedoc.getElementsByClassName("fortune_roulette")
        Row = Row + 1: Cells(Row, 1) = posts.getElementsByClassName("fortune_round__num")(0).innerText
        Cells(Row, 2) = posts.getElementsByClassName("second_digit")(0).innerText
        Cells(Row, 3) = posts.getElementsByClassName("first_digit")(0).innerText
    Next posts

    ie.Quit
End Sub

Btw, it always fetches results in the first run. So, to encounter the problem (what I was facing earlier) it is necessary to execute the code more than once.
 
I could not refrain myself from posting this. It is impossible to reach out the quote data from that webpage (supplied within my scraper) without using hardcoded delay. However, the alternative approach which I've come across lately is just like below:
Code: 
Sub Get_Quote()
    Dim IE As New InternetExplorer, HTML As HTMLDocument
    Dim post As Object

    With IE
        .Visible = True
        .navigate "https://www.nyse.com/quote/XNYS:A"
        While .Busy = True Or .readyState < 4: DoEvents: Wend
        Set HTML = .document
    End With
    ''this is the fix
     Do: Set post = HTML.getElementsByClassName("d-dquote-x3")(0): DoEvents: Loop While post Is Nothing
    [A1] = post.innerText
    IE.Quit
End Sub

The output (at this moment):
Code: 
72.86
 
You must log in or register to reply here.
Back
Top