Clarity solution – MS Excel macro for Result Table check

Importance of the Topic

Automating the transfer and evaluation of chromatographic result tables enhances data integrity and efficiency in routine and regulated laboratories. The ability to directly import analysis results into Excel, apply calculations, and compare against predefined limits reduces manual errors and accelerates decision-making in quality control and research environments.

Objectives and Study Overview

This application note presents an Excel macro solution designed to streamline the processing of chromatogram result tables exported from Clarity chromatography software. The main goals include:

• Automate loading of exported chromatogram data into Excel
• Format result tables and insert custom calculations (e.g., percentage of amount, averages)
• Compare results against user-defined limits and flag pass/fail status

Methodology and Used Instrumentation

The workflow consists of several steps integrating Clarity software, batch scripts, VBScript, and MS Excel macros:

1. Enable Excel macros by adjusting the security settings to allow macro execution.
2. Unzip the provided archive to a local folder (e.g., C:\xls-export) containing the Excel macro and related scripts.
3. Configure Clarity’s Export Data settings to generate a tab-delimited text file and call a batch script post-analysis.
4. Use the OpenChromInExcel.bat batch file and OpenChrom.vbs VBScript to launch MS Excel and pass the exported file name to the macros.
5. Execute two VBA macros: one to import and format the chromatogram data, and another to insert limit checks based on a "Limits" worksheet.

Used Instrumentation

• Clarity Chromatography Software (version 8.1.0.77)
• Microsoft Excel (tested on 2013)
• VBScript and Windows batch scripting for automation

Main Results and Discussion

The implemented macro successfully imports single-signal chromatogram data into Excel, applies custom calculations such as percentage content and statistical averages, and compares each analyte against preset acceptance criteria. The process highlights passed and failed compounds directly within the result table. Limitations observed include:

• Compatibility only with single-signal chromatograms; multi-signal data may yield inaccurate average calculations.
• Manual saving required to preserve each processed Excel file under a unique name in automated sequences.

Benefits and Practical Applications

• Reduces manual data handling and transcription errors in chromatography workflows.
• Enhances compliance by providing built-in limit checks and clear pass/fail indicators.
• Customizable functions allow laboratories to adapt the macro to specific analytical methods and reporting requirements.

Future Trends and Potential Applications

Future development could focus on:

• Extending macro support to multi-signal and photodiode array chromatograms.
• Implementing automatic file naming and saving to fully integrate into batch sequences.
• Linking with laboratory information management systems (LIMS) or cloud platforms for centralized data tracking.
• Incorporating advanced data analytics and visualization directly within Excel.

Conclusion

The Excel macro solution provides a practical example of automating data exchange between Clarity and MS Excel, significantly streamlining result table formatting and limit checks. While currently limited to single-signal analyses, the framework can be expanded and customized to support more complex chromatographic data and fully automated laboratory workflows.