Home | Using Katmandoo | Quick Start Guide | How-to | Table of Contents | Glossary | See Also | Collapse All

Utility in Excel

Overview

The utilities developed in Excel (using Visual Basic for Application) are designed to supplement Katmandoo.

Prepare Utility for Import

The utilities are in the "PrepareUtilityForImport_v2.1.0806.04.xls" file which is in the "Katmandoo2\Template\" folder. If you do not have the file or would like to obtain the latest update, please contact Katmandoo Team.
    1. Update Location Postcode
    2. Update Trial Number

Update Location Postcode

This utility is included in the "PrepareUtilityForImport_vN.xls" file (N refer to the version number of the file). User also need "LocationPostcodeMasterList_20070827.xls" (20070827 is the date when the file was downloaded from www.austpost.com.au) to run this utility.

It finds Postcode using Location Name and State of the Location and then update in the target worksheet.
Illustrative snapshot:
Update Location Postcode (Click it to change its size)
  1. Update correct folder name and file name for Location Master List file (provided by Katmandoo Team).
  2. Update folder name and file name for the target file that has the worksheet to be updated.
  3. Update Name of the Target Worksheet  which will be used to update the postcode retrieved from the Location Master List file.
  4. Update the column names to match with the column names used in the Target worksheet.
    • Note: The target worksheet of the file must have Location and State columns with their respective data. If it does not have Postcode column, then add it in the worksheet.
  5. Click on the [ Update Postcode... ] to update postcode in the target worksheet.

Update Trial Number

This utility is included in the "PrepareUtilityForImport_vN.xls" file (N refer to the version number of the file). 

Some trial data file may not have trial number rather it may use other columns to uniquely identify each trial. But Katmandoo uses Trial Number as part of the Alternate Key (along with Site, Site Year and Trial Type) to identified a trial.

This utility generates Trial Number using the 4 Alternate Key columns and then update the Trial Number in the target worksheet.
Illustrative snapshot:
Update Trial Number (Click it to change its size)
  1. Update folder name and file name for the target file that has the worksheet to be updated.
  2. Update Name of the Target Worksheet  which will be used to update the auto-generated Trial Number.
  3. Update the column names to match with the column names used in the Target worksheet.
    • Note: The target worksheet of the file must have Site Year, Trial Type and Site columns with their respective data. If it does not have Trial Number column, then add it in the worksheet.
  4. The trial number column name (in the Target worksheet)  which will be updated with the generated (using the Column1, Column2, Column3 and Column4) Trial Number.
  5. Click on the [ Generate Trial Number and Update... ] to generate the trial numbers and immediately update in the target worksheet.
  6. OR click on [ Generate Trial Number... ] in the newly added Trial Worksheet of tha target file, make changes in trial number (if required). Once it is ready, click on [ Update Trial Number ] to update the trial number in the Target worksheet. So, this option allows to modify trial number after it is generated.

See Also