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!

5 comments:

Sérgio Pereira said...

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

Sony said...

Hello Sergio,

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

Hope that makes sense.

Unknown said...

Buy your products online at low rates in Auckland Newzealand.We Provide you quality household items, outdoor furniture nz, gazeboz nz, items and all the items at low rates.

invincible01 said...

Amazing Article, Really useful information to all So, I hope you will share more information to be check and share here.


Internship near me
Inplant Training for cse
Inplant Training for IT
Inplant Training for ECE Students
Inplant Training for EEE Students
Inplant Training for MECHANICAL Students
inplant Training for CIVIL Students
Inplant Training for Aeronautical Engineering Students
Inplant Training for ICE Students
Inplant Training for BIOMEDICAL Engineering Students

riya said...

Super article

What is Cyber Security
Types of Cyber Attacks
Types of Cyber Attackers
Cyber Security Technology
Cyber Security Tools
Cyber Security Standards
What is Google Adwords
Google Adwords tutorial
Google Keyword Planner
How to Advertise on Google