Distances and DriveTimes

  • This is a modified version of code I have used elsewhere, it returns drive time in seconds, and distance in meters between 2 given postcodes. I've only tested this in the UK, but I don't see why it wouldn't work elsewhere.


    It uses the same services that the on-line TomTom route planner uses. I've read through the T&Cs of the service and can't find anything that using the service in this way would breach, but let me know if you think there are issues and I'll remove the code. (I used to use google APIs for doing this, but it breaches their T&Cs so this is an alternative - there is also a Bing Maps method that I've used, but find this more reliable).


    The only required parameters are the A-End and B-End Postcodes, without setting the other parameters, the function will assume the following:

    • It should not route to avoid traffic
    • It should not include traffic delays in drive time duration
    • Day of travel is Today
    • Time of travel is Now


    A sample usage:

  • Re: Distances and DriveTimes


    Hi I like that, I like that alot, so I've put it into a working file, with a few edits to make it easy for vb novices.


    Hence from XL file just enter POSTCODES and press a BUTTON, to make it so easy: http://www.1sar.karoo.net/DrivingDistanceCalc.xls

    Even simpler instructions
    From POSTCODE: A1
    To Destination POSTCODE: A2

    PRESS BUTTON "GET DRIVING DISTANCE" whilst connected to the internet! And then:


    MILES is displayed in F1
    TIME
    hh:mm:ss is displayed in F2


    Any objections from TOMTOM and the file hyperlink will be removed!

  • Re: Distances and DriveTimes


    Hi Kyle,


    Great piece of code , im a bit of a novice at VBA , how would i get this funcion to return the distance or time by inputing =getdistanceandtime() into a cell?
    I have a sheet with lots of rows of delivery data with columns for to and from postcodes ect. i would like to have the distance in one cell and the time in another, I cant work out how to do this from your code.



    mike...







  • Re: Distances and DriveTimes


    I am trying to get this to work and have inputed the above into a module. The top part only. I have tried "=GetTimeAndDistance(A5,A13)" and just get a #value error. Do i need to use the second part and were would i put this? A5 and A13 is my 2 postcodes.


    Any help would be greatly appreciated


    Many Thankls

  • Re: Distances and DriveTimes


    Quote

    I have tried "=GetTimeAndDistance(A5,A13)" and just get a #value error


    The code was not designed to work like a worksheet function.


    You have to Call the function from another sub, as shown above with the second piece of code (a macro). (Sub TestingDistance())


    If your data is in A5 and A13, then just change this line to read as follows

    Code
    Set data = GetTimeAndDistance([A5], [A13], True, True, vbFriday, 960)


    Then Run the Macro TestingDistance


    The results are returned to cells A1 and A2


    HTH
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Distances and DriveTimes


    I think i am missing something simple. I tried adding the extra code as a command button. just used the code here. Then tried adding straight into the VB for a blank sheet. Tried adding it as a module. I think i am missing on were to add the second bit of code really.


    Sorry very new to this level of spreadsheets.


    Many thanks


    James

  • Re: Distances and DriveTimes


    OK, so you need help understanding how to enter VBA code... see either of these two links:
    https://www.ablebits.com/offic…/add-run-vba-macro-excel/
    http://www.contextures.com/xlvba01.html


    Just copy all of the code above (BOTH the function and the sub), into a module and do as I told you above in post #7


    Note, this forum is not for asking for questions. Please ask in the main forum if you have difficulty in getting macros to work.


    Thanks
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Distances and DriveTimes


    Hi Ger, I've been using a slight variation of this code for a number of years now but it seems to have stopped working. I run monthly stats so can only say that it worked at the start of Oct but doesn't work now. It looks like I'm getting no response text when trying to get the GeoCode for the aEnd value. I wonder if tomtom may have changed something their end as if I paste the url into a browser (substituting aEnd for the postcode) I get an error saying "Oops, we couldn't find the route, location or mapview that you were looking for!". I've used the unaltered code above to prove it's not any of the changes I've made and it still doesn't work. Can someone confirm or deny that this solution no longer works? Many thanks.

  • Re: Distances and DriveTimes


    Update: I've had a play with the URL but I can't seem to get the variables right so nothing useful comes back in the response text :(


    they DO however offer a print function with some of the information on there so currently playing with that


    UPDATE #2:


    I went down the not so pretty route of running through an instance of IE, currently I can get to the page that holds the distance and time, but I don't know how to point to an element on another tab (info opens a second tab). If anyone wants to pick this up then here's the code - I'm not the best at manipulating IE in VBA to say the least....



    UPDATE #3: I've noticed there are conditional statements in the site's HTML that check the version of IE being used - based on the post in #15 I'm guessing there may be a window of opportunity of getting the original code to work if one could emulate an older version of IE... If anyone knows how to do this/if it's possible...

  • Re: Distances and DriveTimes


    Hi all here's an update, GoogleMapCode, easy to use:


    www.hkrebs63.karoo.net/files/DrivingDistance&Dir.xls
    To & From, Distance:Miles & HH:SS, Directions


    www.hkrebs63.karoo.net/files/DrivingDistanceVia6.xls
    To & From, Distance:Miles & HH:SS, for 6 different addresses with option for Via
    HINT: Don't save addresses after search, as won't update, unless column blanked.

    www.hkrebs63.karoo.net/files/DrivingDistancex100.xls

    To & From, Distance:Miles & HH:SS, for 100+ addresses
    HINT: Don't save addresses after search, as won't update.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!