Google API distancematrix returning wrong json result in VBA

Question

hope someone can point me in the right direction. I have a function in VBA which is called from Excel, to call the distance from one postal code to another. This is working fine, until now.

The distance only has to be calculated for The Netherlands, but it is not possible to limit the query-string to a country. Now I have a postal code 2151 KD, I use it in my querystring as a from or to postal code. like:

http://maps.googleapis.com/maps/api/distancematrix/json?origins=1251KD&destinations=1211LW&mode=car&sensor=false

(I remove the spaces between the numbers and letters).

This works fine when I paste it in the browser. It shows the right locations and distance. (Destination or origin).

This querystring is generated in code using this VBA function:

Function GetDistance(fromZip As String, toZip As String) As Integer

Dim Uri As String: Uri = "http://maps.googleapis.com/maps/api/distancematrix/json?origins={0}&destinations={1}&mode=car&sensor=false"
Dim xmlHttp As Object: Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
Dim Json As Object
Dim distance As Double: distance = 0

Uri = Replace(Uri, "{0}", fromZip)
Uri = Replace(Uri, "{1}", toZip)

With xmlHttp
    .Open "GET", Uri, False
    .setRequestHeader "Content-Type", "text/xml"
    .send
End With

'Debug.Print (Uri)
Set Json = JsonConverter.ParseJson(xmlHttp.ResponseText)

'Top-level status OK
If Json("status") = "OK" Then
    'Elementstatus
    If Json("rows")(1)("elements")(1)("status") = "OK" Then
        distance = CLng(Json("rows")(1)("elements")(1)("distance")("value")) / 1000
    Else
        distance = -2
    End If
Else
    distance = -1
End If

If fromZip = "1251KD" Or toZip = "1251KD" Then
    Debug.Print (xmlHttp.ResponseText)
    Debug.Print (Uri)
End If

If IsObject(xmlHttp) Then
    Set xmlHttp = Nothing
End If

If IsObject(Json) Then
    Set Json = Nothing
End If

GetDistance = Round(distance, 0)

End Function

However, the result is different. 1251 AD is now a location in the USA. And there is no distance calculation.

Result when called from VBA code:

{
  "destination_addresses" : [ "1211 LW Hilversum, Netherlands" ],
   "origin_addresses" : [ "NE 1251, Osceola, MO 64776, USA" ],
   "rows" : [

      {
         "elements" : [
            {
               "status" : "ZERO_RESULTS"
            }
         ]
      }
   ],
   "status" : "OK"
}

Result when called from browser directly:

{
destination_addresses: [
"1211 LW Hilversum, Nederland"
],
origin_addresses: [
"1251 KD Laren, Nederland"
],
rows: [
{
elements: [
{
distance: {
text: "6,9 km",
value: 6878
},
duration: {
text: "14 min.",
value: 810
},
status: "OK"
}
]
}
],
status: "OK"
}

So far I haven't been able to figure out how to solve this. The querystring is working when I paste it in the address-bar of the browser, but when called from VBA the result is not valid. So far just for this specific postal code of 1251 KD.

Does anybody have a clue?

In the browser I get the same result as your "VBA" output, unless I add back the space in "1251 KD", when I get your "browser" output. — Tim Williams, Mar 15 '16 at 19:46
I always remove the space, because it could lead to some unpredictable results. like AT was sometime interpreted as Austria, but @bioschaf solutions works great. Thx Tim — Stephan, Mar 15 '16 at 20:36

score 1 · Accepted Answer · answered Mar 15 '16 at 19:59

Sorry, I cannot reproduce your error - the code works just fine.

But if you only need results from the Netherlands, why don't you add the country to the query string?

For your code, that would mean

Uri = Replace(Uri, "{0}", fromZip & ",Netherlands")
Uri = Replace(Uri, "{1}", toZip & ",Netherlands")

Google API distancematrix returning wrong json result in VBA

1 Answers1

Linked