ashish koul
Member
If you want to find Latitude and Longitude of any address .
Download Working File https://www.box.com/s/h733p8zufxkkum8nsi2d
Here is the UDF
To know more visit https://developers.google.com/maps/documentation/geocoding/
Assuming
Street In Cell A2
City In Cell B2
State In Cell C2
Country In Cell D2
Zip In Cell E2
In cell F2 type formula like =lat_lon(A2,B2,C2,D2,E2)
Download Working File https://www.box.com/s/h733p8zufxkkum8nsi2d
Download Working File https://www.box.com/s/h733p8zufxkkum8nsi2d
Here is the UDF
Code:
Function lat_lon(a_t As String, c_t As String, s_t As String, co_t As String, z_t As String)
Dim sURL As String
Dim BodyTxt As String
Dim apan As String, la_t As String, lo_g As String
Dim oXH As Object
'create web url
sURL = "http://maps.googleapis.com/maps/api/geocode/xml?address="""
sURL = sURL & Replace(a_t, " ", "+") & ",+" & Replace(c_t, " ", "+") & ",+" & Replace(s_t, " ", "+") & _
",+" & Replace(co_t, " ", "+") & ",+" & ",+" & Replace(z_t, " ", "+") & ",+" & _
"&sensor=false"""
' browse url
Set oXH = CreateObject("msxml2.xmlhttp")
With oXH
.Open "get", sURL, False
.Send
BodyTxt = .RESPONSETEXT
End With
apan = Application.WorksheetFunction.Trim(BodyTxt)
'Latitude
apan = Right(apan, Len(apan) - InStr(1, apan, "<lat>") - 4)
la_t = Left(apan, InStr(1, apan, "</lat>") - 1)
'Longitude
apan = Right(apan, Len(apan) - InStr(1, apan, "<lng>") - 4)
lo_g = Left(apan, InStr(1, apan, "</lng>") - 1)
lat_lon = "Lat:" & la_t & " Lng:" & lo_g
End Function
Assuming
Street In Cell A2
City In Cell B2
State In Cell C2
Country In Cell D2
Zip In Cell E2
In cell F2 type formula like =lat_lon(A2,B2,C2,D2,E2)
Download Working File https://www.box.com/s/h733p8zufxkkum8nsi2d
Last edited by a moderator: