# Off Topic > Tips and Tutorials >  >  Getting Distances and DriveTimes

## Kyle123

Getting driving distances and drive times seems to be a very popular topic recently. I had originally posted a thread as to how to get this via the google API which is extremely simple, grabbing this and putting it into excel directly however breaches the terms of use.

Since then, I have discovered that Microsoft also offers a similar API, you do however need to sign up and get a key (here) and accept their terms of use.

I don't think posting the below contravenes anything, but if anyone feels otherwise let me know and I'll happily remove this.

In the below, you need to input your API key in the relevant place.




```
Please Login or Register  to view this content.
```

----------


## Rajjan

Thanks for posting this! After trying to download distance tables from sites that don't really want you to see them, this was a relief.

----------


## Elpropio

Thank you very much for this code and all your advise. I use the distance calculator to report drived miles in my work and this saves me a lot of time.
Welldone!

----------


## jamington2004

Brilliant! I was looking to do something complicated using a Geomapping program we have...... but now I tried this and everyone will think I am some kind of excel guru! 

Took me a while to work out how to create the key.... I was entering my Bing ID for a while. But I can't blame you for not accounting for idiots! 

 :Smilie:

----------


## Kyle123

The distance returned is in Km not miles  :Wink:  And this comes from Microsoft rather than google

----------


## Kyle123

The code should go in a module, not the sheet. Insert a module and put the code there  :Smilie:

----------


## jamington2004

yes it does seem to work with many EU postcodes..... although you should put the country code after the postcode to be sure it picks it up properly... see my other thread here asking about an issue with France!  :Smilie: 

http://www.excelforum.com/excel-prog...nce-macro.html

----------


## James Donovan

Its really great, thanks for sharing such a data. It is really informative. Thanks one's again...  :Smilie:

----------


## trainingit

THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Stupidly, I was inserting the code in Sheet1... thanks again  :Smilie:

----------


## StefanoV12

Thanks Thanks Thanks!!!!

----------


## jt1986

This is a brilliant piece of code. Thanks very much.

----------


## FDibbins

It was stated in an earlier post that this gave distance in km's not miles

----------


## John.R

Just wanted to say thank you to Kyle123 for the code and info as well as the others who contributed information as well. Thank You

----------


## walyxlime

Thank you for this!

----------


## Kyle123

Unfortunately this code won't work on a mac, you'd need to be using a windows machine

----------


## Kyle123

You might have some luck with apple script, but I've never used it - though it should be possible.

A lighter approach may be to use a querytable - these are available in Mac VBA, record a macro and you should be able to alter the url accordingly

----------


## jimmykup

I've done some work with this code and changed it from a function to a macro. Here it is.

*Get Travel Distance in Miles*



```
Please Login or Register  to view this content.
```


*Get Travel Time in Hours*



```
Please Login or Register  to view this content.
```


What my code does is take a range of zip codes and gets the distance or time to travel there from a single zip code that you provide via a dialog box. If your zip code is in A1, select it and this macro will ask you for a starting zip code, you type it in and hit enter, and then the distance/time is dropped in to the cell right next to it, which in this case would be B1. You can select one cell, or many cells in a column.

The macro first checks that the two values it's searching for are are not empty. That way we can prevent any unnecessary calls to Bing. If your zip code doesn't match this criteria, you'll get a cell that says "Invalid Zip". If your zip matches this criteria, but Bing returns an error, the cell will instead say "Try Later; Bing Error". The most likely cause for this is you have made too many requests too quickly. Take a breather and try again later. If I knew what Bing's limit was I might try to code a pause in there, but I don't know how to tell.

I hope this helps anyone looking for another way of accomplishing this.

----------


## Kyle123

It does help, but it will be much more helpful if you remove the limitation of only numbers and 5 characters. This really only applies to a small number of countries, many more are alpha numeric

----------


## jimmykup

You're right, that was an addition I made because it's the only thing I'd be using for my work. I've gone ahead and edited it for other people's use. I'd like to keep checks in there, but I don't know foreign zip codes well enough to be able to know what characters to look for and then reject. My gut says to reject anything that isn't A-Z, 0-9, or a hyphen, but I don't know that for sure. Also, knowing what the smallest amount of digits is in any zip code would be helpful as well.

EDIT: After some research I'm tempted to add some light verification. I could go all out (and I'd really like to) with a regex that does a basic check that any country would pass. But as I've learned, zip codes can change and I'm sure that means new formats could be put into use one day. So I won't bother with it. I learned that currently the shortest zip code is 2 characters long, and that the longest zip code is 12 characters long. Furthermore, it looks like all zipcodes worldwide only use a-z, 0-9, hyphen, and space. But again, I'm hesitant to add that because I don't know if it'll change in the future.

I'm a sucker for overdoing it on verification of input. But I'll leave this one alone. Assuming anyone wants to go the extra mile, here's a regex that should do the trick. It's a catch-all for every zip code worldwide: ^[a-z0-9][a-z0-9\- ]{0,10}[a-z0-9]$ Obviously, if you know the zip codes you'll be handling, you can be more specific with your regex. And if you're from the US and you know you'll only need to verify domestic zip codes, this one will do: ^[0-9]{5}(-[0-9]{4})?$

It's all a lot of work initially, but it pays when you can prevent from sending Bing invalid requests. Especially when you're looking to get info on hundreds of zip codes.

----------


## jimmykup

Maybe I'm beating a dead horse here, but I've gone ahead and tried to improve this code. I hope that it helps people out one day.

*Get Travel Distance in Miles*



```
Please Login or Register  to view this content.
```


*Get Travel Duration in Hours*



```
Please Login or Register  to view this content.
```


I attempted to improve the efficiency by adding the data from successful API calls to an array. If you need to get the distance or duration for multiple zip codes, the modified code above will refrain from calling the Bing API if it knows its already gotten data for that zip code previously. I'm not exactly great at writing arrays, so my code might be written poorly. But the important thing is that it works! If anyone has any suggestions I'm all ears.

----------


## Triumph_o

Excellent code, it works really well.

----------


## KatWat

You guys are amazing this code is brilliant and exactly what I need....thank you  :Smilie:

----------


## mgarcia

Just wanted to say this code came in really helpful.  Doing a small project for work and needed to pull up distances from my work to about 600 different locations.  Thank you Kyle123 for you work.  

Also, just wanted to note some things I had to make sure I did to get it running:
Save the code from the first post to a module, not a sheet
Save the file as .xlsm
Check the box as noted before to reference to Microsoft XML v6.0

These steps took care of getting the first line of code highlighted in yellow in Excel 2010 as well as the compile error.  I aslo noted I got the #VALUE error in my listing several times, but found out they were due to going from my location to places with no roads( like California to Hawaii).  Hope that helps others.

Thanks again!

----------


## Julia.G.Park

Got this to work by referencing XML 3.0 instead of 6.0

Thanks for the great tool!

----------


## Sinatlb

Thank you it works perfect!

----------


## RepentantWall

This helped out.

----------


## msexcelguy

thanks, ill note this for future use

----------

