Databases Reference
In-Depth Information
specific type of data file. The landmarks may not always be in the same place (row- and columnwise)
for every data file, but the important point is that they will always be present at a specific juncture
in every data file of the type to be examined. To accomplish this task, the following landmarks will
be utilized: [Samples], [RawData], and [Results]. Although [Screen Title] is also a landmark in
this data file (and there are some other landmarks as well), it is not necessary to determine its
location for this task.
The first step is to declare some variables to hold the vital parameters that will be needed to
manipulate information around the Worksheet.
Dim ExtractBook As String
Dim ResultsRow As Integer, ResultsCol As Integer
Dim RawDataRow As Integer, RawDataCol As Integer
Dim SamplesRow As Integer, SamplesCol As Integer
Dim ptrRow As Integer, LoopCounter As Integer
ExtractBook
is a string variable utilized to hold the name of the Workbook being accessed.
Utilizing global string variables suffixed with “Book” is an excellent way to manage the names
of Workbooks that will be utilized in a project. The next six variables are set up to hold the
positions (Row and Column) of each landmark. Finally, a variable called
ptrRow
is declared.
This nomenclature denotes a “pointer” to a “row.” The function of this variable is to “point” (or
store) the row number of interest (or row number to be acted upon). Such pointers will be utilized
a great deal in this text, and most errors in automated data analysis result when pointers are not
pointing to where the designer intended them to point.
LoopCounter
is a variable that simply
keeps track of how many times a
While
or
Do
loop has cycled.
The first task is obviously to load a file to be acted upon. This is easily accomplished utilizing
the code discussed in Chapter 1.
'Load Data File
ExtractBook = LoadWithDialog("xls", "c:\", "Load the
Extract.xls file for this demo!")
Once the file is loaded, it is necessary to determine the location of the landmarks within the file.
Location here meaning the starting row and column positions of the landmarks. Again, this is easily
accomplished utilizing the code in Chapter 1.
'Determine Landmark Positions
ResultsRow = FindRow(FindPos(ExtractBook, ActiveSheet.Name,
"[Results]", False, False))
ResultsCol = FindCol(FindPos(ExtractBook, ActiveSheet.Name,
"[Results]", False, False))
RawDataRow = FindRow(FindPos(ExtractBook, ActiveSheet.Name,
"[RawData]", False, False))
RawDataCol = FindCol(FindPos(ExtractBook, ActiveSheet.Name,
"[RawData]", False, False))
SamplesRow = FindRow(FindPos(ExtractBook, ActiveSheet.Name,
"[Samples]", False, False))
SamplesCol = FindCol(FindPos(ExtractBook, ActiveSheet.Name,
"[Samples]", False, False))
With the locations of the landmarks established, the modifications to the [Results] area of the
Worksheet can now be performed. First, the number of measurements is determined for each sample.
A header “[No.]” is written into the column above where the number of measurements is to be stored.
