The below UDF will list all files in all subfolders returning a one dimensional array that match the pattern provided in the argument filePattern. The pattern can include ? (question mark) that identify one repetition of any character and * that identify one or more characters.
The first argument, folderPath, is the folder where the UDF will start looking for files matching the pattern, and then it will look in all subfolders. This folder path must not end with a path separator, e.g. it must be C:\Users\Desktop and not C:\Users\Desktop\
The function will return a one dimensional array with Ubound = -1 if no matches are found
Function GET_FILES_IN_SUBFOLDERS(ByVal folderpath As String, _
ByVal filePattern As String) As Variant
GET_FILES_IN_SUBFOLDERS = _
Split(CreateObject(“WScript.Shell”).EXEC(“WHERE /R ” & Chr(34) & folderpath & Chr(34) & ” ” & Chr(34) & filePattern & Chr(34)).StdOut.ReadAll, vbNewLine)
Calling the UDF
The below example will list all the Excel files that are found in C:\Users\Me\Desktop and in its subfolders that contain the word chart:
Const filePattern = “*chart*.xlsx”
Const folderpath = “C:\Users\Me\Desktop”
Dim vFiles As Variant
vFiles = GET_FILES_IN_SUBFOLDERS(folderpath, filePattern)
I hope you will find this UDF useful. Most of the times it does the trick for me and it avoids me to perform loops which may take much longer to complete.