Mileage Spreadsheet

[VPLUMWOOD]

I am wanting to try and create a mileage claim sheet whereby I can add in the post code of my starting point and end point and excel then works out the miles between - at the minute I do this manually by looking on google maps and seeing what it says on there,

Is there any form of intergration that would be able to do this simply by adding in postcodes?

Thank you

 

[Georgiboy]

There is but you would require an API key from Google, if you google search "Google distance matrix API" you will see what i mean. Once you have the API key and have enabled the distance matrix API via the google cloud console you would be able to use the distance matrix API within Excel VBA.

Google Maps Platform Documentation | Distance Matrix API | Google Developers

This is also possible with software from "HERE technologies" it is almost the same with the exception that HERE needs to convert the postcodes to Lattitude/ Longitude before its distance matrix will provide an answer.

I will provide the code below for a function that returns miles from origin and destination postcode/ city/ POI:

Public Function GetDistance(start As String, dest As String)
    Dim firstVal As String, secondVal As String, lastVal As String
    Dim apiKEY As String
   
    apiKEY = "API_Key_Goes_Here" 'API key
    firstVal = "https://maps.googleapis.com/maps/api/distancematrix/json?origins="
    secondVal = "&destinations="
    lastVal = "&mode=car&language=en&sensor=false&key=" & apiKEY
    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) / 1609.344 'meters to miles conversion
   
    Exit Function
ErrorHandl:
    GetDistance = "Error"
End Function

The code could be updated somewhat but i just tested it and it is working fine.

Hope this helps