Settle Harriers Races Results Process
Results Excel VBA Setup
The workbook should under Excel 2010 and coming forward.
First thing to check is that the VBA in the si-entries workbook runs on your device.
Open Excel and check if the top level menu has a Developer option.
If there is no developer option, then select file and options.
Select Customise Ribbon and find Developer command and add it to the Main Tabs below Automate
Open the si-entries workbook, click on Developer on the main menu bar and then the Visual Basic button to open the VBA window
In the window that opens, find the project with the same name as the workbook and select it.
Click on tools on the main menu bar and select references.
Check the boxes shown in the image below.
Now return to the workbook window and find the control panel table and then click on the build Registration sheet. It will either complete or fall over. If it fails, drop me an email.
Pre-Entry Processing
The description below describes how the entries are processed to produce the registration sheets and labels. The results sheet is another excel workbook.
The first one is the participant list. This is downloaded from Si-entries, once the entries are closed, it can be downloaded as a XLSX or a CSV file.
The columns in the spreadsheet there are many but for the race we are interested in
Participant – Participant No
Participant – First Name
Participant – Last Name
Participant – Class
Entry Details – Club
The si-entries workbook processes the participant list
So the first tab Si-entries Export has to be populated with the participant list, this has to be a copy and pasted of the cells from one sheet to another because we need to preserve the formulas in the Si-entries Export Mapped sheet.
Open the participant list in a separate excel session and
If the sheet SiEntries Export is populated, select all the cells and clear the contents, do not use delete otherwise the formula linkages are broken.
Open the participant list in a separate excel session and copy the contents of the sheet and paste into SiEntries Export
The SiEntries Export Mapped sheet is to map the columns into the correct format. This is really used for validation if si-entries change the output of the participants list.
If you scroll across the first row there are some headings in red, these contain the output from the mapping.
So, the first one Participant – Race Class, maps the Participant – Class into a race category. If you look at the formula in the cell it is quite convoluted, but it does give us a correct race category.
The next one Entry Details – Club Cleaned, deals with unattached runners in Entry Details – Club
The next Tab SiEntries Export Cleaned has the data as SiEntries Export Mapped, but cleans up the participant data. This is the sheet where the entry list is built from.
Dev Column numbers is only used when I was developing the code to find the correct column number.
Control Panel is the place that you run a process by clicking on the button.
Build registration sheets does what it says, the output will be 4 sheets:
Entry Contact Details
Entries Sorted by Surname
Entries by Order of Entry
Entries sorted by Race No
The buttons below are to output the result.
The Build labels sheets will produce a sheet with the labels.
The buttons below are to output the result.
The labels are quite precise and may need for fine-tuning depending on your printer. The way I do it is to print on plain paper and compare that with the label sheet.
