Methods of Loading and Saving Data in Excel - Automated Data Analysis Using Excel - page 69

FIGURE 2.20 The List Files in Directory example.
The function Create_File_List not only reads the files residing in a particular folder
(indicated by DirPath$ ) into an array, but also has the ability to limit files written to the array
to those with a certain suffix (*.csv, *.txt, etc.). If all files are to be chosen, then an asterisk (*)
should be passed as sFileType . The function can also return filenames from subfolders within
the chosen folder (specified by DirPath$) by setting the passed parameter bDoSubs equal to true.
An additional option is the ability to prefix the filename returned with the path indicating its location.
This can be accomplished by setting the passed parameter bPrefixFileNameWithPath to
True. This is an especially attractive option should the user choose to return the filenames contained
within subfolders of the specified folder, as it will be readily apparent whether the file returned
lies in the “top most” folder, or is contained within a subfolder.
Two points are worth mentioning about this function that make it unique compared with the
discussions that have been entertained so far. First, and most important, is that this function utilizes
recursive calls. This means, in layman's terms, that, under the right conditions, this function will call
itself while the code is executing within itself . In this instance, recursion is utilized to process any
subdirectories that may reside under the chosen directory specified by DirPath$. The second point
is that this function utilizes what is called a static variable. Normally, when a function or subroutine
is called, all the variables within the function or subroutine are cleared and reset to their default states.
Because this function utilizes recursion to process any subdirectories that may fall beneath the chosen
directory, the number of files counted (stored in the variable fileCount ) must be preserved after
each recursive call. This is done by declaring the variable fileCount utilizing the Static
statement. When the function is called, the Boolean passed parameter RecursiveCall is set to True if
the function is to be called recursively, that is, called from within itself. If the function is called outside
of itself (or normally), the Boolean passed parameter RecursiveCall is set to False. The Boolean
passed parameter RecursiveCall simply indicates to the function whether or not it should reset
the static variable fileCount to 0, which must be done when a new file list is to be created.
Function Create_File_List(DirPath$, sFileType As String,
FileList() As String, _
bDoSubs As Boolean, bPrefixFileNameWithPath As Boolean, _
Optional RecursiveCall As Boolean = False) As Integer
'Subroutine Extracts Selected Files from a given path "dirpath$"
'SFileType to specify certain kinds of files only (csv, xls,
etc.) use * for all
'FileList is the Array of Files Returned
'Search and Extract from SubDirectories set BDoSubs = True
'Include path in filename set bPrefixFileNameWithPath = True