Databases Reference
In-Depth Information
FIGURE 5.33
Directory structure for Auto Load files example.
Each data file contains four columns that store the following information: Compound, Area,
Retention Time, and Height. For the purpose of this example, suppose that it is desired to create
a macro that will automatically open every data file for each compound (at all the various methanol
percentages 40, 50, 60, and 70) and create a report that extracts for each method the retention time
corresponding to the largest area with the largest peak height as a secondary selection factor. (In
other words, the first selection criteria is the area; should two areas be equal, choose the retention
time corresponding to the area that has the greatest height.) The final goal here would be to produce
a report such as that depicted in Figure 5.35.
The development of a macro capable of creating the report shown in Figure 5.35 requires the
writer to develop code capable of completing the following processes. The macro must first be
pointed to a file structure, and from there it must automatically be able to navigate through the
underlying file structure and open all the data files required to construct the report depicted in
Figure 5.35. Each data file must be sorted first by area and then by height. Once this has been
accomplished, the retention time corresponding to the peak with the greatest area can be extracted
from each data file. Once this has been done, the final report should be saved and possibly printed.
Figure 5.36 shows the sample GUI utilized to execute this macro. It is simplistic in design in that
the user merely points to a file structure and clicks the “Extract from Files” button to run the sample
macro. The first major step in the process of this macro is to determine the total number of files that
must be accessed and the full path to each file's location. The function
Create_File_List
con-
structed in Chapter 2 will accomplish this task quite nicely by placing all of this information into an array.
TotalFiles = Create_File_List(frm5AutoLoad.TextBox_Path.Text,
"xls", FileNames(), True, True, True)
Upon execution of this line of code, the total number of Excel (*.xls) files (in this case 12)
will be stored in the variable
TotalFiles
. The name and location of each of these files (or the
FIGURE 5.34
Example data file for Auto Load files example.
