Chromatography Forum

Posted: **Fri Jul 24, 2015 7:42 pm**

I'm having some trouble deciphering the log for ChemStation macros.

I 'wrote' a rudimentary 'customtool' that I pieced together, and worked nicely, but now I need to go through and recalculate (based on a touched-up method) every sample (>1000 samples) for export again.

Code: Select all

	found = 1 !found = 0 when Next.mac doesn't have a next file

	While found = 1
		TB2INTEGRATE 	        !Integrate the current
		AO_XLSReps `XLSQres.mac'!Turn the data into a spreadsheet, quantitatively
		Next.mac		!Go to the next file in the folder, also NextFile.mac could be used
	endWhile

I've pieced what I needed, looking at the log for Macro Recording, but don't see as clear a 'timeline' for when I go to the Quantitate>Calculate menu option. I'm assuming that I need to pass something to "quantbeg.mac" (or set a variable?).

As soon as I get it into the .D folder structure, I have an Excel macro that collects all the MSReps into one file, and another macro that parses out all of the data into a nice table format that I can import into any statistical software.

I just need some help getting on track to try to see how much code needs to be put into the above loop.

Thanks much!

Posted: **Mon Jul 27, 2015 6:19 am**

Hello,
Am I correct in presuming that you don’t want to open >1000 data files, and apply your new quantitation method parameters to files you have already quantitated?

I will presume you have already created a macro that will output the quantitated results in a format you need to import into another piece of software.
And you now just desire to apply your new quantitation method parameters and run your macro to >1000 datafiles.

There are two ways this can be performed:
1) Tool -> DoList
And choose Summary Quant w/Calculation ( this will apply the new quantitation method parameters to files)
And choose Macro and enter in the Command string the MACRO [“customtool.mac ”][,GO]
( this will run your macro on the newly quantitated file)

Then click OKAY, and select all the files ( start of with a couple first to make sure it is working)
I suggest you set your printer to PAUSE, otherwise it will print out all the newly quantitated files, and after you’re finished, you can delete all the pending jobs.

2) Quantitate -> Select Template/DatabaseTool and make a database, then choose Update Database and select all the files you need to output the data from, please note the *.crt file created, and be opened directly in excel, but don’t save the file as *.crt, as chemstation doesn’t recognize the excel saved format, so just treat the *.crt file as read only.
This way you don’t need to run a custom macro nor analysis any logs.

Kind regards
Alex

Posted: **Mon Jul 27, 2015 7:56 pm**

Thanks much Alex! I would not have figured that out myself, I don't think. I'll try messing with the Database Export sometime this week hopefully. It sounds like it could be a time-saver. And yes, it is just to apply new quant method to the datafiles.

I did figure out some nuances (with help from the first suggestion of Alex's from the Tool>DoList, using the QT command), and updated the macro as such:

Code: Select all

Name exporter
!REQUIRES:
! XLSQResNoInteract.mac to be placed in the Addon folder (in MSExe). This is a modified version of XLSQRes.mac.

 !need to do while found = 1, when found = 0, there is no next file
	found = 1 !found = 0 when Next.mac doesn't have a next file

	While found = 1
		QT 1,0,`X'		!Calculate the report
						! 1  = do calculations (create new results file) and generate report
						! 0  = screen (default)
						!"X" = Excel; report to the file <methodname>.xls which is in the format used by Custom Reports.
		TB2INTEGRATE 	        !Integrate the current
			!AO_XLSReps `XLSQres.mac'!Turn the data into a spreadsheet, quantitatively
			! The original XLSQres.mac shows to screen, the excel file, so I commented out the check if the session was interactive

		AO_XLSReps `XLSQresNoInteract.mac'!Turn the data into a spreadsheet, quantitatively


		Next.mac		!Go to the next file in the folder, also NextFile.mac could be used
	endWhile
EndMacro

I don't really know all of the functions available in ChemStation (simply I needed to run samples on GCMS and didn't want to generate everything manually), but I pieced the above portion together. I'm also not sure if any of called files I used were built for us (my team/ supervisor), as I'm not a full-time user for the instrument, but it's doing what I want it to do, now.

If anyone uses the above code, you'll need to make a copy of "XLSQres.mac", and rename the copy of it to "XLSQresNoInteract.mac", then change the code below:

Code: Select all

  if check("variable","xlsinteractive")=0	! don't quit if it is interactive usage.
    DdeExecute AO_hChan2, "[Quit]"
  endif

to

Code: Select all

 !if check("variable","xlsinteractive")=0	! don't quit if it is interactive usage.
    DdeExecute AO_hChan2, "[Quit]"
 !endif

, which is just commenting out the check to see if it's interactive, and always closing the Excel file once files are exported.

The reasoning for commenting out the check was so that it always exits excel once excel file is generated. This is because running the macro that I wrote would always show the window, and quanting lots of files, you get lots of windows open, and lots of instances of Excel.

Also if anyone is confused with some of the language, the XLM Macro language is being used for (at least some portion of) the ChemStation macros.

If anyone wants me to attach the excel macros (or just code) that I wrote for once the data was all quanted, let me know.

Thanks!

Chromatography Forum

Help with ChemStation Macro

Help with ChemStation Macro

Re: Help with ChemStation Macro

Re: Help with ChemStation Macro