Calculating Distances between Postcodes

awdl22125

New Member
Joined
Nov 7, 2003
Messages
5
I need to calculate the distances between my company head office and a range of customer addresses. Is there a way of doing this in excel perhaps using google maps?. I am in the UK so the distance needs to be calculated between postcodes.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
Googling

"distance between postcodes"

provides a few links that claim to do this.
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
For addresses, you can use this.

Code:
'A2 = "10 Downing Street, London, SW1A 2AA,United Kingdom"
'B2 = "Countryland, XY14 2LG, United Kingdom"
'C3 = GetDistance(A2, B2) / 1000 '580 in Kilometers

'https://analystcave.com/excel-calculate-distances-between-addresses/
'Returns distance in meters. Divide by 1000 to get kilometers.
'http://www.excelforum.com/showthread.php?t=1140863
Public Function GetDistance(start As String, dest As String)
    Dim firstVal As String, secondVal As String, lastVal As String
    Dim objHTTP As Object, URL As String, RegEx As Object
    Dim matches As Object, tmpVal As String
    
    firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
    secondVal = "&destinations="
    lastVal = "&mode=car&language=pl&sensor=false"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
    Set RegEx = CreateObject("VBScript.RegExp"): RegEx.Pattern = """value"".*?([0-9]+)": RegEx.Global = False
    Set matches = RegEx.Execute(objHTTP.responseText)
    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    GetDistance = CDbl(tmpVal)  'meters
    Exit Function
ErrorHandl:
    GetDistance = -1
End Function

Public Function MultiGetDistance(ParamArray args() As Variant) As Double
    MultiGetDistance = 0
    Dim startLoc As String, endLoc As String, i As Long
    For i = LBound(args) To UBound(args) - 1
        startLoc = args(i): endLoc = args(i + 1)
        MultiGetDistance = MultiGetDistance + GetDistance(startLoc, endLoc)
    Next i
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,099,458
Messages
5,468,769
Members
406,608
Latest member
G3TEN

This Week's Hot Topics

Top