I have a function that makes use of the Application.FileSearch Object in MS Access. I basically want to search files on the network within a folder on my organizations network. The folder has many sub-folders within sub-folders. I essentially want the search to only go in at two levels deep. If I search the entire folder and all of its sub-folders, the search could take 5 or 10 hours. Is there a way I can restrict the number of sub-folders to parse through with the Office.FileSearch Object in VBA?




Option Compare Database
Option Explicit

Function CustomFindFile(strFileSpec As String)
'This procedure demonstrates a simple file-search
'routine that displays a message box containing the
'names of all files in the "c:\" directory that
'match the file specifications provided in the
'strFileSpec argument.
'The strFileSpec argument can contain one or more file
'specifications in a semicolon-delimited list. For example, the
'following strFileSpec argument will return all files
'in the "c:\" that contain these extensions: "*.log;*.bat;*.ini"
Dim fsoFileSearch As Office.FileSearch
Dim varFile As Variant
Dim strFileList As String

' If the input in valid, then process the file search.
If Len(strFileSpec) >= 3 And InStr(strFileSpec, "*.") > 0 Then
Set fsoFileSearch = Application.FileSearch
With fsoFileSearch
.NewSearch
'.LookIn = "D:\test\" RETURNS 2 FILES -- VERY FAST
'BELOW LOOKIN PROPERTY POINTS TO THE NETWORK
'PERHAPS 1 to 2 GIGS OF FILES TO LOOK THOUGH. ONLY
'WANT TO ONLY TO SEARCH 2 SUB-FOLDERS IN.
.LookIn = "\\NETWORKFOLDER\Prod\Images\22FEB06\"
.FileName = strFileSpec
.SearchSubFolders = True


If .Execute() > 0 Then

For Each varFile In .FoundFiles
strFileList = strFileList & varFile & vbCrLf
Next varFile
End If
End With
MsgBox strFileList
Else
MsgBox strFileSpec & " is not a valid file specification."
Exit Function
End If
End Function


Public Function TestFunction()
MsgBox "THIS IS A TEST"

End Function