Closed Thread
Results 1 to 43 of 43

Google Maps Driving Times

  1. #1
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Google Maps Driving Times

    Hi all

    I would like to be able to have a spreadsheet which looks up data from google maps so that when I input two addresses or landmarks, it would tell me the driving time and if possible the driving miles.

    For example:

    Input
    A1 = Start Address
    A2 = End Address
    ---------------------------------
    Output
    A3 = Journey Time
    A4 = Miles

    Now I know that this sounds a lot simpler than it is, but it would really make my life a lot easier if this is possible.

    Any ideas?

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Google Maps Driving Times

    hey thegooser123

    found this by Shred Dude at VbaX
    Please Login or Register  to view this content.
    hope it helps
    Last edited by DonkeyOte; 02-06-2011 at 05:58 AM. Reason: edit: added parseGoog which was missing from original
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    Thanks for the help

    How would I implement this into my spreadsheet? I know how to insert it as code but lost as to what to do after that. Where would I put the Start and finish point?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Google Maps Driving Times

    Given you wish to return distance & time separately you would need to modify the first function slightly, eg:

    Please Login or Register  to view this content.
    then

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    I just keep getting #NAME?

    I think that this is something I'm doing wrong as opposed to something wrong with your coding. Do you have any suggestions to what I can do or a sample sheet for me?

    Appreciate all your help.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Google Maps Driving Times

    The functions must all reside in a standard module in VBE (Insert > Module) - when re-opening the file ensure macros are enabled.

  7. #7
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    Got it.

    This works great, however is there any way that I can turn the times into an actual time format (i.e "01:34") ? I need to be able to add times together, but this is giving me the driving time as a text result (i.e "1 hour 34 mins")

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Google Maps Driving Times

    A few approaches - one would be to revise the UDF further:

    Please Login or Register  to view this content.
    formatting cells containing the time returns to [h]:mm

  9. #9
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    You have been a massive help my friend. We have just started a taxi company and its down to me to orginise the bookings. When someone calls I have to do 5 or 6 route lookups on google maps to tell them when we can pick them up. This will let me just put in the pick up and drop off point and do it all for me pretty much instantly.

    So thank you.

    One quick thing though (and I know I am pushing it here), is it possible to get the miles as a number instead of a text result like you have done to the times?

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Google Maps Driving Times

    perhaps simplest (at this time of night!) to just modify the final part of the UDF such that instead of:

    Please Login or Register  to view this content.
    you have

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    01-10-2011
    Location
    Lake Havasu City, AZ
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Google Maps Driving Times

    Hello. I have a similar need; since I'm now a 1099 on-my-own "consultant", I need to prove my mileage to the IRS. I have addresses in Google Calendar for each of my appointments, and my starting point is set.

    Using Excel 2010, I'm trying to use the above code w/no luck. I've gone into VBA & done an INSERT MODULE with the info above. I then put two different addresses into A1 & A2, made B1 & B2 the same as listed, but they keep coming back with #VALUE! .

    I'm a Newb for programming in Excel, but this is getting exciting.

    I've included the code info, but I don't see as it's any different from above.

    Suggestions?
    Please Login or Register  to view this content.
    and this...
    Please Login or Register  to view this content.
    Thanks!
    scott
    mailto:[email protected]

  12. #12
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Google Maps Driving Times

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

  13. #13
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    This is working brilliant.

    Until now my only option was to purchase mappoint, then purchase an excel add in which gets the times and mileage. The problem was that in the small part of the world that I need the data for, mappoint was just not accurate enough for me to be able to use, and to edit it on the mappoint side was a nightmare. Google maps is however pretty much perfect.... so thanks for your help.

    To the user who was getting the #VALUE message, you need to check that google recognises the cells. The best thing to do is to type the address into google maps. If it finds it straight away then it'll find it in the spreadsheet. If google maps gives you suggestions of what it think that you might mean, then the spreadsheet will return the error.

    Fantastic bit of code, thank you very much for your help.

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Google Maps Driving Times

    Actually, the #VALUE! error may be due to the time rather than the cells not found.

    If the time is greater than minutes and less than hours, for example, 1 hour 18 mins, the function will fail.

    I modified it to cope with hours and hour in the return.

    I have two versions of the function now; one that returns the text values and one that returns the numeric values for time and distance.

    Please Login or Register  to view this content.


    thanks to the originator of the functions and DonkeyOte for modifying it to cater for input from cells.


    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  15. #15
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Google Maps Driving Times

    good spot TMS though you could just embed a further Replace

    Please Login or Register  to view this content.
    I have saved my version accordingly.

    edit: should note it would still fail for minute distances but I would live with that as it would flag oversights.
    Last edited by DonkeyOte; 02-10-2011 at 09:19 AM.

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Google Maps Driving Times

    @DonkeyOte: hah, you make it look so easy :-)

    I tried all sorts to overcome the problem when I realised what it was and I really, really struggled. eventually, I came up with an answer ... but I prefer yours, much neater.

    Regards
    Last edited by TMS; 02-12-2011 at 05:33 PM.

  17. #17
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Google Maps Driving Times

    A more robust version might be:

    Please Login or Register  to view this content.
    that should handle hour(s), min(s) & sec(s) and combinations thereof.

  18. #18
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    Now then...

    This worked perfectly for me until 3 days ago. then it suddenly stopped working!

    I have tried everything that I can think of to get it fixed, but to no avail. The only thing that I can think is google may have changed something which has stopped it from working.

    Is anyone else experiencing problems with this?

    Any help would very much be appreciated.

  19. #19
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Google Maps Driving Times

    I have to be honest and say I haven't touched this since the last dialogue.

    Sadly, it seems that something has changed. I'm getting #VALUE! for parameters that previously worked and returned distances and time.

    Regards

  20. #20
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Google Maps Driving Times

    It does seem to have stopped working. I'm guessing the HTML that is being returned has changed so the function to find the time/distance is no longer able to find the value.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  21. #21
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    Oh no!!!

    Is there any alternative? Either free or paid?

  22. #22
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Google Maps Driving Times

    It shouldn't be too hard to adjust the code to pick up the info again. Will have a look later if I get a chance and nobody else has.

    Dom

  23. #23
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    You would be a bit of a legend in my book if you could figure this out for me.

  24. #24
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Google Maps Driving Times

    Made a couple of changes to this function that's called which seems to make it work now:

    Please Login or Register  to view this content.

    Dom

  25. #25
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    Quote Originally Posted by Domski View Post
    Made a couple of changes to this function that's called which seems to make it work now:

    Please Login or Register  to view this content.

    Dom
    This is working perfectly for the distances in miles, however the driving times are all messed up.

    Any ideas?

  26. #26
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Google Maps Driving Times

    What did it used to return for them? I never tried it to be honest.

    Dom

  27. #27
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    Quote Originally Posted by thegooser123 View Post
    This is working perfectly for the distances in miles, however the driving times are all messed up.

    Any ideas?
    =GetGoogDistanceTime(A1,B1,"time")
    I think its giving me the mileage though.

  28. #28
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Google Maps Driving Times

    I meant what sort of value did it used to return? Time in hours/mins/seconds?

    Dom

  29. #29
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    Quote Originally Posted by Domski View Post
    I meant what sort of value did it used to return? Time in hours/mins/seconds?

    Dom
    Sorry Dom...

    it used to return time formatted as hh:mm.

  30. #30
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Google Maps Driving Times

    Maybe:

    Please Login or Register  to view this content.

    Dom

  31. #31
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    works a charm mate.

    Thank you so much for all your help

  32. #32
    Registered User
    Join Date
    07-28-2011
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    4

    SOLVED - Re: Google Maps Driving Times

    Hello,
    Did anyone tried the above code on excel 2010. I keep on getting #Value!. This is what i did..
    Copied the code in a new module and saved the file as *.xlsm extention.
    Please help...

    A1=Turin, Italy
    A2=Milan, Italy
    --------------------------
    A3=#VALUE!

    Formulae: getGoogDistanceTime($A$1:$A$2;"distance")

    Actually, i'm only interested in the KM calculation part

    Many Thanks,
    Ravi
    Last edited by sk80rb0i; 07-28-2011 at 02:20 PM. Reason: SOLVED

  33. #33
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Google Maps Driving Times

    Try a semicolon between A1 and A2 instead of a colon. Does that work?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  34. #34
    Registered User
    Join Date
    07-28-2011
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Google Maps Driving Times

    With a semicolon it says ---> NOT FOUND

  35. #35
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Google Maps Driving Times

    Works fine in 2007. Can't see it being different in 2010.


    =getGoogDistanceTime($A2,$B2,C$1) .... C$1 has "distance" in it. Note that distance and time text values are case sensitive.


    Try just Turin and Milan

    HTML Code: 

    Regards
    Last edited by TMS; 07-28-2011 at 02:02 PM.

  36. #36
    Registered User
    Join Date
    07-28-2011
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Google Maps Driving Times

    Thank you very very much. It was the case sensitive thing.

  37. #37
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Google Maps Driving Times

    You're welcome. Thanks for the rep.

  38. #38
    Registered User
    Join Date
    07-20-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Google Maps Driving Times

    I'd just like to thank everyone in this thread for their input in this solution. You know you can pay 000's for things like this, but with a bit of ingenuity and thought it's been solved with a simple bit of Excel.

    Great stuff!

  39. #39
    Registered User
    Join Date
    08-30-2010
    Location
    Cornwall, UK
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Google Maps Driving Times

    Quote Originally Posted by adam2804 View Post
    I'd just like to thank everyone in this thread for their input in this solution. You know you can pay 000's for things like this, but with a bit of ingenuity and thought it's been solved with a simple bit of Excel.

    Great stuff!
    Agreed!!!

    I don't know if the people that helped out here are still subscribed to the post, but a massive thank you. I still use this multiple times an hour and it really has made my working day easier. Thank you so much!

  40. #40
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Google Maps Driving Times

    It is pretty cool! I tried to come up with something similar a while back but failed miserably so learnt some new stuff figuring out the problem when it occurred.

    Dom

  41. #41
    Registered User
    Join Date
    07-27-2010
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Google Maps Driving Times

    I spent so much time looking for something like this.. thanks!

    I was wondering if there is any way to make this code run faster? I'm guessing no, as the longest time component of the code seems to be retrieving the information from Google. Thought I'd ask though, as I have a list of 40,000 zip code pairs to calculate. At just over a second per pair, this takes a long time.

    Maybe it's time for me to upgrade my internet service!

  42. #42
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Google Maps Driving Times

    Can anyone post a working example of this file please? I would be intrested to see if it works for UK addresses or post codes (I am using excel 2007)

    To what level does it go to, can I put in a street address?

  43. #43
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Google Maps Driving Times

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

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