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 ony 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 MSXML2.XMLHTTP60, html As New HTMLDocument
    Dim sth As String, mth As String, z As String
    Dim cg As Object, tar As Object, mj As Object
    Dim x As Long, lrow As Long, tac As Object

    lrow = Range("A" & Rows.Count).End(xlUp).Row
    x = 2
    For x = 2 To lrow
    z = Cells(x, 1)
    sth = Cells(x, 2)
    mth = Replace(sth, " ", "-")

    http.Open "GET", "" & 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

    x = x + 1
    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

  • This post is deleted!

  • This post is deleted!

  • This 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.

Log in to reply

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.