[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
    Phone = Range("A1")
    http.Open "GET", "http://www.whitepages.com/phone/1-" & Phone, False
    html.body.innerHTML = http.responseText
    Set catgo = html.getElementsByClassName("bottom-padding-large grey-text")(0)
    Set raw = catgo.getElementsByTagName("p")
    For Each data In raw
    ActiveCell.Value = data.innerText
    ActiveCell.Offset(1, 0).Select
    Next data
    End Sub

  • administrators

    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.

Log in to reply

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