Databases Reference
In-Depth Information
FIGURE 5.40
Setting the Calculation option in Excel.
'Write in Compound Number
Workbooks(ReportBook).Worksheets(1).Cells(rowctr, 1) =
Workbooks(DataBook).Worksheets(1).Cells(2, 1)
'Increment row counter - reset column counter
If rptnbr Mod 4 = 0 Then rowctr = rowctr + 1: colctr =
1
Workbooks(DataBook).Close (False)
Next rptnbr
Looking at Figure 5.37, it is clear that data should be populated into the template starting at row 3
and column 2. The compound numbers should be added to the template starting at row 3, column 1.
To accomplish this, two variables are utilized as row and column counters
(rowctr
and
colctr)
.
Notice that when the remainder of the loop divided by 4 is equal to zero, the row is incremented and
the column number is reset back at the beginning. With each loop iteration, a new data file is opened,
its contents sorted utilizing the
SortWorksheet
subroutine developed in Chapter 2, and the proper
retention time is extracted from that data file and placed in the report. The data file is then closed,
and the loop increments and repeats the process until all files have been processed.
Looking at Figure 5.38, notice that the largest area (86.897) is repeated twice. The Worksheet
is sorted first by area and then by peak height. The area with the peak height of 2.52 is selected,
and its corresponding retention time of (0.800) is selected and placed into the final report as shown
in row 3, column 2 (B) of Figure 5.39.
One final point that must be mentioned before the user attempts to run this example is the
Calculation setting option in Excel. If the Calculation setting in Excel is set to automatic, it will
appear that this sample does not function correctly. This is because, after processes such as sorting
occur in a Worksheet, Excel will automatically recalculate all the values derived from formulas
within the Worksheet. The calculation setting can be changed in Excel by selecting
To
ols->
O
ptions
and choosing the Calculations tab as shown in Figure 5.40.
Obviously the auto loading and extraction example shown previously are built relying heavily
upon material covered in the previous chapters. It is also somewhat rudimentary in that it has no
built-in checks. A “real” application of this type would check the reasonableness of the values
extracted, the existence of the proper files, and so forth. It is left to the reader to modify the above
example to suit their own needs.
