Create Custom Container Labels with Excel
LIMS includes four container label styles to print sample container labels at sample login. All four label styles are sized to fit 30252 (1” x 3.5”) address labels on inexpensive Dymo LabelWriter printers. Two of the four label styles include a sample ID barcode.
Users with an LIMS Full System license can design and add custom label styles for alternate label sizes and content with a simple customization. The process is described in the LIMS Programmer’s Guide and requires only basic experience with the Microsoft Access report generator.
With an LIMS Annual Subscription license, label changes are limited to altering the contents of fields in any of the four label styles. But what if you want to change label fonts, sizes, or field widths, use smaller or larger labels, or list a sample’s analyses on a label, for example? For those using an annual subscription license, an Excel template is a viable solution. Excel template labels cannot automatically print during sample or batch login like integrated label styles. However, you can generate and print custom labels after sample login by simply exporting a LIMS report.
You can create your own labels for any size label stock and label printer. First install the Windows printer driver for your selected printer on the LIMS workstation. In the example below we will make our own version of a larger label for Dymo’s 30256 (2.3” x 4”) shipping label.
Begin by downloading the Excel Container Label Examples from the File Library. Extract the example templates from the zip file and save them in your Excel export templates folder, which you will find listed on the Folders tab of the Workstation Configuration screen on the Admin menu. Copy the example “Container Labels – Dymo 30256 Shipping.xlt” template to a new file, then rename the file “My Container Label.xlt.”
While Excel can save a worksheet’s paper size with a workbook or template, this only works well with the paper sizes for your default printer. Since your Windows default printer is unlikely to be your label printer, it is best to configure the label size with the label printer’s properties. Right-click the label printer in Windows’ list of printers, select Printing Preferences, then set the paper size to your chosen label stock. The layout of the printing preferences screen is specific to the chosen printer.
The screen below shows the paper size set using the [Advanced…] button on the Dymo LabelWriter 450 Printing Preferences screen. Use the [OK] button to save your selections.
Right-click your new My Container Label.xlt template and select Open to access the template itself (remember, double-clicking a template in Windows Explorer does not open the template file; rather it creates a new workbook from the template). Alternatively, open the template file within Excel. See the template’s Read Me sheet for more information on the template’s operation.
Let’s review how this template is constructed. The first two rows on the Labels worksheet are designated title rows using Page Setup and will repeat on each label. Using repeating title rows is a good way to include an image, such as our example’s logo, on the top of each label. Select the OneLabel name from the Name box to view the cells that comprise the variable portion of each label. The template’s AfterTransferFromLIMS macro automatically copies the OneLabel named range to cells below, once for each sample exported from the LIMS, inserting a horizontal page break before each copy. The image below shows the selected OneLabel named range.
The dimensions of a Dymo 30256 shipping label are 2.3” by 4” so this template has sufficient room to display a sample’s analyses. Our example uses five rows with four analytes per row. The formulas to display analyte names look up the Nth analyte for the label’s sample ID on the AnalysisData sheet. If the sample does not have an Nth analyte, an empty string is listed.
The remaining formulas, which show #NAME? errors, retrieve a specific LIMS field value for the sample from the LimsData worksheet. The formulas use named ranges that will not exist until they are created by the AfterTransferFromLIMS macro using the LIMS field names. That is, the formulas will not result in errors after we export a LIMS report to the template. Review the formulas showing #NAME? errors and you will see they all have the following format:
=INDEX(fieldname, (ROW() – ROW(OneLabel)) / ROWS(OneLabel) + 1)
Where fieldname is the LIMS field name. The formula above retrieves the Nth value in the specified named range for the current label.
The template currently includes formulas to display the sample ID, project, sample type, customer, location, collected date, and collected time. Let’s modify the label and replace the sample type and location with the customer’s sample ID and a two-line sample description. Select cell A6 and replace “SampleType.Name” in its formula with “CustomerSampleID”. Select cell A7 and replace “Location.Name” in its formula with “Description” then unmerge the cell, select cells A7:J8 and merge to create a two row cell. Right-click the new merged cell, select Format Cells, set horizontal alignment to Left, vertical alignment to Top, and then enable text wrapping.
Close the template saving your changes.
If you reviewed the template’s Read Me sheet, you will recall this template is designed to be used with the Work Order report on the Samples menu. To try your new template, preview a multi-sample Work Order report, use File | Export to MS Excel Template or the equivalent toolbar button and select the new My Container Label template. The resulting Excel workbook created from your template will show one “page” or label per sample exported as shown below. This is a good time to view the available data and field names on the LimsData sheet and experiment with formula and layout changes on the Labels sheet using real LIMS data. When you are satisfied with any changes you can duplicate the modifications in your template.
When copying a named range in Excel, the destination cells do not inherit the row heights of the source cells. A more elaborate AfterTransferFromLIMS macro could add this capability but, for simplicity, our example does not. For this reason, the height of all rows in the OneLabel named range and all remaining rows in the worksheet are the same. If you alter the row height or insert new rows in the OneLabel range, you should verify your new page will fit on a single label. Simply select your label printer and the default paper size you previously set is automatically selected. Then use Excel’s print preview feature and you can easily verify whether each page fits on a single label using the print orientation and margin settings configured in your template’s page setup. When you close print preview you will see page breaks in the Labels sheet for your selected printer and paper size. Below is a preview of a label from our new template.
Finally, so that you do not have to select the printer each time you print labels with your new template, add the printer name to the Printer option on the template’s Settings worksheet as shown below. When you export a Work Order report, the template’s AfterTransferFromLIMS macro will enable the [Print Labels] button on the Labels sheet if a printer is included on the Settings sheet. Simply click the [Print Labels] button to print the labels to your designated label printer.
Now you can create any number of custom container label templates for any label printer and label size, for daily use or special projects. Full System licensees may also find the convenience of Excel-based labels a handy addition to their collection of Excel templates.