Reverse Geocoding: Get Location Address from GPS coordinates(Latitude & Longitude)

Posted: July 5, 2015 by Transformer in Excel, VBA
Tags: , ,

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 🙂

Advertisements
Comments
  1. Imelda says:

    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?

  2. Imelda says:

    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

  3. Jonas Figueroa says:

    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!

  4. Jonas Figueroa says:

    Hello, do you know if this code will work in Excel 2011 for Mac?

  5. Garred says:

    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?

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s