How to Remove a Template’s LIMS Infrastructure

Summary

The information in this article applies to Excel templates used with LIMS 2.x.

In addition to the LIMSData worksheet, an Excel template used with LIMS’ Excel interface may include several worksheets used to produce a final report. Analysis data, lookup table, pivot table, and customer data worksheets are examples of worksheets that a template may use to assemble the data for a template’s report. This article includes procedures to automatically remove all such infrastructure from a workbook created from an Excel template.

More Information

All Excel templates used with LIMS’ Excel Interface include a LIMSData worksheet with accompanying macros. A template may also contain other worksheets with additional LIMS data. For example, the template used to create the certificate of analysis (CofA) below includes worksheets with analysis data and the LIMS’ customer record. For simpler template maintenance, the CofA worksheet uses formulas to display sample characteristics, analysis results, and customer information. With this design, the report can be modified without any need to edit the macro’s Visual Basic for Application (VBA) code.

This common template design works well when you only need to print the report. However, when you save a copy of the workbook to distribute the report electronically a few problems are apparent. First, the additional worksheets result in a larger workbook file. Since the workbook includes macros anyone opening the workbook will likely encounter Excel’s macro security warning. If the workbook will be emailed, the additional data worksheets may reveal more data than you would like and some of the data may be sensitive.

The technique described in this article can be used to automatically remove all infrastructure worksheets leaving only the report sheet. The solution uses an Excel feature that replaces a cell’s formula with the value resulting from the formula. For example, if you enter the formula =1+2 in a cell, although the cell displays the value 3, the formula is still the cell’s contents. Now use Edit | Copy to copy the cell then use Edit | Paste Special and choose the Values option and Excel replaces the cell’s formula with the value 3. After the template’s report is generated, we can use this feature to replace all formulas on the report worksheet with their values then we are free to delete all of the infrastructure worksheets.

A simple modification to your template’s macro is all that is necessary to implement the solution. Follow the steps below to add the necessary code to each of your templates to remove the entire LIMS infrastructure used to produce your final report.

Step 1

Download file SaveReport.txt. This file contains a single VBA procedure that will save all formulas as values on the first worksheet and then delete all remaining worksheets. If necessary, reorder your template’s worksheets so the report sheet is positioned first. Note that if you have more than one report worksheet in your template, you will need to modify the SaveReport procedure accordingly.

Step 2

Open the template file in Excel and use Tools | Macro | Visual Basic Editor or Alt+F11 to open the VBA editor.

Step 3

Double-click the LIMSData sheet in the project explorer in the upper left corner of the VBA editor to display the sheet’s macro code. If the project explorer is not visible, use View | Project Explorer to display the window. Click anywhere within the macro code on the right side of the screen and use Ctrl+End to position the cursor at the end of the code.

Step 4

With the cursor positioned at the bottom of the VBA code window, use Insert | File then select file SaveReport.txt downloaded in step 1.

Step 5

Scroll up in the VBA code window until you find the end of the AfterTransferFromLIMS macro illustrated below. Before the “End Sub” line, insert a new line and type the statement “Call SaveReport” as show below.

If your template’s AfterTransferFromLIMS macro includes error trapping, add the “Call SaveReport” statement before the first “Exit Sub” line as illustrated below.

Step 6

Use Debug | Compile VBA Project to check for syntax errors. Any errors will be highlighted with an appropriate error description. Use File | Close and Return to Excel to close the VBA editor. Close the template in Excel saving your changes.

After making these changes, when you export from LIMS to the updated template, you will now see the following dialog:

Click OK to delete all but the first sheet or use Cancel to leave all worksheets intact. During template development and testing, edit the AfterTransferFromLIMS macro and place a single quote in front of the Call SaveReport statement to turn the line into a comment. This can be particularly useful while troubleshooting the report worksheet’s formulas.