0

I have a function that works to search through the subfolders of a given directory and finds the file name I need. However, it only goes through one set of subfolders, finding the first one and then going through to the end of the subfolders. However, it then just stops. I have looked through various threads and tried different options but no joy.

I need it to then loop back to the root directory (say, sPath=C:\Windows) and look at the next subfolder, go through that whole directory, come back to the root folder, and so on until it finds the file it needs. I cannot seem to get that part to work, hoping someone here can help point out what I am missing. I am trying to keep this set at a higher level root folder rather than have to start lower in in the directory to get it to work. Here is the function:

Function recurse(sPath As String, strname As String, strName3 As String)

Dim FSO As New FileSystemObject
Dim myFolder As Scripting.Folder
Dim mySubFolder As Scripting.Folder
Dim myFile As Scripting.file    

Dim strJDFile As String
Dim strDir As String
Dim strJDName As String

Set myFolder = FSO.GetFolder(sPath)

' strName = Range("a2").Offset(0, 3)
strName3 = Replace(strName3, "/", " ")

For Each mySubFolder In myFolder.SubFolders
Debug.Print " mySubFolder: " & mySubFolder

For Each myFile In mySubFolder.Files        

    If "*" & myFile.Name & "*" Like "*" & strName3 & "*" Then
        strJDName = myFile.Name
        strDir = mySubFolder & "\"
        strJDFile = strDir & strJDName

        recurse = strJDFile

        Exit Function

    Else
        Debug.Print "  myFile.name: " & myFile.Name
    End If

Next

recurse = recurse(mySubFolder.Path, strname, strName3)

Next

End Function
learningthisstuff
  • 91
  • 2
  • 15

1 Answers1

1

Here is a routine you may be able to adapt to your use, if you are running Excel under Windows.

  • Pick a base folder using the Excel folder picker routine
  • Enter a file name mask (eg: Book1.xls*)
  • Uses the Dir command window command to check all the folders and subfolders for files that start with Book1.xls
  • The results of the command are written to a temporary file (which is deleted at the end of the macro)
    • There is a way to write it directly to a VBA variable, but I see too much screen flicker when I've done that.
  • The results are then collected into a vba array, and written to a worksheet, but you can do whatever you want with the results.
Option Explicit
'set references to
'   Microsoft Scripting Runtime
'   Windows Script Host Object model
Sub FindFile()
    Dim WSH As WshShell, lErrCode As Long
    Dim FSO As FileSystemObject, TS As TextStream
    Dim sTemp As String
    Dim sBasePath As String
    Dim vFiles As Variant, vFullList() As String
    Dim I As Long
    Dim sFileName As String

    sTemp = Environ("Temp") & "\FileList.txt"

'Select base folder
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    If .Show = -1 Then 'if OK is pressed
        sBasePath = .SelectedItems(1)
    Else
        Exit Sub
    End If
End With

'File name mask
sFileName = InputBox("Entire File Mask", "File Finder")

Set WSH = New WshShell
lErrCode = WSH.Run("CMD /c dir """ & sBasePath & "\*" & sFileName & """ /A-D /B /S > " & sTemp, xlHidden, True)

If Not lErrCode = 0 Then
    MsgBox "Problem Reading Directory" & _
        vbLf & "Error Code " & lErrCode
    Exit Sub
End If


Set FSO = New FileSystemObject
Set TS = FSO.OpenTextFile(sTemp, ForReading, False, TristateFalse)

vFiles = Split(TS.ReadAll, vbLf)
TS.Close
FSO.DeleteFile sTemp
Set FSO = Nothing
Set WSH = Nothing

ReDim vFullList(1 To UBound(vFiles), 1 To 1)
For I = 1 To UBound(vFiles)
    vFullList(I, 1) = vFiles(I)
Next I

Dim rDest As Range
Set rDest = Cells(1, 2).Resize(UBound(vFullList, 1), UBound(vFullList, 2))

With rDest
    .EntireColumn.Clear
    .Value = vFullList
    .EntireColumn.AutoFit
End With

End Sub
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60