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
You'll also need to set a reference to Microsoft XML v6.0
Function GetDistance(sPCode As String, ePcode As String) As Double Dim t As String Dim re As XMLHTTP t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=YOUR_MS_KEY" Set re = New XMLHTTP re.Open "get", t, False re.send Do DoEvents Loop Until re.readyState = 4 With re s = Split(.responseText, "<TravelDistance>") End With GetDistance = Val(s(1)) End Function Function GetTimeinMins(sPCode As String, ePcode As String) As Double Dim t As String Dim re As XMLHTTP t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=YOUR_MS_KEY" Set re = New XMLHTTP re.Open "get", t, False re.send Do DoEvents Loop Until re.readyState = 4 With re s = Split(.responseText, "<TravelDuration>") End With GetDistance = Val(s(1)) / 60 End Function
Last edited by Kyle123; 01-09-2012 at 05:34 PM.
Click the * below to say thanks
Girls sleep with guys who use photoshop, but marry the ones who work with Excel
Corduroy pillows: They're making headlines!
Did you mean: recursion
http://www.google.com/search?hl=en&q=recursion
I have managed to get the API key and I have put this into the cose you have kindley supplied but, I also need the Forumla that needs to go into the cells in excel at present when I was using Google the forumula used was =getGoogDistanceTime(C47,C48) Can you advise what I change this too.
Thanks
Shazz
You just use the name of the function, so =getdistance(a1,a2) etc
Click the * below to say thanks
Girls sleep with guys who use photoshop, but marry the ones who work with Excel
Corduroy pillows: They're making headlines!
Did you mean: recursion
http://www.google.com/search?hl=en&q=recursion
Thank you for posting this. This is extremely helpful. I got a key and inserted in the code, but I don't think I'm referencing the XML correctly. I try and use the function and I get a compile error [user-defined type not defined], that highlights the "Dim re As XMLHTTP" part of the code
What am I doing wrong?
Apologize in advance if this question is basic or ill-defined.
Adkf30, welcome to the forum.
Did you read this bit from the original post: "You'll also need to set a reference to Microsoft XML v6.0"
In the VB Editor click Tools > References and scroll down the list and select that option.
Thanks very much Paul. That did the trick and now the GetDistance function works smoothly.
But when I try and use the GetTimeinMins function I get a:
"function call on left hand side of assignment must return Variant or Object" error at
GetDistance = Val(s(1)) / 60
Ideas on what's happening now?
Note: I am running Excel 2010, not 2007 as my profile originally stated. Profile has been updated to reflect that.
Never mind. Figured it out.
In the line referenced in my previous post, I changed "GetDistance" to "GetTimeinMins" and that did the trick.
Much appreciate the help. Thx everyone.
Ah yes, good spot, I'll change it
Click the * below to say thanks
Girls sleep with guys who use photoshop, but marry the ones who work with Excel
Corduroy pillows: They're making headlines!
Did you mean: recursion
http://www.google.com/search?hl=en&q=recursion
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.
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!
I don't know what I have done wrong, I get invalid name error in the field where I getdistance in my workbook.
I made the reference as well as put the code in the VBA editor
nevermind, it works flawlessly.
This really is quite excellent code!
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!
Hi again..... I have been doing some testing of this code using an extract of all the visits made by our sellers, to calculate total distance travelled.
Has anyone found any issues where the formula returns a different value than if you go to google maps / get directions? (this is where I think the VB code points to)
The example if I have is =getdistance(TN23DG,ME24NR)
My forumula returns 41 miles...... but google maps shows 26 miles? Quite a difference!
I checked google maps and it does find the exact postcodes when I click on the map location....
Any ideas anyone - its driving me nuts!
Kind regards
Jamie
The distance returned is in Km not milesAnd this comes from Microsoft rather than google
Click the * below to say thanks
Girls sleep with guys who use photoshop, but marry the ones who work with Excel
Corduroy pillows: They're making headlines!
Did you mean: recursion
http://www.google.com/search?hl=en&q=recursion
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks