Excel File download with VBA and Google Maps to calculate distance and duration between multiple destination addresses.
Updated 5/23/2019: Fixed error where re-encoded origin string resulted in incorrect distances after 10 destination addresses.
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.