Access Cookbook - Ken Getz [158]
Public Function FillDirList(ByVal strFileSpec As String, _
astrFiles( ) As String) As Integer
' Given the file specification in strFileSpec, fill in the
' dynamic array passed in avarFiles( ).
Dim intNumFiles As Integer
Dim strTemp As String
On Error GoTo HandleErr
intNumFiles = 0
' Set the filespec for the dir( ) and get the first filename.
strTemp = Dir(strFileSpec)
Do While Len(strTemp) > 0
intNumFiles = intNumFiles + 1
astrFiles(intNumFiles - 1) = strTemp
strTemp = Dir
Loop
ExitHere:
If intNumFiles > 0 Then
ReDim Preserve astrFiles(intNumFiles - 1)
acbSortArray astrFiles( )
End If
FillDirList = intNumFiles
Exit Function
HandleErr:
Select Case Err.Number
Case 9
' The array needs to be resized
' Just add room for 100 more files.
ReDim Preserve astrFiles(intNumFiles + 100)
Resume
Case Else
FillDirList = intNumFiles
Resume ExitHere
End Select
End Function
TIP
Rather than resizing the array for each matching file name, the FillDirList function traps the error that occurs when the array is full, and resizes it 100 slots at a time. Using the Redim Preserve statement is quite expensive in VBA, and you should consider looking for ways to call it as seldom as possible. In this example, the code resizes the array to the correct size once it's done filling in all the file names.
Import basSortArray from 07-08.MDB. This is the same sorting code that we used in the Solution in Recipe 7.7.
Discussion
The list box in this example uses a list-filling callback function, FillList, to supply its data. (See the Solution in Recipe 7.5 for information on callback functions.) Here's the code:
Private Function FillList(ctl As Control, _
varID As Variant, lngRow As Long, lngCol As Long, _
intCode As Integer)
Static astrFiles( ) As String
Static intFileCount As Integer
Select Case intCode
Case acLBInitialize
If Not IsNull(Me.txtFileSpec) Then
intFileCount = FillDirList(Me.txtFileSpec, astrFiles( ))
End If
FillList = True
Case acLBOpen
FillList = Timer
Case acLBGetRowCount
FillList = intFileCount
Case acLBGetValue
FillList = astrFiles(lngRow)
Case acLBEnd
Erase astrFiles
End Select
End Function
In FillList's acLBInitialize case, it calls the FillDirList function to fill in the astrFiles array, based on the value in the txtFileSpec text box. FillDirList fills in the array, calling acbSortArray along the way to sort the list of files, and returns the number of files it found. Given that completed array, FillList can return the value from the array that it needs when requested in the acLBGetValue case. It uses the return value from FillDirList, the number of files found, in response to the acLBGetRowCount case.
There's also an interesting situation you should note in the FillList and FillDirList routines. FillList declares a dynamic array, astrFiles, but doesn't give a size because it doesn't yet know the number of files that will be found. FillList passes the array off to FillDirList, which adds filenames to the array based on the file specification until it doesn't find any more matches. FillDirList returns the number of matching filenames, but it also has the side effect of having set the array's size and filled it in. Here's the code that does the work. This code fragment uses the ReDim Preserve keywords to resize the array every time it finds a matching filename:
' Set the filespec for the dir( ) and get the first filename.
strTemp = Dir(strFileSpec)
Do While Len(strTemp) > 0
intNumFiles = intNumFiles + 1
astrFiles(intNumFiles - 1) = strTemp
strTemp = Dir
Loop
FillDirList uses the Dir function to create the list of files. This function is unusual in that you call it multiple times. The first time you call it, you send it the file specification you're trying to match, and Dir returns