Finding files via code 1

[wacker]

I need to be able to find all access databases on my c:\ via code within Acccess. The code I have written is as follows:
Private Sub cmdFindFiles_Click()
Dim strFileName, strPath As String

strPath = "c:\*.mdb"
strFileName = Dir$(strPath)

Do While strFileName > ""
'some code is entered here to update table with results

strFileName = Dir$
Loop
End Sub

The problem is it only finds files within the root direcory of C:\ and not the whole of the c:\drive's folders etc.
Any help would be appreciated.

 

[danvlas]

Here is a function that does it. It stores the locations of the file in the 'resultsarray'.
It searches all mapped drives, so it may take a while until it finishes.
The idea is to check each subfolder of a root, then check files in the root, then take the first subfolder and find its subfolders and so on...

Function StoreResults(FileName)
On Error Resume Next
Dim mypath, MyDirName, j, Dimension, myFileName, varreturn
Dim NoOfFiles As Long
ReDim fldarray(1, 0)
ReDim resultsarray(0)
For j = 65 To 90
mypath = Chr(j) & ":\"
1:
MyDirName = Dir(mypath, vbDirectory)
Do While MyDirName <> &quot;&quot;
NoOfFiles = NoOfFiles + 1
    If MyDirName <> &quot;.&quot; And MyDirName <> &quot;..&quot; Then
        If (GetAttr(mypath & MyDirName) And vbDirectory) = vbDirectory Then
        Dimension = UBound(fldarray, 2) + 1
        ReDim Preserve fldarray(1, Dimension)
        fldarray(0, Dimension - 1) = 0
        fldarray(1, Dimension - 1) = mypath & MyDirName & &quot;\&quot;
        varreturn = SysCmd(acSysCmdSetStatus, mypath & MyDirName)
        End If
    End If
    MyDirName = Dir
Loop
myFileName = Dir(mypath)
Do Until myFileName = &quot;&quot;
If myFileName = FileName Then
Dimension = UBound(resultsarray, 1) + 1
ReDim Preserve resultsarray(Dimension)
resultsarray(Dimension - 1) = mypath & myFileName
'Results.RowSource = Results.RowSource & mypath & myFileName & &quot;;&quot;
'Results = mypath & myFileName
End If
myFileName = Dir
varreturn = SysCmd(acSysCmdSetStatus, mypath & myFileName)
Loop
FileNotFound:
For Dimension = 0 To UBound(fldarray, 2) - 1
If fldarray(0, Dimension) = 0 Then
mypath = fldarray(1, Dimension)
fldarray(0, Dimension) = 1
GoTo 1
End If
Next Dimension
NextDrive:
Next j
Debug.Print &quot;results:&quot;
For j = 0 To UBound(resultsarray, 1)
Debug.Print resultsarray(j)
Next j
varreturn = SysCmd(acSysCmdSetStatus, UBound(resultsarray, 1) & &quot; files found&quot;)
StoreResults = UBound(resultsarray, 1)
DoCmd.Hourglass False
ReDim fldarray(0, 0)
ReDim resultsarray(0)
End Function

HTH,

Dan

 

[danvlas]

Or use FileSearch Object:

Sub test()
Dim i
With Application.FileSearch
.NewSearch
.LookIn = "\\ShareName\Path\"
.SearchSubFolders = True 'or False
.FileName = "YourFileName"
.MatchTextExactly = False 'or True
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

HTH,

Dan