Excel File download with VBA and Google Maps to calculate distance and duration between multiple destination addresses.
Update 12/17/2019: Important! You must have a Google Matrix API Key and IT MUST BE CONNECTED TO AN API BILLING ACCOUNT!
If your billing account is not active or connected to the API account, the tool WILL NOT RUN!
I apologize for leaving this out of the instructions.
Hi, I'm Matthew Moran. I am a 25+ year technology consultant, author, and business automation specialist. In my other life, I also write & play music.
Also, my goal is to provide learning across multiple disciplines. If you've found this information helpful, consider buying me a cup or two (or 100) of coffee.
I've been asked to update this and address a few things in the code for a couple years.
Back on October of 2016, I posted this blog - with an Excel download. The file calculated distances between a base address and a list of addresses. As I explained at the time, it was used for a marketing report for one of my clients. They wanted to know how far their customers were driving to get to them and from where.
Since then, I've had a LOT of people download the file. However, changes to the Google Maps API Distance Matrix was causing the code to fail frequently and eventually always. A few reasons for this.
- It used an outdated URL
- It didn't include the API Key
- It didn't include region (needed for some people).
All of that has been addressed and a few other items adjusted as well. Below is a quick video example/demo.
I'm not going through the code here. My current workload doesn't allow me to. But you can go into the code when you download it and I have a few items commented in the code.
A few things of note:
- I parse this as XML and I want to clean that up a bit. There is no error handling.
- Google lets you have multiple origin (from) addresses and multiple destination (to) addresses. I use that in this code. I process up to 10 destinations at a time. Why 10? Because. You can do up to 100.
- I use the Microsoft XML 6.0 Library in the references and use early binding. You may want to change that to late binding... or not. I wanted the intellisense - particularly when working with the childnodes as an IXMLDOMNode.
- I was modifying the previous code, so what's downloaded here is less efficient than I would like but it is effective.
- I'm aware of the lack of error checking. I'll leave that up to you. 😉
What I would like from you:
I don't ask for much. But, if you find this helpful, please consider doing the following:
- Say thanks. On YouTube, here in the comments, or send me a message using the form below.
- Follow me on Twitter, YouTube, and/or Facebook.
- Find out about my music and other creative pursuits.
- Share this page or my site, etc.
And if you can't do any of that, no worries. I hope the code was helpful. I'll be adding to it. I'll have a json parser and also code to have multiple origin and destination distances. And I'll document the code a bit more.
If you are interested in knowing when that happens, send me a message using the form below.