Get Multiple Distances Using the Google Maps API and VBA for Excel or Access includes sample file

Automate Everything

I hate manual processes.

NOTE: If I get one more email from someone nagging me to finish that Quickbooks SDK Tutorial.. I’m going do it!!!  😉
Edit: 7/26/2017 – Okay.. I lied. I cannot get to it per se… but I am posting some code to help with QuickBooks integration from time to time. 

Visit the aforementioned post to read more.

Recently, a client wanted a market analysis showing how far customers traveled to get to their location. They were having employees manually check addresses using Google maps to determine this information and entering this on a sheet. The data was later entered in a spreadsheet. All of this data, minus the distances, is in their database.

Their database is a somewhat archaic, closed system.. But custom queries allowed for the creation of a csv file.

Simple Solution using VBA and the Google Maps API

Instead of manually retrieving this information I wrote a VBA routine that connects to the Google Maps API and retrieves the distances. I prompt the user to select the csv file, prepare it a little for readability, retrieve the distances, then add some functions to the top to show averages of some key fields.

I’ve also written a post analysis routine that takes all the city and state information and determines and list the cities where the bulk of their guests are from. This file is then uploaded to Google Maps to show a visual representation of where their customers are from.

The Google Maps API limits request to 10 per second (or something like that) before returning an “OVER_QUERY_LIMIT” error/message.

When I get that message, I pause the routine 2 seconds and try again. Actually, you’ll see in code that I retry up to 5 times. That’s because when I retried once or twice, it sometimes got the same error – even with a few seconds between.

Nothing fancy here but it forms a basis for you to retrieve Google Maps API data for multiple addresses. You can also retrieve travel time and directions.

You may download the sample sheet below.

Here is a video of the code executing on my sample spreadsheet.

 

File Download

Download

 

If this works for you and you find it helpful, please consider sharing this page. Thanks.

Posted in Consulting, Tips and Tools, Video and tagged , , , .

6 Comments

  1. Hey Matt,

    I would like to view your VBA code that you are sharing here but your website is blocking me from downloading it.

    Dont know if anyone else is experiencing this problem, but I thought I would make you aware of it.

    • Hey Alex,

      Thanks for pointing that out. We recently changed our hosting and started it was initiated with some restrictive permissions. Please try again and let me know if you are able to download the file. Thanks, Matt

  2. I’m currently using the Distance matrix api to calculate the distance from one location to another in an access database. What we just discovered is that the distance matrix api uses the google recommended directions. So it may be fastest, shortest, etc. We reimburse mileage based on the shortest distance. Is this using the google recommended directions/distance or shortest distance?

    • Hi Renee,
      I actually do not know.. something to look at. I would guess there is a parameter that could be added/adjusted for that. I don’t have time, at the moment, to look into that – but it is whatever the default for the Google API is.

    • Hi Sushma,

      Thanks for writing. I just downloaded and ran this file and it worked fine.

      What version of Excel? What OS? View the code and references, maybe send me a screen shot of that. My contact information is on the home page.

      Also, your name sounds like you may not be from the US. Where are you from? Could there be any limitations imposed on Google’s Map API from your geo/location?

      Let me know.. Thanks.

Leave a Reply

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