Google Maps Drive Time & Distance… in Microsoft Excel

Here is a handy piece of VBA code that allows you to query the Google Maps API from Excel, you can feed it any two points that usually work in Google Maps: GPS co-ords, street addresses, landmarks, businesses etc.

Public Function get_dis_and_time2 _
( _
origin_address As String, _
destination_address As String _
)
Dim surl As String
 Dim oXH As Object
 Dim bodytxt As String
 Dim tim_e As String
 Dim distanc_e As String
 Dim APIkey As String
 
 APIkey = “ENTER HERE BETWEEN THESE QUOTES” ‘https://developers.google.com/maps/documentation/javascript/get-api-key
surl = “https://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & _
 Replace(Replace(Replace(origin_address, “ “, “+”), “,”, “+”), “++”, “+”) & _
 “&destinations=” & _
 Replace(Replace(Replace(destination_address, “ “, “+”), “,”, “+”), “++”, “+”) & _
 “&mode=driving&sensor=false&units=metric&key=” & APIkey
‘ units=imperial
 ‘if u want to show distance in kms change unit to metric
 ‘https://developers.google.com/maps/documentation/distancematrix/#unit_systems
 ‘units=metric (default) returns distances in kilometers and meters.
 ‘units=imperial returns distances in miles and feet.
Set oXH = CreateObject(“msxml2.xmlhttp”)
With oXH
 .Open “get”, surl, False
 .send
 bodytxt = .responseText
 End With
bodytxt = Right(bodytxt, Len(bodytxt) — InStr(1, bodytxt, “<value>”) — 6)
 tim_e = Left(bodytxt, InStr(1, bodytxt, “</value>”) — 1)
bodytxt = Right(bodytxt, Len(bodytxt) — InStr(1, bodytxt, “<value>”) — 6)
 distanc_e = Left(bodytxt, InStr(1, bodytxt, “</value>”) — 1)
get_dis_and_time2 = tim_e & “ | “ & distanc_e
Set oXH = Nothing
End Function

You can call this function from Excel like a regular function:

This file can be downloaded at the link below

Here is an example file, remember you need to enter in your own API key which you can create here: https://developers.google.com/maps/documentation/javascript/get-api-key

The finished article

Note: Since July 2018 Google has changed their terms and this type of query is no longer free :( Be careful, this code will execute each time it’s used (and you’ll be billed) every time you refresh/calculate in Excel.