Wouldn't it be useful if...

[TinaP]

The first line should be two lines...

Function G_DISTANCE(Origin As String, Destination As String) As Double
Dim myRequest As XMLHTTP60

As far as I know, there's no such thing as DoubleDim.

 

[mnordeen]

Thanks Tina,
sorry everyone I guess I got in a hurry because for once I thought I had an answer.

 

[TinaP]

I thought you were just trying to prove your signature.

If you do it right, No one will know you've done anything at all.

I was thoroughly confused until I re-read your post saying that the distances were in km.

 

[diddi]

@Gerald
I have 2 comboboxes and a couple of labels on a udf. when setting up the contents of the comboboxes, make sure that the place names match the syntax used by google maps.

this is straight from my project and works great in Australia.

Sub GetMapsDistances()
    Dim xhrRequest As XMLHTTP60
    Dim domDoc As DOMDocument60
    Dim ixnlDistanceNodes As IXMLDOMNodeList
    Dim ixnNode As IXMLDOMNode
    Dim TtlDist As Long
    
    ' Donated to MrExcel users by diddi
    ' Read the data from the website
    
    Set xhrRequest = New XMLHTTP60
    xhrRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" & ComboBox2.Value & "&destination=" & ComboBox3.Value & "&sensor=false", False
    xhrRequest.send

    Set domDoc = New DOMDocument60
    domDoc.loadXML xhrRequest.responseText

    Set ixnlDistanceNodes = domDoc.selectNodes("//step/distance/value")

    '  Total up the distance from node to node
    TtlDist = 0
    For Each ixnNode In ixnlDistanceNodes
        TtlDist = TtlDist + Val(ixnNode.Text)
    Next ixnNode

    Label13.Caption = "One way distance is about " & Int(TtlDist / 1000) & "km"
    Label14.Caption = "Return trip is about " & Int(TtlDist * 2 / 1000) & "km"
    
    Set ixnNode = Nothing
    Set ixnlDistanceNodes = Nothing
    Set domDoc = Nothing
    Set xhrRequest = Nothing
End Sub

 

[Gerald Higgins]

nmordeen and TinaP - thank you. I should have said, I had enabled the reference, I'm guessing it was the glitch that Tina spotted that was causing my problem. I'm not in a position to check right now, but it sounds likely.

diddi - thanks for sharing your method as well.

I will try both of these methods - it might be next week now before I can do it properly, so thanks in advance to all !

 

[schielrn]

Both solutions I believe return kilometers, so in the US, you would want to multiply by 0.621371 to get the results in miles.

Or change the functions to return strings instead of doubles and use:

//leg/distance/text instead of //leg/distance/value

You can also return the travel time as well.

 

[Gerald Higgins]

nmordeen - I got your solution to work perfectly (with help from TinaP) - this is fantastic ! Thanks very much !
diddi - thanks for posting your solution too. As nmordeen's works, I haven't rushed to test your's, but I will try and get round to it.
schielrn - you actually need to divide kms by 0.621371 to get miles, not multiply

Thanks again everyone !

 

[Kyle123]

The only thing to be aware of is that this breaches the terms of use for Google, because of this, I wrote an alternative using Bing maps which does not have the same stipulations on use: Getting Distances and DriveTimes

 

[Gerald Higgins]

Kyle123 - thanks for that.

Which part of the TOU do you think it breaches ?
Is it the bit about disallowing "mass downloads or bulk feeds" ? If yes, is there any word on what qauntity of downloads would qualify as "mass" or "bulk" ?

 

[Kyle123]

Hi Gerald, it's this:

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.

https://developers.google.com/maps/documentation/distancematrix/