Livio/ May 3, 2018/ Excel, Excel VBA/ 0 comments

User-defined function

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)

End Function

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:

Sub GetFiles()
Const filePattern = “*chart*.xlsx”
Const folderpath = “C:\Users\Me\Desktop”
Dim vFiles As Variant

vFiles = GET_FILES_IN_SUBFOLDERS(folderpath, filePattern)

End Sub

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.

me