UDF to find Latitude and Longitude of any address using MapQuest API and VBA

[ashish koul]

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]

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

[/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