[Solved] How could i make my VBA application wait until a web page is loaded, and exactly where (between my code) to put that line?

  • Below are the list of docs against which I want to get address and phone number. First item in the row is for Range("A2") and second one for Range("B2") and the result should be placed respectively in cell C3 and D3. My code is working fine but the problem is that i don't know how to make vba wait for a certain time until page is loaded that is why it only grasps half of the docs. I hope i have made myself clear.
    Here is the code.

    2502051 The Apple Pan
    10015814 Trimana
    460207740 Good Microbrew Grill
    451379748 The Coffee Bean Tea Leaf
    460619922 Prime Grind Coffee
    460705774 Tom N Toms
    513420432 Blue Bottle Coffee
    482833585 Transcendent Cafe


    Sub GrabbingData()
    Dim http As New ServerXMLHTTP60, html As New HTMLDocument
    Dim sth As String, mth As String, z As String
    Dim cg As Object, tar As Object, mj As Object
    lrow = Range("A" & Rows.Count).End(xlUp).Row
        For x = 2 To lrow
        z = Cells(x, 1)
        sth = Cells(x, 2)
        mth = Replace(sth, " ", "-")
        http.Open "GET", "http://www.yellowpages.com/los-angeles-ca/mip/" & sth & "-" & z & "?lid=" & z, False
        html.body.innerHTML = http.responseText
        Set cg = html.getElementsByClassName("address")
        Set mj = html.getElementsByClassName("phone")
            For Each tar In cg
            Cells(x, 3).Value = tar.innerText
                For Each tac In mj
                    Cells(x, 4).Value = tac.innerText
                Next tac
            Next tar
        Next x
    End Sub

  • administrators

    @shahin2137 There's nothing such as wait in HTTP request. You make a request and you get a response. HTTP client gives you only one response which is complete.

    You'll get same response even if you add application.wait for one hour. The missing data that you're looking for might be rendered on the client side. detailed explanation here - Best practices of scraping website data

  • Perhaps it is my fault that i could not make you understand what is happening when i run the code. The fact is, if i have 10 rows with lookup material (i meant, lid number and name) then i get 5 alternate rows filled in with result. Then again if i put one of the blank row, that was skipped first time, on the top and run the code then it fills the rest. I tried with the wait method but found the same result as you said. Anyways, to get the full result i have to run the code twice. And at the time of second run i need to make sure whether the row with no result is on the top. This is definitely a flaw which i have made somewhere in my code. This is it. I get inspired when i get any comment from your end. Thanks again.

  • post_is_deleted

  • post_is_deleted

  • @ranjithkumar10
    Bingo!!! Found it. I made a for next loop and mistakenly increment it one more time by using x=x+1. Removing the line i got the result i expected.