Shearching For Files

[Martin Harris]

Hi All
I need some code to search for a file that could be in any directory or subdirectory. I found the code below and whilst if you tap though it seems to be doing its job but when let loose properly it fails with an error 52 issue. Cut and paste the whole code in to a module and then run the following in the immediate window.
Dim strFile As String
strFile = ListFiles("C:\", "thefilename.ext", True)

I would be truly grateful if anybody could explain why this might be.





Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
Optional bIncludeSubfolders As Boolean, Optional lst As ListBox)
On Error GoTo Err_Handler
'Purpose: List the files in the path.
'Arguments: strPath = the path to search.
' strFileSpec = "*.*" unless you specify differently.
' bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
' lst: if you pass in a list box, items are added to it. If not, files are listed to immediate window.
' The list box must have its Row Source Type property set to Value List.
'Method: FilDir() adds items to a collection, calling itself recursively for subfolders.
Dim colDirList As New Collection
Dim varItem As Variant

Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)


'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window.
If lst Is Nothing Then
For Each varItem In colDirList
ListFiles = varItem
Next
Else
For Each varItem In colDirList
lst.AddItem varItem
Next
End If

Exit_Handler:
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Function
Private Function FillDir(colDirList As Collection, ByVal strFolder As String, strFileSpec As String, _
bIncludeSubfolders As Boolean)
'Build up a list of files, and then add add to this list, any additional folders
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colDirList.Add strFolder & strTemp
strTemp = Dir
Loop
If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop
'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
Next vFolderName
End If
End Function
Public Function TrailingSlash(varIn As Variant) As String
If Len(varIn) > 0& Then
If Right(varIn, 1&) = "\" Then
TrailingSlash = varIn
Else
TrailingSlash = varIn & "\"
End If
End If
End Function

 

[S O WXLS]

Try something like this instead:

Function FindFile(FileName As String) As String

'// Example of FileName could be "MyFile.xlsx"
'// Searches whole of C Drive for file.

x = CreateObject("WScript.Shell").Exec("CMD /C DIR ""C:\*" & FileName & """ /S /B /A:-D").StdOut.ReadAll

If x = "" Then x = "File Not Found."

FindFile = x

End Function

 

[Martin Harris]

Did the code I posted make sense to you? Could you get it to work?

 

[S O WXLS]

In all honesty I didn't even check it - There's a lot of variable declarations going on and unnecessary functions (plus a sub that calls itself?) so I'm not sure what you're trying to achieve with all that code.

If you want to search for a file and return it's full path, the code I posted will do that much quicker and uses a fraction of the resource that your current code would use. Also if you wrap your code in code tags by selecting it and clicking the [#] button in the editor - it will make it easier to read for everyone and you may get more responses.

 

[Martin Harris]

Thanks for your advice about the [#] button.