Chromatography Forum

Not sure if this is where I post this.. if not, please move to the appropriate section...

I need help creating an excel file with my GC-MSD data. The file needs to be formatted this way:

and sorted by "Sample ID".

I'm using MSD Chemstation E.01. Not sure if this is something a macro can do.. but any help would be greatly appriciated..

In Chemstation generate an .xls report for, say, Sample-01 and Sample-02

In Excel open the .xls report for Sample-01, create a new excel document and save it as 'GC-MSD Template', enable windowed viewing.

Select the .xls report. Find the run date/time field. Select that field and hit ctrl-c to copy it.

Select the template doc. Left-click on cell A1 (or anywhere, I guess) to select it. Right-click and select 'paste special'. Select 'paste link'. Repeat for all of the fields that you need (it may be simpler for repeating fields, like date/time, to, for example, go to A2 and enter '=A1'). Save 'GC-MSD Template' and copy it for a backup. Save the file (again) as 'Sample-01'.

Now open the 'GC-MSD Template' file (this will be your working version, don't mess with your backup) in Excel. Go to the 'Edit' menu and select 'Links'. Click 'Change Source' and browse to where the Sample-02 detail.xls file is and select it. If all goes well you will propagate the template file with all of the data that you need. This assumes that Sample-01 and Sample-02 were done with the same method and compound list as altering those will alter the location of the data that you're linking to in the template. Save the file as 'Sample-02' and just keep using the template to resort your detail.xls files.

When you want to get fancy pick up a copy of Access and write some code to pull all of the sample data for a job and compile it into a large spreadsheet and create a custom report (and EDD?). When you want to get really fancy pick up a copy of SQL server and push tables to a shared back end where you can have multiple users adding data and creating reports.