Heres a little example of some VBA functions I put together to get geocodes into excel using the google maps API.
It’s not the best geocoder in the world, but it does ok in the test I’ve done so far, although it does just come up with blanks for some locations. To use it you will need a Google maps API key which you need to store in a text file on the same path as the workbook, details are on the first sheet of the workbook.

As for the code itself I had to automate IE rather than use an API or the HTTP or XMLHTTP libraries, as my corporate firewall would not allow access using these. The function only asks for 1 string, so you have to add spaces yourself, this might be done better, but it suited my needs (that is to say, I didn’t bother writing in optional arguments etc.)

It’s worth while looking at the code as I have done a few things that might not be considered “best practice”, mainly not closing the IE application when the function has run – this is to speed up the function – there is a public function that you can use to close it from a worksheet if you wish (see code as it’s not explained in the “documentation”)

Theres probably some other stuff that I should mention, but I did this a week or so ago and have not been able to post it as my internet connection was down, so I have forgotten that!

Feel free to post any questions and I’ll feel free not to answer them!!!!

Enjoy
Ross

Download M.I.E Google Maps Geocoding Example (zip)