Thread: Calculate the distance between 2 postcodes in miles Thanks: 0 Likes:  1 Post #5215821 (1)

1. 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. 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.

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!

3. Re: Calculate the distance between 2 postcodes in miles

Originally Posted by 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.

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!

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

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

5. 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

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

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

GetTimeinMins = Val(s(1)) / 60

End Function```
use as

=GetDistance("PO144HZ","PO155TT")

6. 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. 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?

8. 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. Re: Calculate the distance between 2 postcodes in miles

MSXML (I meant) **typo

10. 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

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

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.

User Tag List

Posting Permissions

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