Using a VBA Function and Google places API to find and format an address from a Cell-CodePudding

To make sure an address is always formatted in the same way (which I will use in another VBA Sub) I'm trying to use the google places API in combination with a VBA created function. At the moment I'm able to get the query working (in the browser) but I'm not able to get the result I'd like back from the created function.

The API that is working is the following (temporary API-key added) added with a random address: https://maps.googleapis.com/maps/api/place/findplacefromtext/json?fields=formatted_address,name,rating,opening_hours,geometry&input=Coendersweg 2&inputtype=textquery&key=AIzaSyDNZs0Dmc0xTQqxEjuU26A_f9IeNnA_78U

It gives the result "Formatted_Address" which I'd like to show up as the result of the function. If it's possible to make resulting address have the zip code (9722 GE) formatted as "9722GE" and the country ", Nederland" not show up that would be even better.

Example Resulting of random address:

Coendersweg 2, 9722GE Groningen / [Streetname number, Zipcode City]

This is the VBA code I have so far:

Function FindAddress(address, APIKEY)
Dim strURL As String
strURL = "https://maps.googleapis.com/maps/api/place/findplacefromtext/" & _
"json?fields=formatted_address,name,rating,opening_hours,geometry&input=" _
& address & "&inputtype=textquery&key=" & APIKEY

Set httpReq = CreateObject("MSXML2.XMLHTTP")
With httpReq
        .Open "GET", strURL, False
        .Send
End With

Dim Response As String
Response = httpReq.ResponseText

Dim parsed As Dictionary
Set parsed = JsonConverter.ParseJson(Response)
Dim FoundAddress As String

FoundAddress = (formatted_address)

FindAddress = FoundAddress

I've got the "JsonConverter.bas" from https://github.com/VBA-tools/VBA-JSON as a module inside my VBA as well.

Most of the code is borrowed from the following Youtube video where I made some tweaks to work with Google places API instead of Google Directions API: https://www.youtube.com/watch?v=_P2lj4yHNu4

Would be really appreciated if someone has a solution so the function will work, and is able output & fill/format the address that is used as input for the function.

CodePudding user response：

Here's a method that will return the formatted_address field. You can return other fields if you prefer -- the modifications should be obvious.

Note that I used early binding, but you can use late binding if you prefer.

Run against your input, => Coendersweg 2, 9722 GE Groningen, Netherlands

Option Explicit
Function getAddress(S As String)
    Const API As String = "key=YOUR_API_KEY"
    Const sURL1 As String = "https://maps.googleapis.com/maps/api/place/findplacefromtext/json?fields=formatted_address"
    Const sURL2 As String = "input="
    Const sURL3 As String = "inputtype=textquery"
    Dim sAddr As String
    Dim sURL() As String
    Dim sLocation As String
    Dim xhrRequest As XMLHTTP60

Dim strJSON As String, JSON As Object

sAddr = Replace(S, " ", " ")

'Many ways to create the URL to send
ReDim sURL(3)
    sURL(0) = sURL1
    sURL(1) = sURL2 & sAddr
    sURL(2) = sURL3
    sURL(3) = API
Set xhrRequest = New XMLHTTP60
With xhrRequest
    .Open "Get", Join(sURL, "&"), False
    .Send
    strJSON = .ResponseText
End With

Set JSON = ParseJson(strJSON)
If Not JSON("status") = "OK" Then
    MsgBox "Status message: " & JSON("status")
    Exit Function
End If

'might need to check if more than one candidate is returned
getAddress = JSON("candidates")(1)("formatted_address")

End Function

If you want to have the format different from what is shown, I suggest you use the Places api to return the place_id. You can then feed that value into the Place Details to return the address_components and format the address however you prefer.