Wouldn't it be useful if...

[Gerald Higgins]

OK thanks Kyle123 - I'll take a look at your Bing workaround.

I was using nmordeen's suggestion.
Although I haven't tested diddi's solution myself, I'm guessing this may also fall foul of Google's ToU ?

 

[Kyle123]

I'm afraid so, they both use the same API

 

[Superstar31]

If you have 2 lat/lon points, you can use the formula below. Everything I have found shows that it is reliably accurate. Of course there is probably some variable that needs to be accounted for that makes it perfect, but this works well enough for my purposes:

=ACOS(COS(RADIANS(90-Lat1))    *COS(RADIANS(90-Lat2))   +SIN(RADIANS(90-Lat1))    *SIN(RADIANS(90-Lat2))   *COS(RADIANS(Long1-Long2)))*3958.756

How do I uses this?

Well I found this, I didn't write it but it works. its in KM but that is an easy conversion if you want Miles.

You need to enable the Reference; In VBA Tools/Reference Select "Microsoft XML,V6.0"

in Cell say C1 use

=G_Distance(A1,B1)

< type the address or zip to/from in A1 and B1.

Function G_DISTANCE(Origin As String, Destination As String) As DoubleDim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim distanceNode As IXMLDOMNode
    G_DISTANCE = 0
    On Error GoTo exitRoute
    Origin = Replace(Origin, " ", "%20")
    Destination = Replace(Destination, " ", "%20")
    Set myRequest = New XMLHTTP60
    myRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
        & Origin & "&destination=" & Destination & "&sensor=false", False
    myRequest.send
    Set myDomDoc = New DOMDocument60
    myDomDoc.LoadXML myRequest.responseText
    Set distanceNode = myDomDoc.SelectSingleNode("//leg/distance/value")
    If Not distanceNode Is Nothing Then G_DISTANCE = distanceNode.Text / 1000
exitRoute:
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

I believe this can be modified to find public bathrooms.

What needs to be changed to make it so Miles and not KM?

And yes I realize this is an old thread, but it covers exactly what I need and hopefully the original people are still subscribed to it!

Well that's a lie.. exactly would be doing the above using long and latitude... as the crow drives!

 

[shg]

What needs to be changed to make it so Miles and not KM?

Multiply by 0.621371

 

[Kyle123]

You don't need a web service to get straight line distance between 2 coordinates, you can do that with a formula

 

[Superstar31]

Multiply by 0.621371

I thought someone said divide? So I don't need to mess with the module, just take the answer and multiple it my .621371

You don't need a web service to get straight line distance between 2 coordinates, you can do that with a formula

As the grow drives (driving directions)

 

[shg]

How many miles in a kilometer?

 

[Superstar31]

How many miles in a kilometer?

I understand what you're saying but this is the message i saw and nobody corrected it

schielrn - you actually need to divide kms by 0.621371 to get miles, not multiply

and it WAS NOT giving me the correct answer. So I wanted to make sure everything I did was correct! Thanks!

 

[shg]

You're welcome.