Monday, 7 July 2014

Geocoding address data in Excel using google Geocoding API

I had a long list of addresses that I needed to show on a map. I found a great article that showed exactly how to call googles geocoding mapping from Excel.

The only issue I had was that Googles API doesn't like it if too many requests are made too quickly so I created a function to add the geocoding calculation into a column one cell at a time and waiting one second between calls.

Below is the function

Sub Geocoding100()
    Dim I
    For I = 1 To 100
        ActiveCell.FormulaR1C1 = "=GoogleGeocode(RC[-1])"
        ActiveCell.Offset(1, 0).Select
        Application.Wait (Now + TimeValue("00:00:01"))
        Next I
End Sub
I can leave this function running in the background to geocode a list of addresses in excel and I am not hammering Google Geocoding API.

Its a win win situation!


Sérgio Pereira said...

can you explain were to I have to paste this code in the googlegeocode macro.

Sony said...

Hello Sergio,

You need to paste my function into the module that is created for the GoogleGeocode functions.

Hope that makes sense.