Reverse geocoding is the process of taking a set of GPS coordinates and turning it into the nearest available address.
Browsing through the net, I found VBA code on many websites to get GPS coordinates(latitude & longitude) from a location address but found it really hard to get a reverse geocoding in VBA.
It can be achieved using following Google API:
http://maps.googleapis.com/maps/api/geocode/json?latlng=-33.856098662,150.996315097333&sensor=false
It will return JSON, that can be parsed to get the nearest available location.
e.g.
Function GEOAddress(dblLatitude As Double, dblLongitude) As String
Dim strJSON As String
Dim strAddress As String
Dim lngTemp As Long
Dim objXml As Object
Dim strUrl As String
strUrl = "http://maps.googleapis.com/maps/api/geocode/json?latlng=" & dblLatitude & "," & dblLongitude & "&sensor=false"
Set objXml = CreateObject("Microsoft.XMLHTTP")
With objXml
.Open "GET", strUrl, False
.send
strJSON = .responseText
End With
Set objXml = Nothing
lngTemp = InStr(1, strJSON, "formatted_address")
strAddress = Mid(strJSON, lngTemp + 22, InStr(lngTemp, strJSON, """,") - (lngTemp + 22))
GEOAddress = strAddress
End Function
Happy Coding 🙂
Can I have the Name tagged in Google map to appear? for instance -1.288645
, 36.822955 to give Kenyatta International Conference Centre, Harambee Ave
Nairobi instead of KICC Helipad, Parliament Road, Nairobi City, Kenya
What part of the code should I change?
Thanks for the code but is there a way the name tagged by google(Kenyatta International Conference Centre, Harambee Ave
Nairobi) to appear instead of the road or street name(KICC Helipad, Parliament Road, Nairobi City, Kenya)
Try this -1.288592, 36.823212 coordinates for KICC Nairobi
Thanks again
good question Imelda. I’ll get back to you on this
Great work on this.
Do you know if this will work in Excel 2011 for Mac? I haven’t tried it as i don’t own a Mac but I was wondering if it will work because i have colleagues who may want to try it.
Thanks!
Hello, do you know if this code will work in Excel 2011 for Mac?
Hi Jonas,
Though i have not tried this code on mac but i believe it should work on mac machine also.
Not working in Mac
I’m having an issue with how to format the long/lat in a cell in excel. How should it be formatted to have it generate the address using the function?
I want to use UTM coordinates in meters in place of Lat Long or else any conversion method is there Lat long to UTM coordinates?
Superb, it worked for me.