Skip to main content
Have a Question?
Table of Contents
< All Topics
Print

Importing Analyte Results with Excel Templates

Initial Setup

When importing results from an Excel Template (either from Batch Login or Results by Analyte), each Analyte in the template must have a corresponding record in the Analysis table (Main Menu: Setup > Analyses).

Configuring the Analysis Table

The Analysis table defines the Excel Template to LIMS interface. Under the Excel Interface tab, configure the following:

  • Template name and location
  • Worksheet name where results are found
  • Starting cell of the result data
  • Direction (by row or column)
  • Number of cells between each analysis
  • Sample ID cell location (where the Sample ID will always start)

Important Limitations:

  • Each Analyte record can have only ONE Excel Template import defined
  • The column containing each Analyte must always remain in the same position

Template Setup

File Requirements

The Excel template must be an Excel Macro-Enabled Template (.xltm) file.

Worksheet Structure

At minimum, the template must contain:

  • One worksheet named LIMSData (required)
  • Additional worksheets as needed for test data, imports, and reports

VBA Macros

Overview

For LIMS integration to work properly, the Excel Template must contain Visual Basic for Applications (VBA) macros with four required procedures.

Accessing the VBA Editor

Use either method:

  • Press Alt + F11, or
  • Go to Developer tab → Visual Basic

Creating the Required Macros

  1. In the VBA Editor, locate the Project Explorer window (left side)
  2. Double-click on LIMSData under Microsoft Excel Objects
  1. The code window will open on the right side (it should be blank for a new template)

Required VBA Code

Copy and paste the following code into the LIMSData code window:

vba

Public Sub BeforeTransferFromLIMS()

    ‘ Insert code to run before J Street LIMS exports data

    ‘ to the LIMSData worksheet

End Sub

Public Sub AfterTransferFromLIMS()

    ‘ Insert code to run after J Street LIMS exports data

    ‘ to the LIMSData worksheet

    ‘ Create named ranges

    Application.ScreenUpdating = False

    With Worksheets(“LIMSData”)

        .Select

        .UsedRange.Select

        Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False

    End With

    ‘ Display the first worksheet

    Worksheets(1).Select

    Application.ScreenUpdating = True

End Sub

Public Sub BeforeTransferToLIMS()

    ‘ Insert code to run before J Street LIMS imports data

    ‘ from the workbook

End Sub

Public Sub AfterTransferToLIMS()

    ‘ Insert code to run after J Street LIMS imports data

    ‘ from the workbook

End Sub

Save the Template

  1. Click the Save icon or go to File > Save
  2. Important: Excel may change your save location and/or file extension
  3. Verify the save location and ensure the extension is .xltm before clicking OK

Implementation Workflow

Step 1: Prepare Samples in LIMS

  1. Create a group of Sample IDs for samples that will be tested
  2. Perform testing and obtain results (typically in a worksheet with Sample ID and Result columns)

Step 2: Query for Samples

  1. Run a query for all Sample IDs that are expected to have results (Analysis Entry)
  • Click OK to open the Results Entry by Analyte screen
  • Step 3: Open the Template
  1. Verify that the correct Sample IDs are displayed
  2. Click the Spreadsheet button (top left) if the Spreadsheet options are not showing
  • Click New to open a new copy of your Template
  • Verify Sample Population

Your template will open with the selected Sample IDs automatically populated in column A, starting at cell A7 in this example.

Important: If you see #REF! errors in empty rows, this means you selected fewer samples than the template has pre-configured rows. To avoid this error, ensure the number of samples selected is equal to or greater than the number of rows set up in the template.

Step 5: Enter Results

Enter all test results for the samples in the appropriate columns of the template.

Step 6: Import Results into LIMS

  1. Return to the LIMS Results Entry by Analyte screen
  2. Click the Import Results By Analyte button
  1. The Excel Data Import dialog will display with populated fields
  2. Double-check that all samples and results are correct
  3. Click Import to import all the data

Note: You may see the message: “An analytical batch does not exist. Any QC data in the spreadsheet was skipped.”
This message can be ignored when importing routine samples (non-QC batches).

  1. Click Yes when prompted to save the imported results

Step 7: Complete or Continue

For single-analyte templates:

  • Click Close to exit the Results Entry by Analyte screen
  • Click Cancel to close the Results Entry by Analyte Setup screen

For multi-analyte templates:

  • Click Next Analyte button
  • Click Spreadsheet button
  • Click Import Results button
  • Click Import to import the next analyte into LIMS
  • Repeat for each analyte in the template until all have been imported

Best Practices

  • Always verify Sample IDs before importing
  • Match the number of samples to the template’s configured rows to avoid errors
  • Double-check results before clicking Import
  • Save template changes regularly during development

Troubleshooting

#REF! errors in template:

  • Select more samples or reduce the number of pre-configured rows in your template

Template doesn’t open when clicking New:

  • Verify the template path is correctly configured in the Analysis table
  • Ensure the .xltm file exists at the specified location

Import fails or data doesn’t appear:

  • Verify the Excel Interface settings in the Analysis table match your template structure
  • Check that named ranges were created properly (AfterTransferFromLIMS macro)
  • Ensure Sample IDs in the template match those in LIMS
Table of Contents