Calculate the distance between 2 postcodes in miles

[Jaffabfc]

Hi,

i am wanting to be able to calculate the distance between 2 postcodes in excel.

i have a list of around 3000 places in the UK and i am wanting to know which is the closest to were i currently am.

To do this i am wanting to be able to put a column on the end (e.g. column f) to work out how much it is from A to B, is this even possible?

I am hoping there is something simple that i can do even if it means linking google maps into it.

Thanks

 

[easy2understandexcel]

Hi,
I think you will want to get the Latitude and Longitude coordinates for each postal code, then it can be calculated by formula.
I googled it and found a few places you can get it for free.

The trigonometry is a bit beyond me for the formula, but it looks like this will be a good starting point: link i found

Hope this helps!


Please check out my excel youtube channel Excel 101

 

[Jaffabfc]

Hi,
I think you will want to get the Latitude and Longitude coordinates for each postal code, then it can be calculated by formula.
I googled it and found a few places you can get it for free.

The trigonometry is a bit beyond me for the formula, but it looks like this will be a good starting point: link i found

Hope this helps!


Please check out my excel youtube channel Excel 101

if i do the longitude and latitude though wont that do the distance as a pigeon distance and not as in proper distance for people driving etc.

thanks

 

[Comfy]

What you are asking is quite substantial.

To start you off you are going to want to look at either the BING maps API or Google Maps API.

Make sure you read the licensing details so that you are sure you are using them within the terms of service.

You then might need an understanding of javascript/JSON and/or XML.

 

[MARK858]

There was some code from Kyle123 which seems to work which was posted in a different forum but I am sure Kyle123 won't object to me re-posting it here and also below is an excerpt from his post giving brief instructions.

I had originally posted a thread as to how to get this via the google API which is extremely simple, grabbing this and putting it into excel directly however breaches the terms of use.

Since then, I have discovered that Microsoft also offers a similar API, you do however need to sign up and get a key (here) and accept their terms of use.

I don't think posting the below contravenes anything, but if anyone feels otherwise let me know and I'll happily remove this.

In the below, you need to input your API key in the relevant place (IN RED)

You'll also need to set a reference to Microsoft XML v6.0

Distance is in Kilometers and comment out Open Explicit if you use it.

Function GetDistance(sPCode As String, ePcode As String) As Double
    Dim t As String
    Dim re As XMLHTTP


    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=[COLOR="#FF0000"]YOUR_MS_KEY[/COLOR]"
    Set re = New XMLHTTP

    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4

    With re
        s = Split(.responseText, "<TravelDistance>")
    End With

    GetDistance = Val(s(1))

End Function


Function GetTimeinMins(sPCode As String, ePcode As String) As Double
    Dim t As String
    Dim re As XMLHTTP

    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=[COLOR="#FF0000"]YOUR_MS_KEY[/COLOR]"

    Set re = New XMLHTTP
    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4

    With re
        s = Split(.responseText, "<TravelDuration>")
    End With

    GetTimeinMins = Val(s(1)) / 60

End Function

use as

=GetDistance("PO144HZ","PO155TT")

 

[bdeeve8]

Hi, thanks for sharing this!

So I used this code ad the distance computed was zero (0). Any idea what could be wrong?

 

[Scott Huish]

1. Did you get your API key and modify the code as indicated? https://www.microsoft.com/en-us/maps/create-a-bing-maps-key
2. What postal codes did you use?

 

[bdeeve8]

I did so. Modified the code as follows:
1. Deleted the "
2. Referenced MSML

and I got m API key. I'm using Canadian postal codes (which I assume is equally applicable: "T2P 4R5", "M9N 1K9"

 

[bdeeve8]

MSXML (I meant) **typo

 

[John_w]

I have updated the two functions to parse the XML response using the XML library, instead of the Split function, and return the distance and time.

Note - this code uses early binding of the XML library and therefore it requires a reference to Microsoft XML, v6.0. Set this via Tools -> References in the VBA editor.

Const APIkey = "YourAPIkey"

Function GetDistance(sPCode As String, ePcode As String) As Double

    Dim t As String, s As Variant
    #If VBA7 Then
        Dim re As XMLHTTP60
        Set re = New XMLHTTP60
    #Else
        Dim re As XMLHTTP
        Set re = New XMLHTTP
    #End If
   
    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=" & APIkey
   
    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4
   
    With re.responseXML
        .SetProperty "SelectionNamespaces", "xmlns:ns='http://schemas.microsoft.com/search/local/ws/rest/v1'"
        GetDistance = .SelectSingleNode("//ns:TravelDistance").Text
        Debug.Print .SelectSingleNode("//ns:DistanceUnit").Text
    End With
   
End Function


Function GetTimeinMins(sPCode As String, ePcode As String) As Double
    Dim t As String, s As Variant
    #If VBA7 Then
        Dim re As XMLHTTP60
        Set re = New XMLHTTP60
    #Else
        Dim re As XMLHTTP
        Set re = New XMLHTTP
    #End If
   
    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=" & APIkey
   
    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4
   
    With re.responseXML
        .SetProperty "SelectionNamespaces", "xmlns:ns='http://schemas.microsoft.com/search/local/ws/rest/v1'"
        GetTimeinMins = .SelectSingleNode("//ns:TravelDuration").Text / 60
        Debug.Print .SelectSingleNode("//ns:DurationUnit").Text
    End With
   
End Function

=GetDistance("T2P 4R5", "M9N 1K9") returns 2240.836018 (miles) for me.

Edited 29-Mar-2023 - removed forum's HTML markup around #If ... #Else ... #End If lines.