Find Distance and Time between two cities using Google API In excel

[ashish koul]

If you want to know the distance between two cities by passing the address of destination and Origin place using "Google Distance Matrix API" in VBA.


Download Working File


https://www.box.com/s/3ai8xgasra8kpsgt40lq


Copy the below udf and paste it any new module of your workbook

Public Function get_dis_and_time _
( _
origin_street As String, origin_city As String, _
origin_state As String, origin_country As String, _
destination_street As String, destination_city As String, _
destination_state As String, destination_country As String _
)

' Read more about Google API’s here
'https://developers.google.com/maps/documentation/distancematrix/

' we will be using Google API Distance Matrix to get Time and distance between two cities.
'https://developers.google.com/maps/documentation/distancematrix/
Dim surl As String
Dim oXH As Object
Dim bodytxt As String
Dim tim_e As String
Dim distanc_e As String
surl = "http://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & _
Replace(origin_street, " ", "+") & "+" & Replace(origin_city, " ", "+") & "+" & Replace(origin_state, " ", "+") & "+" & Replace(origin_country, " ", "+") & _
"&destinations=" & _
Replace(destination_street, " ", "+") & "+" & Replace(destination_city, " ", "+") & "+" & Replace(destination_state, " ", "+") & "+" & Replace(destination_country, " ", "+") & _
"&mode=driving&sensor=false&units=metric"""

'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, "<Text>") - 5)
tim_e = Left(bodytxt, InStr(1, bodytxt, "</text>") - 1)
bodytxt = Right(bodytxt, Len(bodytxt) - InStr(1, bodytxt, " <Text> ") - 5)
distanc_e = Left(bodytxt, InStr(1, bodytxt, "</text>") - 1)

get_dis_and_time = tim_e & " | " & distanc_e
Set oXH = Nothing

End Function

Suppose you have

Origin Street in A3

Origin City in B3

Origin State in C3

Origin Country in D3

Destination Street in E3

Destination City in F3

Destination State in G3

Destination Country in H3


In cell I3 Type = get_dis_and_time(A3,B3,C3,D3,E3,F3,G3,H3)


Visit here to know more about Google API’S


https://developers.google.com/maps/documentation/distancematrix/


Download Working File


https://www.box.com/s/3ai8xgasra8kpsgt40lq