Description
This module contains functions to facilitate automating Data Explorer,
and some functions to manipulate other applications for generating reports
in them. There are a few logical categories of functions, which are
listed at the beginning of this document, along with the functions that
are part of those categories.
Dependencies
To use this module requires the following references (in addition to
any default references that are already selected)
Variable Formats
There are two variable formats that are used commonly in this module.
One is a single dimension array that holds peak information (the particular
information it holds differs between chromatogram and spectrum peaks, but
the size is the same). The other is a 2 dimensional array that contains
a list of peaks.
Peak
Arrays
A peak array is an array of Variants dimensioned from 0
To 6. FIXME
Peak
Tables
A peak table is an array that FIXME
File and Application Manipulation
These functions are used to open and close applications and documents, and to browse for files. The ask functions are for browsing for files, the load functions for loading applications and documents, and the release functions for closing applications and documents. The load functions will attempt to attach to an already running instance of the application (or an already open copy of the document), rather than spawning a second copy. Therefore, the release functions, where possible, will not close the application if the load call did not open it. Because of a bug in Data Explorer, this behavior is not possible with Data Explorer documents. They will always be closed when the object is released.
Public Sub askDEFile(FileName As String, Title As String, OpenDialog As Boolean, Optional FilterIndex As Integer = 1)
Parameters
Public Function loadDataExplorer(deApp As DataExplorer.Application, Optional bVisible As Boolean = True) As Boolean
Parameters
Dependencies
Data Explorer Type Library
Public Function loadDEDoc(FileName As String, deDoc As DataExplorer.Document, deApp As DataExplorer.Application) As Boolean
Parameters
Dependencies
Data Explorer Type Library
Public Sub releaseDEDoc(deDoc As DataExplorer.Document, DidOpen As Boolean)
Parameters
Notes
If DidOpen is true, then the document is closed. If
DidOpen
is false, the document should be left open. However, due to a bug
in Data Explorer, it will be closed anyway.
Dependencies
Data Explorer Type Library
Public Sub releaseDataExplorer(deApp As DataExplorer.Application, DidOpen As Boolean)
Parameters
Notes
See releaseDEDoc
for a more detailed explanation of the DidOpen parameter
Dependencies
Data Explorer Type Library
Public Sub askXLSFile(FileName As String, Title As String, OpenDialog As Boolean)
Notes
See askDEFile for
a description of the parameters.
The only difference between this function and askDEFile is
in the file filters. This function has the default filter showing
.xls files, and an All Files filter (*.*). Because there is only
one Excel file filter, the FilterIndex parameter is not present
in this function.
Dependencies
modCommonDialog
Public Function loadExcel(xlApp As Excel.Application, Optional bVisible As Boolean = True) As Boolean
Parameters
Return Value
The return value is the same as that for loadDataExplorer
Notes
Excel has some bugs in its ActiveX interface that can make this function
behave in strange ways. If Excel is launched with this function and
left invisible, then, when it is closed with releaseExcel,
it does not properly close. If this call is made after that, it finds
the copy of Excel left over that is already open, but invisible.
Thus, when one might think that a new copy of Excel should be launched
and made visible according to the bVisible parameter, instead
xlApp
will be attached to the already running, invisible copy. Also, if
there is one of these invisible copies of Excel running, and the user launches
Excel (from the Start menu, say), then a second copy of Excel will be opened.
However, this function will attach to the still running invisible copy.
This can cause problems, especially if the ActiveX system is manipulating
a document that the user has opened in the second, visible copy of Excel.
Dependencies
Excel Type Library
Public Function loadXLDoc(FileName As String, xlDoc As Excel.Workbook, xlApp As Excel.Application) As Boolean
Notes
This function behaves in the same way as loadDEDoc
Dependencies
Excel Type Library
Public Sub releaseXLDoc(xlDoc As Excel.Workbook, DidOpen As Boolean)
Notes
This function behaves in the same way as releaseDEDoc.
Excel does not, however, have the bug that causes the document to be closed
no matter what.
Dependencies
Excel Type Library
Public Sub releaseExcel(xlApp As Excel.Application, DidOpen As Boolean)
Notes
This function behaves in the same way as releaseDataExplorer.
See loadExcel for
some notes about a bug in Excel that affects this function.
Dependencies
Excel Type Library
Public Sub askWordFile(FileName As String, Title As String, OpenDialog As Boolean)
Notes
This function behaves the same as askXLSFile,
except that the file filters are Word Documents (.doc) and All Files(*.*)
Dependencies
modCommonDialog
Public Function loadWord(wordApp As Word.Application, Optional bVisible As Boolean = True) As Boolean
Notes
This function behaves the same as loadExcel.
It has not yet been determined if Word suffers from the same bug as
Excel (See loadExcel
for a description).
Dependencies
Word Type Library
Public Function loadWordDoc(FileName As String, wordDoc As Word.Document, wordApp As Word.Application) As Boolean
Notes
This function behaves the same as loadXLDoc.
Dependencies
Word Type Library
Public Sub releaseWordDoc(wordDoc As Word.Document, DidOpen As Boolean)
Notes
This function behaves the same as releaseXLDoc.
Word does not suffer from the document closing bug present in Data
Explorer (see releaseDEDoc
for a description).
Dependencies
Word Type Library
Public Sub releaseWord(wordApp As Word.Application, DidOpen As Boolean)
Notes
This function behaves the same as releaseExcel.
It has not yet been determined if Word suffers from the same bug as
Excel (See loadExcel
for a description).
Dependencies
Word Type Library
Public Sub askFile(FileName As String, Title As String, OpenDialog As Boolean, Filter As String)
Notes
This function behaves the same as the other ask functions. The
only difference is the Filter parameter.
Filter is a string which contains the information for the
filters. Each filter is two strings separated by a vbNullChar.
The first string is the string displayed in the dialog for that filter.
The second is the actual filter. Filter is terminated by two vbNullChar's.
To combine multiple file specifications within a single filter, separate
the specifications with a comma. See Microsoft's Win32 API documentation
for more detail on this parameter (look at then GetOpenFileName
function).
Example: filters for browsing for a few document types, and also the
All Files filter, which should always be included.
Filter = "Data Explorer Documents" & vbNullChar & "*.dat,
*.spc, *.ms*" & vbNullChar & "Excel Documents" & vbNullChar
& "*.xls" & vbNullChar & "All Files" & vbNullChar &
"*.*" & vbNullChar & vbNullChar
Dependencies
modCommonDialog
Public Function changeExtension(strFileName As String, strNewExt As String) As String
Parameters
strFileName is the file name to start from (it is not modified)
strNewExt gives the replacement extension. It should
not have a leading period.
Return Value
strFileName with its extension stripped off and replaced with
strNewExt.
Example
changeExtension("C:\Documents\File.dat", "xls") would return
"C:\Documents\File.xls".
Dependencies
None
These functions are used to retrieve information from Data Explorer. Currently, they all serve to retrieve peak information. There are, of course, two groups of functions. One retrieves chromatogram peak data, the other retrieves spectrum data. All of these functions use the array formats described in the Variable Formats section.
Public Function getChroPeakCount(deDoc As DataExplorer.Document) As Long
Parameters
deDoc is the document object to work with.
Return Value
The function returns the number of peaks detected on the chromatogram
for the given document. If an error occurs, the return value is zero.
However, the return value is also zero if there were no detected
peaks.
Dependencies
Data Explorer Type Library
Public Function getChroPeakLargest(destarray As Variant, deDoc As DataExplorer.Document) As Boolean
Parameters
destarray should be a Variant which can be redimensioned.
It will be dimensioned and filled as a peak.
deDoc is the document to work with.
Return Value
getChroPeakLargest returns True on success, False
on failure
Dependencies
Data Explorer Type Library, getChroPeakTable
Public Function getChroPeakNumber(destarray As Variant, deDoc As DataExplorer.Document, n As Integer, Optional SortOrder = deChroPeakSortSpecNumOrTime) As Boolean
Parameters
destarray should be a Variant that can be redimensioned.
It will be dimensioned and filled as a peak.
deDoc is the document to work with.
n determines which peak to retrieve. 0 retrieves the
first peak.
SortOrder determines how the peaks are sorted before the nth
peak is retrieved from the list. Passing deChroPeakSortSpecNumOrTime
will cause the peaks to be sorted in chronological order. Passing
deChroPeakSortHeight
will cause the peaks to be sorted in order of increasing height.
Return Value
The return value is a success value.
Dependencies
Data Explorer Type Library, getChroPeakTable
Public Function getChroPeakTable(destarray As Variant, deDoc As DataExplorer.Document, SortOrder As Integer) As Boolean
Parameters
destarray should be a Variant that can be redimensioned.
It will be dimensioned and filled as a peak
table.
deDoc is the document to work with.
SortOrder determines how the peaks are sorted. Passing
deChroPeakSortSpecNumOrTime
will cause the peaks to be sorted in chronological order. Passing
deChroPeakSortHeight
will cause the peaks to be sorted in order of increasing height.
Return Value
The return value is a success value.
Dependencies
Data Explorer Type Library
Public Function getSpecPeakCount(deDoc As DataExplorer.Document) As Long
Parameters
deDoc is the document object to work with.
Return Value
The function returns the number of peaks detected on the spectrum for
the given document. If an error occurs, the return value is zero.
However, the return value is also zero if there were no detected
peaks.
Dependencies
Data Explorer Type Library
Public Function getSpecPeakLargest(destarray As Variant, deDoc As DataExplorer.Document) As Boolean
Parameters
destarray should be a Variant which can be redimensioned.
It will be dimensioned and filled as a peak.
deDoc is the document to work with.
Return Value
getSpecPeakLargest returns True on success, False
on failure
Dependencies
Data Explorer Type Library, getSpecPeakTable
Public Function getSpecPeakNumber(destarray As Variant, deDoc As DataExplorer.Document, n As Integer, Optional SortOrder = deSpecPeakSortMass) As Boolean
Parameters
destarray should be a Variant that can be redimensioned.
It will be dimensioned and filled as a peak.
deDoc is the document to work with.
n determines which peak to retrieve. 0 retrieves the
first peak.
SortOrder determines how the peaks are sorted before the nth
peak is retrieved from the list. Passing deSpecPeakSortMass
will cause the peaks to be sorted by mass. Passing
deSpecPeakSortIntensity
will cause the peaks to be sorted in order of increasing intensity.
Return Value
The return value is a success value.
Dependencies
Data Explorer Type Library, getSpecPeakTable
Public Function getSpecPeakTable(destarray As Variant, deDoc As DataExplorer.Document, SortOrder As Integer) As Boolean
Parameters
destarray should be a Variant that can be redimensioned.
It will be dimensioned and filled as a peak
table.
deDoc is the document to work with.
SortOrder determines how the peaks are sorted. Passing
deSpecPeakSortMass
will cause the peaks to be sorted by mass. Passing deSpecPeakSortIntensity
will cause the peaks to be sorted in order of increasing intensity.
Return Value
The return value is a success value.
Dependencies
Data Explorer Type Library
These functiosn serve to perform operations that sum across part of one of the windows. They are separated into two groups. One deals with summing in the chromatogram window, the other with the spectrum window.
Public Sub sumChromatogramPeaks(deDoc As DataExplorer.Document, CombineMode As Integer, BackSubCount As Integer)
Parameters
deDoc is the document in which to perform the summation
CombineMode is either deCombineAccumulate or deCombineAverage,
and determines how Data Explorer does the summation.
BackSubCount is how many spectra to use for background subtraction
for each chromatogram peak
Notes
This function will sum across all chromatographic peaks all at once.
It will generate a set of summation ranges that are the bounds of each
chromatogram, and a set of background subtraction ranges that are of the
size specified by BackSubCount. The function will first
attempt to put the background subtraction range onto the lower side of
each chromatogram peak, but failing that will put it on the upper side.
If it will not fit on either side, it omits the range for that peak.
Dependencies
getChroPeakTable
Data Explorer Type Library
Public Function sumSpectraOverChroPeak(ChroPeak As Variant, deDoc As DataExplorer.Document, SubRangeSize As Integer, SubLeft As Boolean, SubRight As Boolean, SumMode As Integer) As Boolean
Parameters
ChroPeak is a peak
array containing a chromatogram peak
deDoc is the document in which to perform the summation
SubRangeSize determines how many spectra are used in background
subtraction
SubLeft determines whether spectra on the left hand side of
the peak are used for backgroupd subtraction
SubRight determines whether spectra on the right hand side
of the peak are used for backgroud subtraction
SumMode is either deCombineAccumulate or deCombineAverage,
and determines how Data Explorer does the summation.
Return Value
The return value is a success value
Notes
This function will take the given chromatogram peak and sum across
it. If SubLeft and/or SubRight are true, then SubRangeSize
spectra from the sides of the peak dictated by SubLeft and SubRight
will be used for background subtraction. Note that if you specify
a SubRangeSize of n, and pass True for both SubLeft
and
SubRight, n spectra will be used on each side, not n spectra
total.
Dependencies
Data Explorer Type Library
Public Function makeXICFromSpecPeak(SpecPeak As Variant, deDoc As DataExplorer.Document, XICType As Integer, Width As Double) As Boolean
Parameters
SpecPeak is a peak
array containing a spectrum peak
deDoc is the document in which to perform the summation
XICType is either deExtractedIonMass or deExtractedIonWindow
and determines how Data Explorer does the summation
Width determines which masses are used to make the XIC
Return Value
The return value is a success value
Notes
If XICType is deExtractedIonMass then the mass range
used is a fraction of the width of the peak. If Width =
1, then the width of the peak is used. If Width < 1,
then the corresponding fraction of the width is used, and if Width
> 1, then a width greater than the peak width will be used. If XICType
is deExtractedIonWindow, then the masses used for the summation
with be those in the range of the peak centroid +/- Width.
Dependencies
Data Explorer Type Library
These functions are used to generate reports. Currently, all the reports are Excel spreadsheets. In the future, functions to generate reports in Word could be added (an interface for basic manipulation of Word along the same lines as the interface for Data Exporer and Excel is already in place). The genXLSReport function is the workhorse. It is a generic function for dumping all or a subset of the colums of an array to a specified place in an specified Excel document, along with headings for each column. The other functions are front ends to this function and are all very similar.
Public Function genXLSReport(vPeakTable As Variant, vColsToDisplay As Variant, bShowHeaders As Boolean, vHeaders As Variant, strXLSFile As String, strSheet As String, strStartCell As String, bAppend As Boolean, Optional bSaveAfter As Boolean = True, Optional nSheetPos As Integer = -1) As Boolean
Parameters
vPeakTable is the peak
table to work with
vColsToDisplay is an array of Boolean values that
corresponds to the columns (fields of an individual peak) in vPeakTable.
If the value in vColsToDisplay is True, then the corresponding
column is displayed. If the value is False, then the column
is not displayed
bShowHeaders determines if colum headings are printed
vHeaders is an array of strings that are the column headings
for each column. Even if a column is not to be displayed, it must
have an entry in vHeaders, although that entry can be blank as
it is ignored
strXLSFile is the full path to the Excel file in which to
put the report
strSheet is the name of the worksheet in which to put the
report
strStartCell is the name of the cell at which the report is
to start
bAppend determines if the report is appended to an existing
sheet, or if the sheet is overwritten
bSaveAfter determines if the document should be saved when
the report generation is done (see Notes)
nSheetPos determines where in the worksheet order the sheet
specified by strSheet is placed if it is created.
Return Value
The return value is a success value.
Notes
This is a very complex function. To explain what the parameters
do, it is necessary to explain the process performed by the function and
how this is affected by the parameters.
Very Important Note
Also, one must be very careful when using this function to make
sure that there are not multiple copies of Excel open, as this can case
very
undesirable results, and frequently causes all data in the requested document
to be erased (and the report not to be saved)!
Dependencies
Excel Type Library
Public Function genDefaultChroReport(vPeakTable As Variant, strXLSFile As String, strSheet As String, strStartCell As String, bAppend As Boolean, Optional bSaveAfter As Boolean = True, Optional nSheetPos As Integer = -1) As Boolean
Parameters
vPeakTable is the peak table from which to generate the report
strXLSFile is the full path of the Excel document in which
to put the report
strSheet is the name of the worksheet on which the report
is to be generated. It will be created if it does not exist
strStartCell is the cell at which to start the report
bAppend should be True if you want the report added
to an existing workbook, False if you want to overwrite an existing
workbook. If the desired workbook does not exist, bAppend
is ignored.
bSaveAfter is only used when appending to an already open
workbook. It should be True if you want the workbook to
be saved when report generation is complete, False otherwise.
nSheetPos is used when the worksheet specified by strSheet
has to be created. It specifies the desired 0 based index of the
new sheet. (see genXLSReport
for more detail).
Return Value
The return value is a success value
Notes
This generates an report for chromatogram peak data, displaying the
columns (with these names) Apex, Height, Area, Start (Time), and End (Time).
Dependencies
Excel Type Library, genXLSReport
Public Function genFullChroReport(vPeakTable As Variant, strXLSFile As String, strSheet As String, strStartCell As String, bAppend As Boolean, Optional bSaveAfter As Boolean = True, Optional nSheetPos As Integer = -1) As Boolean
Notes
This function performs exactly the same thing as genDefaultChroReport,
except it also displays the Start (Spec #) and End (Spec #) columns (with
those names) after the columns displayed by genDefaultChroReport.
Dependencies
Excel Type Library, genXLSReport
Public Function genDefaultSpecReport(vPeakTable As Variant, strXLSFile As String, strSheet As String, strStartCell As String, bAppend As Boolean, Optional bSaveAfter As Boolean = True, Optional nSheetPos As Integer = -1) As Boolean
Notes
This function behaves in the same way as genDefaultChroReport,
except that it takes a spectrum peak table, and uses the following headers:
Mass, Intensity, Centroid, Area, Charge, Start, and End
Dependencies
Excel Type Library, genXLSReport
These functions perform start to finish processing on one or more files. While they are useful in and of themselves, they are primarily intended as examples of how one might combine the various functions in this toolkit into a useful program.
Public Function autoMultiComplexToMulti(vFiles() As String, nFileCount As Integer, Optional strSetFile As String = "", Optional bQuiet As Boolean = False) As Boolean
Parameters
vFiles is an array of full paths to files that are to be processed
nFileCount is the number of files in vFiles to processes
strSetFile is the full path to a .set file to load for each
file processed
bQuiet determines the visibility of Data Explorer and Excel
if they are opened by this program.
Return Value
The return value is a success value
Notes
This function assumes that each file it processes has a chromatogram
with multiple peaks. For each file, it generates an Excel file of
the same name in the same directory (e.g. for C:\Mariner\Data\file01.dat,
C:\Mariner\Data\file01.xls
will be generated). Each Excel file contains one worksheet that has
the chromatogram peak table. For each chromatogram peak, this function
sums across it with background subtraction, and creates a worksheet with
the resulting spectrum peak table. These worksheets are numbered
so that they correspond to the order of the peaks in the chromatogram peak
table.
Dependencies
loadExcel
loadDataExplorer
loadDEDoc
loadXLDoc
releaseXLDoc
releaseDEDoc
releaseDataExplorer
releaseExcel
changeExtension
getChroPeakTable
getSpecPeakTable
genDefaultChroReport
genDefaultSpecReport
sumSpectraOverChroPeak
Data Explorer Type Library
Public Function autoMultiSimpleToSingle(vFiles() As String, nFileCount As Integer, outFileName As String, Optional strSetFile As String = "", Optional bQuiet As Boolean = False) As Boolean
Parameters
vFiles is an array of full paths to files that are to be processed
nFileCount is the number of files in vFiles to processes
outFileName is the full path to the Excel workbook to generate
strSetFile is the full path to a .set file to load for each
file processed
bQuiet determines the visibility of Data Explorer and Excel
if they are opened by this program.
Return Value
The return value is a success value
Notes
This function assumes that each file it processes has a chromatogram
with a single peak. In the output file (outFileName) it generates
a worksheet (the summary sheet) that contains the name of each file and
the largest chromatogram peak it found in that file. For each file,
it takes the largest chromatogram peak, sums across it with background
subtraction, and puts the resulting spectrum peak list in a worksheet.
These worksheets are numbered so that they correspond to the order of files
in the summary sheet.
Dependencies
loadExcel
loadDataExplorer
loadDEDoc
loadXLDoc
releaseXLDoc
releaseDEDoc
releaseDataExplorer
releaseExcel
changeExtension
getChroPeakLargest
getSpecPeakTable
genDefaultChroReport
genDefaultSpecReport
sumSpectraOverChroPeak
Data Explorer Type Library
These functions are used for calculating polymer information. The cropSpecData is used internally only, but some brief documentation on what it does is given anyways. The function that other macros can use is calcPolyInfo.
Private Sub cropSpecData(vRawData As Variant, vPeakData As Variant, vDestArray As Variant)
Notes
This function takes the raw spectrum data in vRawData, and
uses vPeakData to fill vDestArray with only those data
points that lie within a peak. This is used by calcPolyInfo
when it is calculating polymer information in the 'non-fuzzy' mode.
Dependencies
None
Public Sub calcPolyInfo(deDoc As DataExplorer.Document, Mn As Double, MW As Double, Mz As Double, PDI As Double, Optional xMin As Double = 0, Optional xMax As Double = 0, Optional bFuzzy As Boolean = False)
Parameters
deDoc is the document to work with
Mn is a variable that will be filled with the Mn for the polymer
data
MW is a variable that will be filled with the MW for the polymer
data
Mz is a variable that will be filled with the Mz for the polymer
data
PDI is a variable that will be filled with the PDI for the
polymer data
xMin is the minimum x value to use for the calculation.
xMax is the maximum x value to use for the calculation
bFuzzy determines which kind of calculation is performed
Notes
If xMin and xMax are both 0, then the currently
visible part of the spectrum will be used.
If bFuzzy is true, then all of the raw data will be used in
performing the averages that generate the M values and the PDI. If
bFuzzy
is false, then only data that is within a peak will be used.
Dependencies
Data Explorer Type Library
This module has the functions needed for using the standard windows common dialogs. There is only one function that should be used by macros, and that is the FileDialog function.
Function FileDialog(Optional strInitDir As String, Optional strFilter As String = "All files (*.*)" & vbNullChar & "*.*" & vbNullChar & vbNullChar, Optional intFilterIndex As Integer = 1, Optional strDefaultExt As String = "", Optional strFileName As String = "", Optional strDialogTitle As String = "Open File", Optional hwnd As Long = -1, Optional fOpenFile As Boolean = True, Optional ByRef lngFlags As Long = fdOFN_OPENEXISTING) As String
Parameters
strInitDir is the directory in which the dialog should start
strFilter is a string specifying the file filters (see Notes)
inFilterIndex is the index of the file filter to have selected
when the dialog appears
strDefaultExt is the extension to append to the file name
if the user does not specify one
strFileName is the default file name to have when the dialog
appears
strDialogTitle is the text to show in the title bar of the
dialog
hwnd is the window handle of the owner window. This
should usually be left as the default value.
fOpenFile should be True if you want an Open dialog,
False
if you want a Save As dialog
lngFlags specifies flags for the dialog box
Return Value
The return value is the file the user selected, or an empty string
if the user pressed cancel.
Notes
Format of strFilter:
strFilter should be a string composed of pairs of strings separated
by a vbNullChar, and terminated by two vbNullChar's.
Each pair of strings represents a filter, the first string specifying the
text to display, and the second string specifying the file filter.
Example: "Text Files" & vbNullChar & "*.txt" & vbNullChar
& "All Files" & vbNullCar & "*.*" & vbNullChar & vbNullChar
lngFlags is a complicated argument. For a full reference of possible values, look up GetOpenFileName in the Win32 SDK. Note that the constants listed in the Win32 SDK are the same here. Some common combinations are also defined in this module. Those constants start with fd. To combine constants, use the Or keyword, as demonstrated with the fd constants below.
Common Constants
OFN_PATHMUSTEXIST indicates that the path the user specified
must exist
OFN_FILEMUSTEXIST indicates that the file the user specified
must exist
OFN_HIDEREADONLY will cause read only files to be hidden
OFN_OVERWRITEPROMPT will cause the user to be prompted if,
in a Save As dialog, they choose an existing file.
Predefined Combinations
fdOFN_OPENEXISTING = OFN_PATHMUSTEXIST Or OFN_FILEMUSTEXIST Or
OFN_HIDEREADONLY
fdOFN_SAVENEW = OFN_PATHMUSTEXIST Or OFN_OVERWRITEPROMPT Or OFN_HIDEREADONLY
fdOFN_SAVENEWPATH = OFN_OVERWRITEPROMPT Or OFN_HIDEREADONLY
Dependencies
None