I have a spreadsheet I would like to be able to type start and end postcode and have the journey time and Milage display using google maps
The Cells I want to use are as below.
Input
J26 = Start Post Code
J27 = End Post Code
Output
J28 = Journey Time
I30 = Miles
I would apprecaite your help greatly.
Shazz
x
You might want to have a look at this thread: http://www.excelforum.com/excel-gene...ing-times.html
There appears to be a problem with it which I've said I'll look at if I get the chance.
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.
I did have a look at that thread and got very confused so i though I would post my own thread so that the code would be unique to my needs, I am a newbie at all this and really need it spelling out to me basically
If you would be so kind and let me know what you can come up with I would be exstremely greatful.
Shazz
x
I've posted some revised code to that thread.
Dom
Hi Dom,
I have looked at the revised Code below, would you ba able to advise how I would adapt it for use in my spread sheet with the below Celles as the references, Pretty Please...... as I can not work out what I need to change.....
Input
J26 = Start Post Code
J27 = End Post Code
Output
J28 = Journey Time
I30 = Miles
Please Login or Register to view this content.
All the code needs to go in a regular module.
You would then just use formula like this:
Journey Time: =getGoogDistanceTime(J26,J27,"time")
Distance: =getGoogDistanceTime(J26,J27)
Dom
I am really sorry Dom, you are going to get fed up with me - It is not working, all I get in my spreadsheet is #Name where the info should be displayed.
Would you be able to upload an example excel workbook for me......
Shazz
x
No worries, sample file attached.
Dom
Works great for me Dom!.....good job.
Life is like a roll of toilet paper. The closer it gets to the end, the faster it goes.
John Wright
Cheers John. The credit really lies with Shred Dude @ VBAExpress, I just fixed it to take account of changes to the HTML that is returned by Google.
Dom
Great it works now, thanks so much and for your time spent helping me.
Shazz
Hi there
Has there been any changes recently that has caused this formula / module data to stop it from working as this worked for me up until last month now it returns a '0' value?
Anyone know why this may be or know an alternative solution?
Thanks
Milton
Unfortunately the HTML that the query returns has changed significantly. I had a quick look at it the other day but wasn't able to figure it out as the obvious references to the time and distance have disappeared. I'm really busy at the moment but if I get a chance I'll have a look some time soon.
Dom
Hi Dom,
Have you had a chance to take a look at this yet? I was not aware it had stoped working until recently when I needed to use it.
Appreciate your help.
Shazz
You can't do it this way anymore unfortunately, although you can use the google distance matrix API, it breaches the google T&Cs.
I wrote a tip here on how you can use Bing Maps for the same thing
http://www.excelforum.com/tips-and-t...rivetimes.html
You'll need to sign up for an API key and accept their terms and conditions
I thought that might be the case. Great shame as it was a really useful little tool for quite a few people.
Dom
It was and the API is even better, it is extremely fast, just needs the data extracting from XML. Unfortunately google state that you cannot use the API unless in conjunction with displaying the data on a Google map.
I use this function to get the distance between two points.
I didn't write this code myself. I don't remember where I found this. I am sure I just did a Google search for "VBA to retrieve distance from Google Maps" or something. When I search for "Function GoogleGetDistance(ByVal startFrom As String, ByVal endAt As String) As Double" in Google I get a few likely sites that I found this code in. Pick one and it is probably where I found it.Please Login or Register to view this content.
Yep, that and the distance API would be the ones that breach the T&Cs
Big shame really
Last edited by Kyle123; 01-19-2012 at 12:16 PM.
So, what would satisfy the T&C? Would having a window to display the directions page work?
Not from the below, you would have to display a google map:
For the distance matrix:
For directions as in your post:Use of the Distance Matrix API must relate to the display of information on a Google Map; for example, to determine origin-destination pairs that fall within a specific driving time from one another, before requesting and displaying those destinations on a map. Use of the service in an application that doesn't display a Google map is prohibited.
So I guess if you put a google map in your wb with the data plotted on it, it would be fine - albeit a massive PITA!Note: the Directions API may only be used in conjunction with displaying results on a Google map; using Directions data without displaying a map for which directions data was requested is prohibited. Additionally, calculation of directions generates copyrights and warnings which must be displayed to the user in some fashion. For complete details on allowed usage, consult the Maps API Terms of Service License Restrictions.
Seems much simpler to use Bing
Last edited by Kyle123; 01-19-2012 at 12:37 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks