[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
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", "http://www.yellowpages.com/los-angeles-ca/mip/" & sth & "-" & z & "?lid=" & z, False http.send 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 -----
@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.waitfor 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.
This post is deleted!
This post is deleted!
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.