Retrieve closest address suggestion using Google API and Excel Vba

[TG2812]

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 .

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