Google Drive time

Good day all, I have (via this forum) found and customized a macro that will give me Time and distances between 2 cities. This was working perfectly until today when I opened up the spread sheet to use it and now I am getting this error

Runtime error '13'
Type Mismatch

I have figured out that (I believe) I am not getting anything returned from

TgetGoogDistanceTime = CDbl(Left(TgetGoogDistanceTime, InStr(1, TgetGoogDistanceTime, " ") - 1))

as it shows me TgetGoogDistanceTime = 'NOT FOUND'

The same goes with VgetGoogDistanceTime

Please Login or Register  to view this content.

I am in no way an expert so some hand-holding is required, but I would like to say Thank you in advance for your help!

This might help - Its over my head

BTW there another Thread here

thanks Kyle123! this works for Travel Time, is it possible to modify code to also calculate miles?

thanks agian

Col

Thanks Again! amazing! its works but its in Kilometres can I change this to Miles by changing the code or should I just create another column to divide by 0.621371192?

I have attempted to add &units=imperial to your code but returns VALUE

Thanks again!! I will use =(GetDistance(x,y,z))*0.621371192 method. Note that for some reason that I don't understand divide returns incorrect number but * returns correct number

Because dividing by a smaller number that 1 increases the value

Thanks, maybe I am missing something here but where do I put it?

I thought it replaced

Please Login or Register  to view this content.

But I am getting a Compile error sub or sub function no defined on Gethtml

Is this rounding for anyone else? All of my results are rounded and I'm looking for results accurate to tenth of mile...? Thanks in advance

Hi

Not sure if it's just me, but it's not working. Keep getting an subscript out of range issue? Any ideas? On the following line:

temp = (Split(Replace(Filter(Split(Replace("~" & .DocumentElement.XML, "</distance>", "<distance>~"), "<distance>"), "~", False)(0), "<text>", "</text>"), "</text>")(1))

Thanks

Edit: Ugh, never mind. I thought I had an answer to your question.

It's ok, I've tracked it down to an OVER_QUERY_LIMIT due to Google's 2500 limit on use per day.

I'll give it another go later.

Darn it Looks like I missed the working code

Does anyone have an alternative solution?

Thanks
Paul

I'm referring to this bit:

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.

Since we are only using the service to get distances within excel

Interesting; ok thanks. That makes sense to me now.

Sheesh.

The original method didnt use the api, just a normal web query to maps.google.co.uk, so could we use that instead without breaking the terms?

Correct it didn't use the API -- from what I can gather about web queries it would parse source code of the site to look for information. *I BELIEVE* (Since I still don't 100% understand this to this day) that Google changed the website source code to STOP putting in destination travel times and distances in the source code. It somehow references it... somehow... from something without being hard coded. And without it being hard coded it can't be parsed.

I'm pretty sure I'm correct about that: I started another thread about finding alternate ways to get distance as located here: http://www.excelforum.com/excel-gene...t-google.html?

But people tend to ignore my threads and make new ones and get responses. So it should be only a matter of time until someone repeats the same question and the conversation takes off like a horse at the track