Postcode finder in Excel and caculate the distance between two postcodes

[alexba]

Hi All

I am in U.K. . I am working on a mileage expenses form for two distance from one location to another . I want intergrate postcode finder in excel . so when I enter postcode, the address will pop up. so I can caculate the distance for mileage claim.

For example: If I enter LS7 1AB, th address will be showing Lovell Park Hill,Leeds,LS7 1AB

Thanks a lot for your help in advance.

Alex

 

[Webbarr]

Forgive me if I'm wrong, but I don't think Excel has the capability to calculate distances between postcodes. The only way that I'm aware of to make this possible is to have a pre-built list of postcodes with x y z co-ordinates.

You would then need to use an INDEX() of some sort to extract the two x y z co-ordinates based on your post codes being row references & sum the difference to convert into miles. Sounds quite complicated actually. I'm hoping there's an easier solution & if there is I'd love to learn it!

 

[alexba]

Hi

I have spent a couple of hours online searching some threads regarding the postcode finder. I have got an VBA now.

But still have a bit problem with the highlighted line.



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = Range("postcode").Row And Target.Column = Range("postcode").Column Then
Dim IE As New InternetExplorer
IE.Visible = True
IE.navigate "http://www.royalmail.com/find-a-postcode?place=" & Range("postcode").Value
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE

Dim Doc As HTMLDocument
Set Doc = IE.document
Dim sDD As String
sDD = Trim(Doc.getElementsByTagName("<div title")(1).innerText)[/B]
Dim aDD As Variant
aDD = Split(sDD, ",")
Range("postcode").Value = aDD(1)



End If

End Sub


Please can someone help me .

Kind regards

Alex

 

[Taul]

Hi
This has been mentioned before, take a look here

http://www.mrexcel.com/forum/excel-...culate-distance-miles-between-post-codes.html

Paul.

 

[Taul]

Hi,
I played with this ages ago and just found the file , so I thought I would share it with you.
I can't recall where I got the info from but I just tested it again and it appears to work fine

setup your data like this

[TABLE="width: 344"]
<tbody>[TR]
[TD]Location 1[/TD]
[TD]Location 2[/TD]
[TD]Km[/TD]
[TD]Miles[/TD]
[/TR]
[TR]
[TD]LS7 1AB[/TD]
[TD]LS1 3AD[/TD]
[TD]2.0[/TD]
[TD]1.2[/TD]
[/TR]
[TR]
[TD]LS7 1AB[/TD]
[TD]LS2 9JT[/TD]
[TD]2.0[/TD]
[TD]1.3[/TD]
[/TR]
[TR]
[TD]LS7 1AB[/TD]
[TD]LS8 2LJ[/TD]
[TD]4.8[/TD]
[TD]3.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[/TR]
</tbody>[/TABLE]

In cell C2 use this UDF formula

=G_Distance(A2,B2)

In a new module paste this UDF

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

The answer is in Km but just multiply the answer by 0.621371 to get it to miles

Paul.

 

[kcmuppet]

Hi,
I played with this ages ago and just found the file , so I thought I would share it with you.
I can't recall where I got the info from but I just tested it again and it appears to work fine

Hi - I get User-defined type not defined at:

Dim myRequest As XMLHTTP60

I guess the current versions of Excel (I'm using 365) use a different xml protocol or something?

 

[kcmuppet]

Addressing the issue above, the compile error went away when I ticked "Microsoft XML v6.0" from Tools...References... in the VBA editor.

But for me the function till returns only zero. Can anybody help me understand why?

 

[Taul]

Hi,
Yes, it worked in 2016 but Google have changed things since then.
As far as I know, you will need to register your credit card with Google in order to get an API key (that was enough to put me off). They offer quite a lot of free usage calls but if you exceed their limits you get charged.
There was another request recently, link below. It may be worth following it up.

Distance between UK postcodes

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

www.mrexcel.com

Also do a general search on this board, I recall several threads relating to distance finding using Google and Bing

 

[kcmuppet]

Thanks, indeed I spent a couple of hours searching and found yours to be the most up-to-date or other's pointing to yours! Thanks anyway.

 

[Taul]

Hi,
Try this one

Getting Distances and DriveTimes

Getting driving distances and drive times seems to be a very popular topic recently. 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...

www.excelforum.com

It uses Bing maps and you will need to obtain your own API key.
I think if you exceed your usage limits, Bing will probably block further usage until your next quota, whereas Google prefer to charge you.
Good luck
Paul.