Simple Mileage Calculator

[MattCallanan]

Hey guys,

Just wondering if somebody would be able to help me with a simple mileage calculator on Excel for use on an expense form?

The starting postcode is entered into column D (starting at D13), the end postcode In column E (starting E13) and the result is required in column B (B13). The mileage is then used to calculate the cash value of the claim in H.

Is there a simple way of doing this?

Thanks

 

[L33]

Unfortunately, what you're after isn't simple as Excel wouldn't have any way of knowing the miles between two postcodes without some sort of database behind it - unlikely you'd be able to find something to use without paying for it.

You could use a web query perhaps, but I wouldn't know how to do that. This formula will return a hyperlink to a Google Maps page that will show the mileage between your specified postcodes, but how you'd get that info programmatically back in to your spreadsheet is the (very) tricky bit.

=HYPERLINK("http://maps.google.co.uk/maps?saddr="&SUBSTITUTE(D2," ","+")&"&daddr="&SUBSTITUTE(E2," ","+"))

 

[MattCallanan]

Well I was sent this but can't get to work:

Public Function getGoogDistanceTime(rngSAdd As Range, rngEAdd As Range, Optional strReturn As String = "distance") As Variant
Dim sURL As String
Dim BodyTxt As String
Dim vUnits As Variant
Dim dblTemp As Double
Dim bUnit As Byte
sURL = "http://maps.google.com/maps?f=d&source=s_d"
sURL = sURL & "&saddr=" & Replace(rngSAdd(1).Value, " ", "+")
sURL = sURL & "&daddr=" & Replace(rngEAdd(1).Value, " ", "+")
sURL = sURL & "&hl=en"
BodyTxt = getHTML(sURL)
If InStr(1, BodyTxt, strReturn, vbTextCompare) = 0 Then
getGoogDistanceTime = "Error"
Else
getGoogDistanceTime = parseGoog(strReturn, BodyTxt)
If LCase(strReturn) Like "time*" Then
vUnits = Split(getGoogDistanceTime)
For bUnit = LBound(vUnits) To UBound(vUnits) - 1 Step 2
dblTemp = dblTemp + _
Val(vUnits(bUnit)) / Choose(InStr(1, "hms", Left(vUnits(bUnit + 1), 1), vbTextCompare), 24, 1440, 86400)
Next bUnit
getGoogDistanceTime = dblTemp
Else
getGoogDistanceTime = Val(getGoogDistanceTime)
End If
End If
End Function

Public Function getHTML(strURL As String) As String
Dim oXH As Object
Set oXH = CreateObject("msxml2.xmlhttp")
With oXH
.Open "get", strURL, False
.send
getHTML = .responseText
End With
Set oXH = Nothing
End Function

 

[L33]

No, nor can I, but have just found something else. Have a look at this forum post:
http://www.mrexcel.com/forum/showthread.php?t=600750

It contains a link to download a workbook from a user called "trunten". It has a lookup table converting postal districts to longitudes and latitudes then a complex formula to determine the mileage. Could be exactly what you need.

 

[L33]

Actually, scratch that.. That complex formula will only give you an "as the crow flies" mileage. You'll be better off investigating how to use that code you've gotten hold of after all.

 

[Dosnox]

This one might work a bit better for you

http://www.pcreview.co.uk/forums/find-distance-between-two-postcodes-t3913347.html

Also if you want the miles I would change

Set DistanceRow = DistanceTable.Rows(2)
distance = Val(Trim(DistanceRow.Cells(2).innertext))

to

Set DistanceRow = DistanceTable.Rows(2)
distance = Val(Trim(DistanceRow.Cells(5).innertext))

bear in mind it is approximate

 

[MattCallanan]

Thanks, can't see the link unfortunatly, out security set up won't allow access to the site!

 

[Dosnox]

Ok Matt no problem, find below the modified code from the link

Sub post1()
counter = 2

beginrange = Worksheets("sheet1").Cells(counter, 1).Address
endrange = Worksheets("sheet1").Cells(counter, 2).Address


For Each c In Worksheets("Sheet1").Range(beginrange, endrange).Cells
If c.Offset(0, 1).Value = "" Then counter = counter + 2
If counter = 20 Then Exit Sub
beginrange = Worksheets("sheet1").Cells(counter, 4).Address
endrange = Worksheets("sheet1").Cells(counter, 14).Address



Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://www.postcode.org.uk/country/uk/_postcode-distance-calculator.asp"
IE.Navigate2 URL
Do While IE.readyState<> 4
DoEvents
Loop
Do While IE.busy = True
DoEvents
Loop



Set Form = IE.document.getElementsByTagname("Form")
Set inputform = Form.Item(0)

Set Postcodebox = inputform.Item(0)
Postcodebox.Value = c.Value

Set Postcodebox2 = inputform.Item(1)
Postcodebox2.Value = c.Offset(0, 1).Value

Set POSTCODEbutton = inputform.Item(2)
POSTCODEbutton.Click

Do While IE.busy = True
Loop

Set Table = IE.document.getElementsByTagname("Table")
Set DistanceTable = Table.Item(3)

Set DistanceRow = DistanceTable.Rows(2)
distance = Val(Trim(DistanceRow.Cells(5).innertext))

c.Offset(0, 2).Value = distance




IE.Quit
Next

End Sub

where my excel sheet looks like

[RANGE=cls:xl2bb-100][XR][XH=cs:4]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][/XR][XR][XH]1[/XH][XD=h:l]Start Post code[/XD][XD=h:l]End Post Code[/XD][XD=h:l]Distance[/XD][/XR][XR][XH]2[/XH][XD=h:l|c:273d49]CH6 5LT[/XD][XD=h:l|c:273d49]CM2 5PX[/XD][XD][/XD][/XR][XR][XH=cs:4][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][B]Excel 2010[/B]

This will put the post code in C2

Like i said before remember that it is an approximate

 

[MattCallanan]

Thanks, that works great.

Is it however easy enough to link to a form from a different website - for example RAC route planner, Green Flag rout planner or AA route planner?

 

[Dosnox]

Hi Matt

I believe it is possible you would just need to know where to input the post code's on that form and where to pull the relevant mileage from.

Unfortunately I wouldn't be too sure on how to do that as my knowledge of website forms and coding is very limited.

Maybe someone else who is adept with websites maybe able to help.

Sorry I couldn't be more help.