Closed Thread
Results 1 to 27 of 27

Getting Distances and DriveTimes

  1. #1
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Getting Distances and DriveTimes

    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.
    Last edited by dominicb; 05-31-2018 at 06:41 AM. Reason: Edited at Kyle's request to demonstrate late binding

  2. #2
    Registered User
    Join Date
    02-24-2012
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Getting Distances and DriveTimes

    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.

  3. #3
    Registered User
    Join Date
    04-13-2011
    Location
    no
    MS-Off Ver
    no
    Posts
    2

    Re: Getting Distances and DriveTimes

    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!

  4. #4
    Registered User
    Join Date
    05-09-2012
    Location
    Brighton UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Getting Distances and DriveTimes

    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!


  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Getting Distances and DriveTimes

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

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Getting Distances and DriveTimes

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

  7. #7
    Registered User
    Join Date
    05-09-2012
    Location
    Brighton UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Getting Distances and DriveTimes

    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!

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

  8. #8
    Registered User
    Join Date
    07-19-2012
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Getting Distances and DriveTimes

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

  9. #9
    Registered User
    Join Date
    09-20-2012
    Location
    Wales, UK
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Getting Distances and DriveTimes

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

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

  10. #10
    Registered User
    Join Date
    01-10-2013
    Location
    Bratislava
    MS-Off Ver
    Excel 2010
    Posts
    0

    Re: Getting Distances and DriveTimes

    Thanks Thanks Thanks!!!!

  11. #11
    Registered User
    Join Date
    11-06-2013
    Location
    Cornwall, England
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Getting Distances and DriveTimes

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

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Getting Distances and DriveTimes

    It was stated in an earlier post that this gave distance in km's not miles
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  13. #13
    Registered User
    Join Date
    12-27-2013
    Location
    Melville, NY
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Getting Distances and DriveTimes

    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

  14. #14
    Registered User
    Join Date
    04-11-2014
    Location
    Seattle, WA
    MS-Off Ver
    MS Office 2010
    Posts
    1

    Re: Getting Distances and DriveTimes

    Thank you for this!
    Last edited by walyxlime; 04-30-2014 at 02:14 PM.

  15. #15
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Getting Distances and DriveTimes

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

  16. #16
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Getting Distances and DriveTimes

    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

  17. #17
    Registered User
    Join Date
    08-03-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Getting Distances and DriveTimes

    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.
    Last edited by jimmykup; 07-08-2015 at 10:20 AM.

  18. #18
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Getting Distances and DriveTimes

    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

  19. #19
    Registered User
    Join Date
    08-03-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Getting Distances and DriveTimes

    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.
    Last edited by jimmykup; 07-08-2015 at 09:52 AM.

  20. #20
    Registered User
    Join Date
    08-03-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Getting Distances and DriveTimes

    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.
    Last edited by jimmykup; 07-13-2015 at 08:05 PM.

  21. #21
    Registered User
    Join Date
    10-09-2015
    Location
    Bristol, England
    MS-Off Ver
    Office 2010
    Posts
    1

    Cool Re: Getting Distances and DriveTimes

    Excellent code, it works really well.
    Last edited by Triumph_o; 10-09-2015 at 05:45 AM.

  22. #22
    Registered User
    Join Date
    12-12-2014
    Location
    Alloa
    MS-Off Ver
    2007
    Posts
    5

    Re: Getting Distances and DriveTimes

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

  23. #23
    Registered User
    Join Date
    01-17-2006
    Location
    Fresno, CA, USA
    MS-Off Ver
    2016
    Posts
    26

    Re: Getting Distances and DriveTimes

    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!

  24. #24
    Registered User
    Join Date
    01-26-2016
    Location
    Oakland, California
    MS-Off Ver
    365 ProPlus
    Posts
    1

    Re: Getting Distances and DriveTimes

    Got this to work by referencing XML 3.0 instead of 6.0

    Thanks for the great tool!
    Last edited by Julia.G.Park; 01-26-2016 at 04:47 PM.

  25. #25
    Registered User
    Join Date
    02-02-2016
    Location
    California
    MS-Off Ver
    2010
    Posts
    1

    Re: Getting Distances and DriveTimes

    Thank you it works perfect!
    Last edited by Sinatlb; 02-02-2016 at 07:29 PM.

  26. #26
    Registered User
    Join Date
    09-11-2016
    Location
    [email protected]
    MS-Off Ver
    Office 2016
    Posts
    1

    Re: Getting Distances and DriveTimes

    This helped out.
    Last edited by RepentantWall; 05-30-2018 at 08:27 PM.

  27. #27
    Registered User
    Join Date
    06-15-2018
    Location
    North Carolina
    MS-Off Ver
    16.14.1
    Posts
    7

    Re: Getting Distances and DriveTimes

    thanks, ill note this for future use

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1