Back in 20/Jun/2008, when I was Member No. 19,844, I posted this tip.



Like many others I want MM to generate running/cumulative totals in the route cards I generate for the routes I'm planning. Having seen the many requests, but Support providing no solution, or even saying it couldn't be done, I thought I'd try to find one. And I have.

I've done this in MM 2004 (v4), but I'm sure it'll work in MM 2005 (v5) just as well.

Requirements:
A spreadsheet program that can read a .txt file and sort data based on a delimiter into columns. I used Excel 2000 and 2003, but I'm sure any version will work, as will the free OpenOffice suite of programs.

All you need to do is add a simple .txt file to the MM Template directory - normally C:\Program Files\Memory-Map\OS2004\Route_Card_Templates - and then to select it from the [Settings...] button on any route's Property page.

I called the Template - RouteCard_spreadsheet_with_calculations.txt - as it was based on the similarly, but shorter, named .csv template.

The template consists of 4 lines, which I'll detail below. As they are very long I'll do each separately.

Line 1:
Leg;Name;Position;Elevation;Bearing;Leg Distance;Ascent;Descent;Leg Time;(temp calc time);Cumulative Time;(temp calc dist);Cumulative Distance;Comments;Link

Line 2:
%^%#;"%n";"%p";"%e";"%b";"%d";"%a";"%s";"%t";=SUM(I2:INDIRECT("I"&(2+%#)));=DAY(INDIRECT("J"&(2+%#)))&" day "&HOUR(INDIRECT("J"&(2+%#)))&":"&MINUTE(INDIRECT("J"&(2+%#)));=IF(ISBLANK(INDIRECT("F"&(2+%#))),0,IF(IsErr(SEARCH(" km",INDIRECT("F"&(2+%#)))),SUBSTITUTE(INDIRECT("F"&(2+%#))," m","")/1000,VALUE(SUBSTITUTE(INDIRECT("F"&(2+%#))," km",""))));=SUM(L2:INDIRECT("L"&(2+%#)));"%c";"%l"

Line 3:
%$

Line 4:
"Totals";;;;;"%D";"%A";"%S";"%T";;"%T";;"%D";;

Quick explanation: All lowercase %x values represent the value for a single waypoint, such as %n = name, %e = elevation/height, %d = distance, %t = time, %c = a comment added to a waypoint, etc. All uppercase %X values represent total values for a route, or in V5, global values, such as %N = name of the route, %D = total distance.

How to use this new template:
1) Make sure you are using Metric distances as that's what the calculations expect.
2) Select the route required, go to it's Properties, click [Settings...] button and select the new template.
3) From the route's Properties click [Route Card] button. This will, in v4 but not v5, ask you where to store the results, then will start Notepad (or your text editing program associated with .txt files) and display the generated route card file. Close this as it's unreadable.
4) Start your spreadsheet program, and close any files it opens automatically.
5) Import the generated route card file into the spreadsheet. In Excel you do this using the Open option on the File menu, then selecting the file type to Text Files. During the import it will ask if the file contains Delimited or Fixed-width data; select Delimited. When asked what the delimiter is, choose just the semi-colon. (Note, don't put any semi-colons in any comments you've added to waypoints.)
6) You will now have a spreadsheet with cumulative times and distances.
7) Before using/printing this I'd suggest hiding columns J and L as they are just working calculations. I'd also make the first and last rows BOLD, and do a few other tweaks to the formatting. You could even save the formatted version as a true spreadsheet file (i.e. .xls).

I will upload the template and a couple of examples to my website, http://www.virbius.co.uk/memory-map , but the FTP is failing at the moment.
(later: The FTP worked so check out my website.)

When I have time I'll try to do something similar for the .htm (web based) template, probably using JavaScript or similar for the calculations, unless anyone beats me to it.

Over to you, and good luck.