Distance between UK postcodes

[matthew.armitage]

Hi All

Does anyone have a working method for calculating the the distance between UK postcodes?

I have a method that has worked in the past, that I actually found on Mr Excel, but it no longer works

Function G_DISTANCE(Origin As String, Destination As String) As Double
Dim 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

Help appreciated

Thanks

Matt

 

[John_w]

Your code should work if you specify an API key, and maybe other changes. To obtain an API key you must register your project on the Google Cloud Platform and enable the Directions API, which your code uses.

Alternatively, if you only need calculated distances (and times) then you could call the Distance Matrix API, as shown here:

Get Google Maps XML data, parse it and input to Excel cells

With help from Stackoverflow I managed to get a working solution for getting Google Maps XML data, parsing it and inputting to Excel cells. However my next intention get it working for several requests when there are 5 different locations to be analyzed and data from each XML should be inputted...

www.mrexcel.com