Calculate Distance between Zip Codes

[mgirvin]

Dear Smartest Exclers In The World,

I have a table with row headers as source zip code and column headers as destination zip codes and I would like to calculate the distance between the two zip codes. Does anyone know how to do that, maybe a User Defined function or something?

 

[Taul]

take a look here
Don't bother with the part in module 2, it's not relevant (my bad)
http://www.mrexcel.com/forum/excel-questions/878224-help-geocoding-i-think.html

 

[SpillerBD]

see http://answers.microsoft.com/en-us/office/forum/office_2010-excel/calculate-distance-between-two-points-in-excel/40784a33-42f9-4dd0-a954-dca743f53c17

There was another item I saw recently when browsing at home. Will see If I can retrieve the page and post later.
Haven't done it myself, but they all use the Google API.

 

[Taul]

Sorry, just noticed your location is “Seattle”
I have added a conversion from km to miles ( * 0.62137119)

Start zip code in column A
Finish zip code in column B
Use this formula in column C and copy down

= ROUND(G_Distance(A2,B2),2)

Put this in module 1

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) * 0.62137119 'convert from km to miles
exitRoute:
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

 

[mgirvin]

Thanks Taul and SpliierBD!!

When I enter the function, I get a compiling Error. This part of the code is highlighted in yellow: "Function G_DISTANCE(Origin As String, Destination As String) As Double" and this part of the first Dim is highlighted; "myRequest As XMLHTTP60"

Any ideas?

 

[SpillerBD]

I was still thinking that using the Google Maps api you would still need a key. See https://developers.google.com/maps/documentation/distance-matrix/intro
You have a free usage volume, but at some point you may have to pay.

 

[Taul]

Thanks Taul and SpliierBD!!

When I enter the function, I get a compiling Error. This part of the code is highlighted in yellow: "Function G_DISTANCE(Origin As String, Destination As String) As Double" and this part of the first Dim is highlighted; "myRequest As XMLHTTP60"

Any ideas?

Set a reference to Microsoft XML v6.0
In the VBA editor - Tools>References>Microsoft XML, v6.0

Also, SpillerBD suggests using Google API - He is right, it gives more control and will return the road distance, my method gives straight line distance from the center of the zip codes.

 

[SpillerBD]

To say "I Like" seems like a bit of an understatement!

 

[Taul]

Thank you sir, it’s all about getting the best result, whoever provides it.

I have looked at some downloadable spreadsheets that are out there, the only reasonable one I was able to find was from Juice Analytics called Geocoding Tool v3.1 .xls search for it on line, it’s free.
It uses Yahoo geocoding and requires a Yahoo account, it works great for the US but not very good for the UK, so I abandoned it but it may be useful to you.
There may be a Google maps version but it didn’t exist a few years ago when I was playing with this sort of thing. Or maybe I should say, I couldn’t find one.

Paul.

 

[SpillerBD]

PS. I think the returned value is a fastest road directions, not as the crow flies.
I compared between directions on the webpage and the macro, and that was the same distance, Which between my TN to WA locations there is no straight road path....