Is there a piece of code which will pull an address out from a Google Search

Question

So I am trying to get the address of a company when you search for [Company name] + "Address" on google. I can get the "About 20,500 results (0.49 seconds)" no issue because the div id is always resultStats however, for the address its a HTML Class that is called: Z0LcW. Not sure if it changes.

I am trying to confirm that the trading addresses provided by a potential supplier are valid. Its for 4k addresses and i dont want to have to manually verify it.

The idea came from: Using VBA in Excel to Google Search a keyword and return indexed pages on google

I have looked at these guys who were very helpful conceptually: Using getElementsByClassName in Excel VBA

Dim XMLHTTP As Object, html, ObjResultdiv As Object
Dim start_time As Date
Dim end_time As Date

lastRow = Range("A" & Rows.Count).End(xlUp).Row

Dim cookie As String
Dim result_cookie As String

start_time = Time
Debug.Print "start_time:" & start_time

For i = 1 To lastRow

    url = "https://www.google.co.uk/search?q=" & Cells(i, 1) & "&rnd=" & WorksheetFunction.RandBetween(1, 10000)

    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
    XMLHTTP.send

    Set html = CreateObject("htmlfile")
    html.body.innerHTML = XMLHTTP.responseText

    'Debug.Print XMLHTTP.ResponseText


If html.getElementsBytagname("Z0LcW") Is Nothing Then
    str_text = "0 Results"
Else
    str_text = html.getElementsBytagname("Z0LcW")
End If
    Cells(i, 2) = str_text
    DoEvents
Next

end_time = Time
Debug.Print "end_time:" & end_time

Debug.Print "done" & "Time taken : " & DateDiff("n", start_time, end_time)
MsgBox "done" & "Time taken : " & DateDiff("n", start_time, end_time)

The answer should be: 31B, Avenue One, Station Ln, Witney OX28 4XZ

https://www.google.co.uk/search?sxsrf=ACYBGNQ6p6lh1ZFfS6dGwsDcg45wKgmICw%3A1569864526381&ei=TjuSXaPnFvHC8gL81a2gCg&q=beadlight+ltd+address&oq=beadlight+ltd+address&gs_l=psy-ab.3...1272.2467..2622...0.0..0.85.635.8......0....1..gws-wiz.......0j0i22i30j33i160j33i21.NErA1FvTnr8&ved=0ahUKEwijxsKLifnkAhVxoVwKHfxqC6QQ4dUDCAs&uact=5

score 1 · Accepted Answer · answered Sep 30 '19 at 22:12

Added a reference to Microsoft HTML Object Library so we can Dim a HTMLDocument and use the methods available there.

"Z0LcW" is a class so I used getElementsByClassName("Z0LcW") to return the results instead of getElementsByTagName.

Once we actually get the result we can use the .innerText property of the element to get the text value. I'm not a whiz at classes and tags, so be aware there may be duplicates at some point so you might need to loop through a collection of elements later on.

Dim XMLHTTP As Object, ObjResultdiv As Object
Dim start_time As Date
Dim end_time As Date
Dim html As New HTMLDocument

lastRow = Range("A" & Rows.Count).End(xlUp).Row

Dim cookie As String
Dim result_cookie As String

start_time = Time
Debug.Print "start_time:" & start_time

For i = 1 To lastRow

    Url = "https://www.google.co.uk/search?q=" & Cells(i, 1) & "&rnd=" & WorksheetFunction.RandBetween(1, 10000)

    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
    XMLHTTP.Open "GET", Url, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
    XMLHTTP.send

    'Set html = CreateObject("htmlfile") 'Don't need this as we early bind an HTMLDocument with the methods we need
    html.body.innerHTML = XMLHTTP.responseText

    'Debug.Print XMLHTTP.ResponseText


If html.getElementsByClassName("Z0LcW") Is Nothing Then
    str_text = "0 Results"
Else
    str_text = html.getElementsByClassName("Z0LcW")
End If

Debug.Print str_text.innerText 'innerText is the property that will give the text inside the elements found

    Cells(i, 2) = str_text.innerText
    DoEvents
Next

end_time = Time
Debug.Print "end_time:" & end_time

Debug.Print "done" & "Time taken : " & DateDiff("n", start_time, end_time)
MsgBox "done" & "Time taken : " & DateDiff("n", start_time, end_time)

Thank you so much mike. Thats been a great help. Im just going to add a handler for when no address is found. — Besarion, Oct 01 '19 at 09:16

Is there a piece of code which will pull an address out from a Google Search

1 Answers1