ashish koul
Member
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
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
Download Working File
https://www.box.com/s/3ai8xgasra8kpsgt40lq
Copy the below udf and paste it any new module of your workbook
Code:
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
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
Last edited by a moderator: