Clarity solution – MS Excel macro for Result Table check

Significance of the Topic

This application note addresses the need for automated quality control of chromatographic results by integrating Clarity software exports with Microsoft Excel. Manual inspection of result tables can be time-consuming and error-prone. Automating data import, formatting, and limit checks enhances productivity, consistency, and compliance with analytical workflows.

Objectives and Overview of the Application

• Enable automatic loading of Clarity-exported chromatogram data into Excel
• Format the result table and insert custom calculations such as percent amount and averages
• Compare results with predefined acceptance limits and flag pass/fail outcomes
• Provide a template macro that can be customized for different types of chromatographic data

Methodology and Instrumentation

The workflow combines a batch script, Visual Basic script, and an Excel macro to handle data export and processing:

1. Configure Clarity to export chromatograms to a text file using post-run settings or sequence table parameters.
2. Run OpenChromInExcel.bat, which invokes OpenChrom.vbs to open the Excel template (chromatogram-export.xls) and pass the filename parameter.
3. Excel VBA macros import the text data into a worksheet, apply formatting, calculate percent amounts and averages, and insert a limits table.
4. Results are evaluated against user-defined limits in a separate worksheet, with each compound flagged as Passed or Failed.

Main Results and Discussion

The macro successfully automates the end-to-end processing of single-signal chromatograms:

• Imported result tables retain original data structure while adding calculated columns and summary statistics.
• Limit checks provide instant visual feedback on compliance.
• The template remains open for manual review or additional automation such as saving files under unique names.
• Current limitations include support for only single-signal data and the need for manual saving to preserve uniquely named reports.

Benefits and Practical Applications

• Streamlines routine post-run processing in laboratories focused on QA/QC.
• Reduces risk of manual transcription errors and ensures uniform formatting.
• Offers a foundation for extending automation to multi-signal chromatograms or other detector data with additional customization.
• Enhances traceability by linking results directly to predefined acceptance criteria.

Future Trends and Potential Applications

Advances in laboratory informatics and scripting interfaces may enable:

• Full integration of Excel-based checks into laboratory information management systems (LIMS).
• Automated report generation in PDF or web formats with embedded pass/fail summaries.
• Support for multi-signal and PDA chromatograms through dynamic macro adjustments.
• Cloud-based processing pipelines where exported data are routed to centralized servers for batch validation.

Conclusion

This macro demonstrates an effective approach to merging chromatographic export from Clarity with Excel-based QC checks. By automating import, calculation, and limit evaluation, laboratories can improve efficiency and data integrity. The template can be adapted to diverse analytical methods and integrated into broader automation frameworks.

Used Instrumentation

• Clarity Chromatography Software version 8.1.0.77
• Microsoft Excel 2013 (macros enabled)

Reference

• DataApex application note Code/Version D144/EN04-20240221