ashish koul
Member
If you want to find the distance and time taken between tow places by passing the Latitude and Longitude of Origin and Destination Via VBA Excel and Google Map.
Here is the UDF
In cell A3 = Origin Latitude
In cell B3 = Origin Longitude
In cell C3 = Destination Latitude
In cell D3 = Origin Longitude
In cell E3 Type =get_dis_and_time(A3,B3,C3,D3)
Download Working File Here https://www.box.com/s/x2ofy9r8cc7bro0p83xq
Here is the UDF
Code:
Public Function get_dis_and_time(lat_1 As String, lon_1 As String, lat_2 As String, lon_2 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=" & lat_1 & "," & lon_1 & _
"&destinations=" & lat_2 & "," & lon_2 & "&sensor=false"
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
In cell A3 = Origin Latitude
In cell B3 = Origin Longitude
In cell C3 = Destination Latitude
In cell D3 = Origin Longitude
In cell E3 Type =get_dis_and_time(A3,B3,C3,D3)
Download Working File Here https://www.box.com/s/x2ofy9r8cc7bro0p83xq
Last edited by a moderator: