ashish koul
Member
If you want to find the Latitude and Longitude of any address using MapQuest . Try this UDF-
To know more about MapQuest API visit -
http://open.mapquestapi.com/geocoding/
[pre]
[/pre]
Suppose
Street is in cell A2
City is in cell B2
State is in cell C2
Country is in cell D2
Zip is in cell E2
In cell F2 type lat_lon_mapquest(A2,B2,C2,D2,E2)
Download Working File https://www.box.com/s/u0k81vtk12nw03z7ryzh
To know more about MapQuest API visit -
http://open.mapquestapi.com/geocoding/
[pre]
Code:
Function lat_lon_mapquest(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://open.mapquestapi.com/geocoding/v1/address?callback=renderOptions&inFormat=kvp&outFormat=xml&location="
sURL = sURL & Replace(a_t, " ", "%20") & "," & Replace(c_t, " ", "%20") & "," & Replace(s_t, " ", "%20") & _
"," & Replace(co_t, " ", "%20") & "," & Replace(z_t, " ", "%20")
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_mapquest = "Lat:" & la_t & " Lng:" & lo_g
End Function
Suppose
Street is in cell A2
City is in cell B2
State is in cell C2
Country is in cell D2
Zip is in cell E2
In cell F2 type lat_lon_mapquest(A2,B2,C2,D2,E2)
Download Working File https://www.box.com/s/u0k81vtk12nw03z7ryzh