[Solved] Can't Perform a reverse web lookup to get name against a phone number using vba
I've written a code to get the "name of the coffee shop" against a phone number embedded adjacent to a cell in my excel spreadsheet. My intention is to get the name using the phone number from that page. The phone number against which i am trying to get the name is "323-871-0143". But i'm not being able to grab it. For kind consideration i'm pasting my code below. The code doesn't show any error but to know more about it i used python module to get text of that page but it was unable to get that too. Finally my finding is that i am unable to reach that page using my code where the data belongs because of privacy error or something. I can reach that page only when i write manually the address in the chrome address bar. So my earnest request is that if i could get any helping hand to perform it the way i can get the result. Any help would be greatly appreciated.
Sub ReverseLookup() Dim http As New MSXML2.XMLHTTP60, html As New MSHTML.HTMLDocument Dim catgo As Object, data As Object, raw As Object Dim Phone As String Worksheets("sheet2").Select Phone = Range("A1") http.Open "GET", "http://www.whitepages.com/phone/1-" & Phone, False http.send html.body.innerHTML = http.responseText Set catgo = html.getElementsByClassName("bottom-padding-large grey-text")(0) Set raw = catgo.getElementsByTagName("p") Range("B1").Select For Each data In raw ActiveCell.Value = data.innerText ActiveCell.Offset(1, 0).Select Next data End Sub
That website uses sessions to avoid data scraping from simple HTTP requests. When request is first made from a browser it sets a session cookie and redirects to another URL in this format - http://www.whitepages.com/search/ReversePhone?utf8=✓&country_code=1&iso_code=us&full_phone=1-323-871-0142 which then redirects back to the original page with data based on session.
You have to either replicate the same in HTTP requests by reading cookies, setting cookies and handle redirection or use an actual browser.
Need a simple solution? Use Selenium.
You are always the best mentor i have come across so far. Thanks for your kind reply and helpful guidance.
By the way, i'm starting another thread to get a simple solution from you which is about making my vba code wait to grab web data each time.