One quite popular post on this site is the Geocoding for Excel post. In common with lots of the code snippets here, the code was stripped down to the bare minimum to make it understandable and functional.

I happened to be debugging a recent tool (the EPW weather file finder) and thought that it might be an idea to upload the finished version including features like results caching, error handling, URL encoding, and avoidance of localisation problems from the use of commas as decimal separators.

As before it returns latitude and longitude, but it now has three additional options for the return values and one to force the function to requery Google, for example if you think the value it has returned is incorrect.

=G_LATLNG("Trafalgar Square") returns 51.5073346,-0.1276831
=G_LATLNG("Trafalgar Square",1) also returns 51.5073346,-0.1276831
=G_LATLNG("Trafalgar Square",2) returns 51.5073346
=G_LATLNG("Trafalgar Square",3) returns -0.1276831
=G_LATLNG("Trafalgar Square",4) returns Trafalgar Square, City of Westminster, London, UK

To force the function to requery you need to set the Requery option to True like so: =G_LATLNG("Trafalgar Square",,True) (note the two commas – you can also still use the number codes as the second parameter to return latitude, longitude or address).

There’s quite a lot of code in this function, but it’s well commented so should be reasonably easy to follow.

This depends on the following functions:

Jamie Bull | jamiebull1@gmail.com

Related Posts

WiGLE is a popular platform which can be used for finding the location of a device using the names of WiFi networks in its vicinity. I’ve written about this before, and wrote some Python code to interact with their API. This API has since been retired and replaced with a new one, as of December […]

Just a quick post to point out a couple of really useful tools.The first is a web-based tool for finding weather files for a location of interest. It’s similar to the Excel EPW finder tool we created a few years back, but much more modern looking. It is however missing a few of the useful […]

Eppy is a really useful library which I’ve written about several times, since before I really had anything to offer in terms of contributing code. Over the past year or so though, I’ve started to contribute back some of the changes and additions I’ve made while using eppy on academic and commercial projects.This post is […]

22 Comments on “Improved Excel interface for Google Geocoding API”

  • Ian says:

    Hi Jamie,

    I stumbled across this posting while looking for a solution to a similar problem to resolve the address information of the specified location.

    In the XML returned by the Google API all of the address information is wrapped in “address_component” tags as follows:-

    Blackthorn Dr
    Blackthorn Dr
    route

    Inside the “address_component” the “type” fields identifies the address element.

    I have very limited experience in working with XML so was hoping you could provide some pointers on how to iterate through the XML to use option’n’ to return the other address elements.

    Any guidance very much appreciated.

  • Theo says:

    The code is great and very helpful!
    Would it be possible to extend it where the locality (city), postal code, admin level 1 (state) and country are extracted based on the parameter n, (5 through x)

    Kind Regards
    Theo

  • Seth H. says:

    Great function, thanks. Do you have any idea why a handful of results would come up as N/A, after a group of cells are filled down, only to populate with proper data if the “bad” cells are then manually clicked on and edited? (Just “click” and hit the enter key.) If I only do ten cells at a time, that seems to remedy moist of the problem. I haven’t tried this locally, but seems to happen when running an Excel instance on a Citrix box with multiple processors. I’m wondering if one of the processors isn’t “seeing” what the other processor has retrieved from Google. Thanks again for the compact function. Works great, otherwise.

    • Jamie Bull says:

      The problem sounds like Google noticing you accessing the API at a very high rate. A solution is to add a wait to each call – 200 milliseconds works. You may be able to reduce that though, I’ve never tested it. Put the line Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) at the top of your module, add in the line Sleep(200) somewhere in the G_LATLNG() function and the code will pause for 0.2 seconds between queries,

      • Seth H. says:

        Worked like a charm. Thanks again for the great function. Lots of other examples are out there on the web, but this is BY FAR the cleanest implementation that I have seen in VBA. I’m going to add functionality to grab some of the other nodes that Google returns via the XML, and then I’m all set. Much appreciated.

  • Philippe says:

    Hello Jamie,

    I was a great fan of the first version ! 🙂

    I just tested this new version and I got strange results. I seems a little crazy; perhaps you will have an idea.

    It works well for a lot of locations, but with French locations I got problems:

    first time you input G_LATLNG(“Paris”) , you get 48.8566140,2.3522219
    If you refresh the cell, you get #NA
    Then if you write G_LATLNG(“Paris, France”), you get again 48.8566140,2.3522219
    If you refresh the cell, you get again #NA
    If you add a new blank space before France, it works again but as soon as you refresh, you need to enter a new blank space…

    I test the same behavior with Notre Dame, Pont de l’Alma, Tour Eiffel…
    Same with “Bordeaux, Saint-Jean” or “Saint-Jean, Bordeaux”

    But for London or Brussels, no problem at all….

    Could it be only for France, something wrong with Google? Or the cache, but why only for France? I am lost…

    Thanks in advance for your help.

    Any way you posts are great !!!

    Philippe

  • Philippe says:

    Hello Jamie,

    Any idea, direction where to search about the problem above?

    Thanks in advance for your help.

    Philippe

    • Jamie Bull says:

      I’m afraid I can’t reproduce your problem. It works just fine for me for France. A few things you could do to try and diagnose:

      1) Changing your decimal point marker to a point rather than a comma (assuming you’re using a comma at the moment). I don’t hold much hope for that being the issue though if you’re only having problems with French addresses, but always worth a try.
      2) Try forcing requery rather than caching results. To do this use =G_DISTANCE("Paris, France",,TRUE). Do you still have the problem? Not a solution but might give us a clue as to what’s happening.
      3) Step through the code in the VBE and make a note of what it’s doing differently on the times it’s returning the right answer and the times with the #NA error. Pay particular attention to the results of the URLEncode function as I seem to recall having some issues there before.
      4) Comment out the On Error GoTo exitRoute line and make a note of where the code falls over.

      I hope something there helps put you on the right track. Report back the results of the four ideas above and we’ll keep looking for a solution.

      • Dario says:

        Hello Jamie,I have the same problem than Philippe, I use Excel in Spanish, first time it worked fine (I had to replace comma by ;), but when recalculated it gave #NA, could you help me. Thank you

  • Michael says:

    Thanks for the great function. It works really fine for me. The only thing is format of the chars. I need to input some chars like “ß, ü, ä, ö, Ü, Ä, Ö, é, è, á, à, ó, ò and so on. The input should be unicode or UTF-8. I don’t know. It needs to support western europeen chars.

    Maybe at the next update???

    thanks a lot…

  • Guilherme Mello says:

    Hi Jamie,

    Excellent work!

    Two questions:
    – Can i make it work on MAC? Using Excel 2011.
    – Is it possible to know the accuracy? If google maps return the latitude and longitude from the street or the exactly address?

    Thanks a lot.

    • Jamie Bull says:

      Hi Guilherme. Apologies for the delay getting back to you. No it’s not possible to use this code on a Mac as it depends on a reference to a Windows dll. On your other question, the Maps API does return an accuracy – it’s implied by the location_type field but I’ve not implemented anything to parse that part of the response. You can see the definitions of the return values here.

  • Alonso says:

    Jamie,

    Amazing job here! Im impressed 🙂 I wanted to ask how could cange the programming code to get reverse geocoding, in other words having Lat and Lon, have the excel give me the address…. thanks tons!

  • Gerard says:

    Hi Jamie,

    I know this is quite an old post but am wondering how do I go about importing it into my 2010 excel? Do I need to put in 3 different visual
    basics?

    Thanks

    Gerard

Leave a Reply

Your email address will not be published. Required fields are marked *