Issue: Excel Cell Formats Change After Copying Data From LIMSData Worksheet

Summary

The information in this article applies to LIMS 2.x and Excel 97.

When exporting data from an LIMS report to an Excel template, cell formats unexpectedly change when the final report worksheet is displayed.

More Information

When creating a template it’s always nice to work with real data from the LIMS. So, you export some data to the template, format the template with the appearance you want, delete the test data from the LIMSData worksheet, then save your work as an xlt file. Here’s where the problem begins. If any of the LIMSData worksheet’s cells had LIMS dates, the cells maintain a date format even after you deleted the cell’s contents. The problem gets more confusing if you are working with UDR data and you add or remove a UDR column before the next export. Suddenly columns that are not dates are formatted as dates. It gets even worse when the number of rows exported from the LIMS varies, which is likely the case.

A quick solution is to always make sure the LIMSData worksheet is completely empty before saving the template. You can easily do this by clicking the box at the intersection of the row and column labels in the LIMSData worksheet, then use Edit | Clear | All. A better solution is to use the BeforeTransferToLIMS macro to do this for you by adding the following lines:

Sheets("LIMSData").Select
Cells.Select
Selection.Clear

You should also verify the method you are using in your AfterTransferFromLIMS macro to copy data from the LIMSData worksheet to your final report worksheet. Statements of the form:

Worksheets("LIMSData").Range("CollectedDate").Copy _
     Destination:= Worksheets("MyReport").Range("B5")

will copy the cell’s value and any formatting (note that an underscore is a line continuation character). When you have already defined the appropriate cell format on your final report worksheet, you must copy only LIMSData worksheet values to preserve your formatting:

Worksheets("LIMSData").Range("CollectedDate").Copy
Worksheets(

MyReport").Range("B5").PasteSpecial _
     Paste:=xlValues