Edit: See an updated version of this geocoding function.

As with the previous post, I’ve been writing some code lately using Google tools. This post is a rewrite of some heavier code for geocoding location data. The aim is to get latitude and longitude which is then used to display points on a Google Map (more on that in a later post). It’s used as part of a dashboard which can show data about various buildings to give an obvious cue to the user as to which one they are currently viewing.

The function for Excel takes a single argument, a place name or postcode. It then grabs the latitude and longitude from Google and returns them to 4 places separated by a comma and a space as follows:

I’ve named this UDF G_LATLNG and the syntax for using it is =G_LATLNG(Input).

Note that the Google Maps API terms of service state that you can only use the API when displaying a Google Map, so using this code without doing so is at your own risk. Look out for a future post here on how to do that.

The table shows the same ways of inputting data are available as with G_DISTANCE.

You’ll need to install this as a code module in your workbook, and to set a reference to Microsoft XML, v6.0 (Tools > References > Microsoft XML, v6.0 ).

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 […]

33 Comments on “Google geocoding for Excel”

  • Filipe says:

    Hello.

    Would be very nice if you could attach the excel file with the macro working on it.

    I am having some trouble to make it work.

    Thank you in advance.

  • Jamie Bull says:

    Hi Filipe,

    Just a thought – have you added a reference to Microsoft XML, v6.0? I’m polishing up some of the code for a downloadable workbook but if it’s just adding in a reference that you need then that might get you moving quicker.

  • Leonardo Kiam says:

    Hi Jamie,
    Very usefull sample!
    I´d like to ask you some help.

    How do i obtain the City from the XML? In the following excerpt i´d like to get the Long Name.


    Cruzeiro
    Cruzeiro
    locality
    political

    Thanks in advance!

    • Jamie Bull says:

      Hi Leonardo,

      I think you can do what you’re looking to do using the following function as a base (very similar to the G_LATLNG function). You can do quite a bit with the Xpath in VBA but this seems like the simplest way to go about it. The only problem is depending on how you query Google, it can return the city name in various places in the XML.

      Hope that helps.

  • Ian says:

    Just wanted to say thanks for this.
    I have spent two days searching for something to return the lat lng to use on a user form.

    • Jamie Bull says:

      Hi Ian. You might want to hold off a few hours – I’m just working on cleaning up a version that caches the results locally to speed up performance. Only worth waiting for if you’re likely to need the same lat long pairs more than once or twice though, otherwise glad to be of service!

  • cavo789 says:

    I was just searching for a tip on how interpret the result of Google geocoding (//result/address_component). Thanks for having shared your code.

  • Another option is to use the free Excel add-in called MapCite which uses Bing Maps for geocoding: http://www.mapcite.com/ Another option is to use the Bing Spatial Data Service Excel add-in which can geocode 200,000 addresses in a single request: http://bsdsexceladdin.codeplex.com/

    • Jamie Bull says:

      I mainly use this as an intermediate step in other macros. MapCite seems like quite a heavy-weight add-in for that sort of use case but certainly worth a look if you’re doing a lot of map-based data crunching.

  • S.E.N says:

    Hi, I am not an expert. I have some city and country data in Excel file and I need the latitude and longitude of them. I think what you posted at the top the page that is exactly I need. But I don’t understand where should I put these codes that you wrote. Do I need another program except Excel? Could you explain simpler?

    Thanks

    • Jamie Bull says:

      I’d suggest you download the sample spreadsheet from here And adapt it. It makes things much easier. And no, you don’t need any other software, just Excel.

  • Dario says:

    I believe #NA is showed because “OVER HARD QUERY LIMIT” give me in your excel_gmaps_v1_01.xlsm file. Do you know How can i make available this limit?

    Thank you

  • Brian says:

    I’ve been using this for awhile now and it’s been flawless until my most recent file that has a loop of this function. I’m finding that when I loop through many (43 in my case) iterations that I get zero values on some of the results. Same thing happens when I have many cells with the function in it and I recalculate the sheet. It’s as though it’s not waiting long enough to get a result before moving onto the next request.

    When I go back through and click inside any cell that has a zero value and manually recalculate it, then I get a value. However in my current code I am comparing two variables (Mileage A vs. Mileage B. and taking the smaller value) so I can’t recalculate. Is there a way to make the function wait until it is finished receiving the request from the Google website? Kind of like the “READYSTATE_COMPLETE” command? Thanks!

  • Brian says:

    Nevermind I found an answer on another one of your webpages: http://oco-carbon.com/2012/03/06/google-excel-distance-function/

    The Sleep(200) function worked perfectly to fix the issue. You can delete my two comments if you want to keep your page clean.

  • Patrice says:

    Hello if we use this macro in a business environnement for between 100 to 1000 request per day, do we need the google’s business API license at 10K / year ? Or as long as we stay under 2500 requests per day we could use this macro without having to pay ?

  • Panos says:

    Hi Jamie

    I ve got a problem making G_Distance working with Greek characters. It only recognizes Latin.
    Could you please help me.

    Thank you

  • Nick says:

    Is there a similar code to find zip/post code when street, city and state are known?

    thanks

    • Jamie Bull says:

      Not that I know of. I’m sure there’s a post on this site which does something similar for UK postcodes (just the first part of the postcode from memory) but I can’t find it for the life of me.

      • gak says:

        Hi Jamie, thanks for providing all this code it is very usefull. I downloaded your “Excel_GMaps_v1_01.xlsm” but unfortunately when I enable the macros in Excel then VBA starts and an error message appears. Each time I click OK the message reappears so I cannot use / access the code. Would it be posisble to help me here? maybe you could share the code? the function of interest is g_address. Thanks.

  • Vinay Garg says:

    Hey Jamie,
    Could you help me with the following. I have a vector with (Latitude and Longitude) and I want to get the location name from that.
    I have been looking at a lot of forums to solve the problem but unfortunately I couldn’t find any solution.

  • Patrick says:

    Hey guys
    i have bee searching for awhile on how to get a name from LAT/Long. All i can find is how to get LAT/LONG….. any suggestions on how i can complete my task

    thank you

  • Mariana says:

    I’m trying to use this code to give the distance between two cities, but it always give me a zero as result. Do you have any idea of what can be happening?
    Yesterday it was working, but today it just returns me zeros..

  • Mariana says:

    Yes I used a lot I guess.. Do you know if the limit is daily? I also tried to use it in a different computer, but I have had the same result.. Shouldn’t it have worked at a different machine?

    • Jamie Bull says:

      The hard rate limit 2500 queries per day, yes. And the rate is counted by IP address. If the machine is on the same network then it would still be affected. If it was somewhere else then the problem must be something different.

      • Mariana says:

        Then that may be the problem, because the machine is on the same network.
        I’ll try again tomorrow. Thank you so much for your attention.

  • Luiz says:

    O código funciona no windows 2007 mas não funciona no 2010. Porque? tks

Leave a Reply

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