Thursday, 26 April 2012

Excel Addin for UK Postodes - Gives Distance, Duration etc

It was a bit hard coming up with a title for this post. I am regularly frustrated, like most business travellers, with having to fill in mileage returns every month or so to justify where you have been and to claim back relevant monies. I spend what seems like most of a day flicking between my diary and Google Maps inputting postcodes to find out how long each journey was. I am sure this is a problem familiar to many business people whether in terms of expense calculation or in putting together invoices and tenders that have some mileage component to the cost. Anyway, I thought to myself there must be a better way!

With the help of Google's map api and some C# code, I have developed an addin for Excel 2003,2007 and 2010 that will return various bits of info if supplied with postcodes. The functions and their uses are:


Function

Description

GetKilometers(from,to)

Gets kilometres between two postcodes

GetMiles(from,to)

Gets miles between two postcodes

GetLatitude(from,to)

Gets Latitude for postcode e.g. NN14 2JD = 52.4472546

GetLongitude(from,to)

Gets Longitude for postcode e.g. NN14 2JD = -0.82291

GetJourneyTime(from,to)

Gets duration in hours and decimal of hours e.g. 1.2 is actually
1hr 12mins N.B.

Please remember: these functions will ONLY work with UK postcodes.

An example: =GetMiles("NN147RJ","ST52SL") will return 108.01 miles.

***The postcodes need to have double quotes around them as above***

The data is fetched from Google's maps api so please remember that if you are using these formulae for a 1000-line spreadsheet - it may take a while! Needless to say you need an internet connection for this to work and you will need to have .Net Framework 4 installed or it won't work! (Link here)

You can download the adding from here: Excel Mileage Addin

INSTALLATION


Now for the slightly tricky bit.

BEFORE YOU COPY THE ADDIN TO THE ADDIN FOLDER EXCEL MUST BE CLOSED

When you have the addin, you need to copy it to your addins folder which is usually:

Win XP 
C:\Documents and Settings\USERXXX\Application Data\Microsoft\AddIns

Win 7
C:\Users\USERXXX\AppData\Roaming\Microsoft\AddIns

The 'USERXXX' will be your username for Windows - if you get stuck scout the internet for info on this.

Once the addin is copied over, you will need to activate it in Excel:

For Excel 2003 you can go to 'Tools' menu and then 'Addins' - 'ExcelMileage' should be listed, just make sure there is tick against it.

For Excel 2010 you need to go to 'File'->'Options'->'Add-Ins', ExcelMileage should be listed under 'Active Application Add-ins' and you should need to do nothing more. If you have probs, go to the 'Manage:' box at the bottom, select 'Excel Addins' and make sure 'ExcelMileage' is enabled.

And that should be it! The formulae will handle postcodes in various formats - e.g. "NN46HG" or "nn4 6hg" but please remember it will only work for UK postcodes.

Post if you need assistance.

2 comments:

  1. Hi AlgoPete

    I've tried the download link but just get the FileDropper home page. I've got a FileDropper account any ideas on the problem? Could you review the link.

    Thanks
    Nick

    ReplyDelete
  2. Hi is it possible to get this file relisted

    ReplyDelete