Calculate the distance between 2 postcodes in miles
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Calculate the distance between 2 postcodes in miles

  1. #1
    Board Regular
    Join Date
    Jul 2013
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculate the distance between 2 postcodes in miles

    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

  2. #2
    Board Regular
    Join Date
    Dec 2012
    Posts
    299
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate the distance between 2 postcodes in miles

    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
    Last edited by easy2understandexcel; Aug 26th, 2015 at 06:29 AM.
    Checkout my youtube excel channel: Excel 101

  3. #3
    Board Regular
    Join Date
    Jul 2013
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate the distance between 2 postcodes in miles

    Quote Originally Posted by easy2understandexcel View Post
    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

  4. #4
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,371
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate the distance between 2 postcodes in miles

    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.
    Last edited by Comfy; Aug 26th, 2015 at 06:46 AM.

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,957
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Calculate the distance between 2 postcodes in miles

    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.

    Code:
    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=YOUR_MS_KEY"
        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=YOUR_MS_KEY"
    
        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")
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    New Member
    Join Date
    Jan 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate the distance between 2 postcodes in miles

    Hi, thanks for sharing this!

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

  7. #7
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,734
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Calculate the distance between 2 postcodes in miles

    1. Did you get your API key and modify the code as indicated? https://www.microsoft.com/en-us/maps...-bing-maps-key
    2. What postal codes did you use?
    Last edited by Scott Huish; Jan 24th, 2019 at 06:23 PM.
    Office 2010/365

  8. #8
    New Member
    Join Date
    Jan 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate the distance between 2 postcodes in miles

    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"

  9. #9
    New Member
    Join Date
    Jan 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate the distance between 2 postcodes in miles

    MSXML (I meant) **typo

  10. #10
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Calculate the distance between 2 postcodes in miles

    I have updated the two functions to parse the XML response and return the distance and time.

    Code:
    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •