Databases Reference
In-Depth Information
FIGURE 5.38
Sample data file.
, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
'Return Workbook/Worksheet Name for storage to Global (Public)
Variable
Open_Template = ActiveWorkbook.Name
End Function
Notice that the function checks to see if the specified template exists, and terminates the macro
if it does not. If the specified template does exist, it is loaded into Excel, and the name of the
Workbook is returned by the function for storage into a public variable.
The last phase to complete this sample project is to open, sort, and extract the information
required from all of the sample data files (Figure 5.37). The snippet of code which accomplishes
this will be discussed at length.
'Loop through all reports and extract pertinent information
rowctr = 3: colctr = 1 'Start Populating Template on Row 3, col 2
For rptnbr = 1 To TotalFiles
colctr = colctr + 1
'Load the first report
DataBook = AutoLoadFile(FileNames(rptnbr))
Call
SortWorksheet
(DataBook,
Workbooks(DataBook).Sheets(1).Name, 1, 4, 1, 2, False, 4, False)
'Write in RT Value
Workbooks(ReportBook).Worksheets(1).Cells(rowctr, colctr) = _
Format(Workbooks(DataBook).Worksheets(1).Cells(2, 3), "#0.000")
FIGURE 5.39
Final data file created by Autoload and Extract sample.
