Create Excel Templates by Copying<br>Other Templates

Unless you are proficient with Visual Basic for Applications (VBA) macros in Excel you should only create new Excel templates used with LIMS by copying an existing template file. All Excel templates used with LIMS must be in Excel 97-2003 format with an XLT file extension. Currently, LIMS does not support Excel’s newer XLTM macro-enabled template format.

It is problematic to export a LIMS report to a template, make a few changes to the resulting workbook, then save the new workbook as a template. While tempting, a template created in this manner will trigger any number of errors and unexpected results the next time it is used. To understand why you should avoid this scenario, consider the tasks performed by the template’s macros.

All Excel templates used with LIMS include an AfterTransferFromLIMS macro, which is invoked after Excel creates a new workbook from the template and the LIMS transfers data to the new workbook. Among other tasks, the AfterTransferFromLIMS macro in most of the example templates supplied with LIMS will create named ranges for the data transferred from the LIMS to the workbook’s LIMSData worksheet, which allows formulas to refer to LIMS data by name rather than by cell address. For example, the formula =INDEX(SampleID,1) will display the sample ID for the first sample exported. A cell with this formula will correctly show a #NAME? error in the template because the SampleID name does not exist until you export to the template and the new workbook’s AfterTransferFromLIMS macro creates the names. Saving a workbook containing all of the named ranges as a template appears innocuous until you export to the template and the new workbook’s macro attempts to created named ranges for names that already exist, which results in a litany of errors or warnings such as the one in the screen below.

The presence of existing LIMS data is another problem with saving a workbook created by exporting to a template as a template itself. For simplicity, our example templates’ macros assume the template is free of existing LIMS data which eliminates the need to perform any housekeeping tasks such as deleting old data or names. For example, if you export ten samples to a template, save the resulting workbook as a template, then export five samples to the new template, the template’s macro will still see ten samples since only the first five samples were overwritten with new data. In this case, the results are certainly not what were expected.

For these reasons at a minimum, you can see why all templates should be devoid of existing LIMS data and the named ranges created from that data. Understand the difference between a template and a workbook created by exporting LIMS data to the template and you will avoid this common error.