Macro to cycle through column A and output in column B. Need small correction to finish it.

[mpc000]

I have a list of strings in column, and each string is a filename.
I'm trying to output the file path on column B.

I have tweaked a macro I found that will cycle through a given main folder and, if the string in column A matches a filename, it will output the path in column B.

However, I can't get it to cycle through the whole list of strings in column A, but only the first string. Can you help me modify it so that it loops throughout the whole column (number of rows will vary, no blank cells).

Function Recurse(sPath As String) As String

    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim mySubFolder As Folder
    Dim myFile As File

    Set myFolder = FSO.GetFolder(sPath)
    
    For Each mySubFolder In myFolder.SubFolders
        For Each myFile In mySubFolder.Files
            If myFile.Name = Range("A2").Value Then          'This is where it matches the filename with the string, starting from A2
                Range("B2").Value = myFile.Path                     'if it finds a match it outputs the full path + filename in column B, starting from B2
                Exit For
            End If
        Next
        Recurse = Recurse(mySubFolder.Path)
    Next

End Function

Sub TestR()

Dim x As Integer
ActiveSheet.Select
    
    NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count    
    For x = 1 To NumRows                                                                          'I played around with this trying to get it to loop through the column A rows but it doesn't work.

    Call Recurse("H:\test")
Next

End Sub

P.S I posted a couple days ago looking for a similar solution trying to tweak another macro, but that went nowhere. This one is more promising as it works for one cell, just not for all.

 

[mpc000]

Apologies, I couldn't figure out how to edit my original post. I have figured out the solution!!

see below:


Is there any way I can make it to ask me to select a folder, instead of declaring the main folder in the script? Also, would it be possible to select numerous folders (e.g. across different hard drives)?

Function Recurse(sPath As String) As String

    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim mySubFolder As Folder
    Dim myFile As File
    
    
    
    
    Set myFolder = FSO.GetFolder(sPath)
    
    ' Set numrows = number of rows of data.
    
    
    For Each mySubFolder In myFolder.SubFolders
        For Each myFile In mySubFolder.Files
            If myFile.Name = ActiveCell.Offset(columnOffset:=-1).Value Then
                ActiveCell.Value = myFile.Path                'Or do whatever you want with the file
                Exit For
            End If
        Next
        Recurse = Recurse(mySubFolder.Path)
    Next

End Function

Sub TestR()

Dim x As Integer
ActiveSheet.Select
    
    NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
    For x = 2 To NumRows + 1
    ActiveCell = Range("B" & x).Activate
    
    Call Recurse("H:\test")
Next

End Sub

 

[mpc000]

looks like I'm on a roll today. I have tweaked the Sub part and it now allows me to select a folder:

The only thing I still need are the ability to select different folders in different drives at the same time, and
whenever it does not find a match it returns 'TRUE', I would like to return 'File not found' instead.

Solution:

Sub SelectFolder()
Dim sFolder As String
Dim x As Integer
    
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
    
    If sFolder <> "" Then ' if a file was chosen

ActiveSheet.Select
    
    NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
    For x = 2 To NumRows + 1
    ActiveCell = Range("B" & x).Activate
    
    Call Recurse(sFolder) '("H:\test")
Next

    End If
End Sub