Retrieve closest address suggestion using Google API and Excel Vba

TG2812

Board Regular
Joined
Apr 15, 2015
Messages
180
Hi Virtual Friends,

I'm a newbie in VBA coding and would need your great help.

I have a list of incomplete customer names (our internal system truncates the name based on character restriction) and I'm trying to retrieve their respective address leveraging Google API capabilities and VBA. The code below seems to do the trick and return the full result in column 15.

However, things get a little bit complicated when no results are found... I would like VBA and Google API to return the closest suggestion of customer name and address..and I have no clues how to code this.

Let me know if I should be posting this somewhere else. Otherwise, I'm really looking forward to your responses :):cool:.

Code:
Sub myTest()
    Dim xhrRequest As XMLHTTP60
    Dim domDoc As DOMDocument60
    Dim domDoc2 As DOMDocument60
    Dim placeID As String
    Dim query As String
    Dim nodes As IXMLDOMNodeList
    Dim node As IXMLDOMNode


    Dim rng As Range, cell As Range


    Set rng = Range("L2:L500")


    For Each cell In rng
    On Error Resume Next
    'you have to replace spaces with +
    query = cell.Value


    'You must acquire a google api key and enter it here
    Dim googleKey As String
    googleKey = "My Google API Key" 'your api key here


    'Send a "GET" request for place/textsearch
    Set xhrRequest = New XMLHTTP60


    xhrRequest.Open "GET", "https://maps.googleapis.com/maps/api/place/textsearch/xml?" & _
        "query=" & query & "&key=" & googleKey, False
    xhrRequest.send


    'Save the response into a document
    Set domDoc = New DOMDocument60
    domDoc.LoadXML xhrRequest.responseText
    
    'Find the first node that is called "place_id" and is the child of the "result" node
    placeID = domDoc.SelectSingleNode("//result/place_id").Text


    'recycling objects (could just use new ones)
    Set domDoc = Nothing
    Set xhrRequest = Nothing


    'Send a "GET" request for place/details
    Set xhrRequest = New XMLHTTP60
    xhrRequest.Open "GET", "https://maps.googleapis.com/maps/api/place/details/xml?placeid=" & placeID & _
    "&key=" & googleKey, False
    xhrRequest.send


    'Save the response into a document
    Set domDoc = New DOMDocument60
    domDoc.LoadXML xhrRequest.responseText


    Cells(cell.Row, 15).Value = domDoc.SelectSingleNode("//result/formatted_address").Text & output
    
    Next cell


End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top