Making Folder and Subfolder Search Recursive - Finding File Name matching Cell and Getting Path

[qathydana]

Hello,

I've been working on this for hours, and I'm ready to ask for help. I'm a relative VBA beginner, and have been teaching myself over the past month.

I know exactly what steps I want this program to take, but am having trouble implementing those steps in a way that is concise, because I know that there is probably a way to make the code recursive so that I don't have to repeat so much of it, but I'm not sure what that way is and I'm having trouble finding a solution that fits elsewhere.

I have tried making multiple layers of conditional loops, but the problem is that I don't know in advance how many file names the column of file names will have. I want there to be two columns -- one with the file names, and one for the file paths to be filled in once VBA finds a file. I also don't know in advance how many levels of folders the function will have to search before it finds the file, but the file names ARE unique, and the extensions will be included. Essentially, I don't know what folder the file will be in, but I know it is somewhere, and I know that the parent sPath folder I provide contains it.

So:

I'm trying to create a recursive VBA function that will look in a provided folder (called in the sub as "sPath"), check to see if any of the files match the file name in a cell provided in my excel sheet, and then if not, check within all of the subfolders as well (in other words, I want the process to repeat for any number of "subfolder layers" so to speak). Once it finds a match, I want it to put the folder path into the cell to the left of it, and then go down one row to the next cell with a file name and repeat the process. The process as I have it set up currently calls the function from within a sub (see bottom of posted code).

This is what I have so far. I've had several different versions, one of which I'm sure looped correctly through cells in the column and through files in my folder, but I'm having trouble implementing the recursive aspect. As you can see below, I have two chunks of code loops -- one that checks to see if any of the *files* in myFolder match the cell value, and then, if no answer is found (i.e. printed in the destination cell), the second chunk of loops will look in the subfolders of that folder, and then the files in each of those subfolders.

--

Function Recurse(sPath As String) As String  Dim FSO As New FileSystemObject
  Dim myFolder As Folder
  Dim mySubFolder As Folder
  Dim myFile As File
  Dim destRow As Integer
  Dim i As Integer
  Dim fileCol As Range
  Dim fileCell As Range
  Dim fileName As String
  Dim fullNameLen As Integer
  Dim filePathFull As String
  Dim fullPathLen As Integer
  Dim filePath As String
 
' Gets the folder specified when the Recurse function was called in RunRecurse.[INDENT]Set myFolder = FSO.GetFolder(sPath)
Set fileCol = ActiveSheet.Columns(2)[/INDENT]
 
  Application.ScreenUpdating = False
 
' destRow attempts to ignore the header row, since I don't want the program to search
' endlessly for "File Name". Originally I had this as a "for each" loop for each cell in the
' column, but I changed it because I don't know otherwise how to make it ignore the
' header row in its comparisons.

      For i = 1 To fileCol.Cells - 1[INDENT]      destRow = i + 1
        If IsEmpty(fileCol.Cells(destRow, 2).Value) Then
[/INDENT]
[INDENT=2]   Exit For[/INDENT]
[INDENT]        End If[/INDENT]
     
      For Each myFile In myFolder.Files[INDENT]        fileName = myFile.Name
        fullNameLen = Len(fileName)
        filePathFull = myFile.Path
        fullPathLen = Len(filePathFull)
        filePath = Left(filePathFull, fullPathLen - fullNameLen)
        If fileName = ActiveSheet.Cells(destRow - 1, 2).Value Then
               ActiveSheet.Cells(destRow, 1).Value = filePath
               Exit For[/INDENT]
[INDENT]        End If[/INDENT]
     
       Next
 
' Now, after the program has looped through every *file* in the folder, I want it to
' check to see if the destination cell has been filled with a file path yet. If not, I go
' to the next round of checking, and check in the subfolders. I don't like this, because
' it's inefficient, but I'm not sure how to make VBA automatically check files AND
' AND the files within subfolders in a single loop. This way, the code only 'delves deep'
' when the search has not yet found the file name.
 
    If IsEmpty(ActiveSheet.Cells(destRow, 1).Value) Then[INDENT]For Each mySubFolder In myFolder.SubFolders
        [/INDENT]
[INDENT=2]For Each myFile In mySubFolder.Files
        [/INDENT]
[INDENT=3]fileName = myFile.Name
        fullNameLen = Len(fileName)
        filePathFull = myFile.Path
        fullPathLen = Len(filePathFull)
        filePath = Left(filePathFull, fullPathLen - fullNameLen)

          If fileName = ActiveSheet.Cells(destRow, 2).Value Then
              [/INDENT]
[INDENT=4]       ActiveSheet.Cells(destRow + 1, 1).Value = filePath
       Exit For[/INDENT]
[INDENT=3]          End If[/INDENT]
[INDENT=2]Next[/INDENT]
' This next bit of code I got from somewhere else online, but I don't understand it.
' It seems to call the recurse function again with a new folder, being a subfolder, but
' I don't understand how this helps the whole function recurse through all files in all
' subfolders, or even whether it does.[INDENT]
Recurse = Recurse(mySubFolder.Path)[/INDENT]
[INDENT]Next[/INDENT]
   
    End If
   
    Next i
 
Application.ScreenUpdating = True
 
End Function
--
Sub runRecurse()
  Call Recurse("C:...path....\5.  Operations\Monitoring\")
End Sub

 

[John_w]

The code which loops through the file name cells should be in the main procedure, not the recursive function. This loop should call the recursive function with 2 arguments: the cell file name and the folder path from which the search should start.

The recursive function should loop through the folder passed to it, looking for the file with the same name as that passed to it. If not found, loop recursively through the folder's subfolders until the file is found or there are no more subfolders. The function should return the file path, if found, or "" indicating that the file wasn't found.

Hopefully that will give you a few pointers as to how to structure the code, but post back if you need more help.