The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders for a file name. It returns the exact path to the file, file name, and the file size.
To enter an array formula, select cell range B6:D7. Type the formula and then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
User Defined Function Syntax
Required. The filename you are looking for.
Required. The full path to the folder you want the UDF to begin searching in. The function will also search in all subfolders of this folder.
There are actually two user-defined functions in order to search all subfolders and a variable that needs to be located at the very top of your workbook's code module.
Public temp() As String
Here is the first UDF, it makes sure that the result doesn't contain any error values.
Function ListFiles(FileName As String, FolderPath As String)
Dim k As Long, i As Long
ReDim temp(2, 0)
If Right(FolderPath, 1) <> "\" Then
FolderPath = FolderPath & "\"
Recursive FileName, FolderPath
k = Range(Application.Caller.Address).Rows.Count
If k < UBound(temp, 2) Then
For i = UBound(temp, 2) To k
ReDim Preserve temp(UBound(temp, 1), i)
temp(0, i) = ""
temp(1, i) = ""
temp(2, i) = ""
ListFiles = Application.Transpose(temp)
The second UDF is recursive meaning that a new instance of this UDF is created for each subfolder.
Function Recursive(FileName As String, FolderPath As String)
Dim Value As String, Folders() As String
Dim Folder As Variant, a As Long
If Right(FolderPath, 2) = "\\" Then Exit Function
Value = Dir(FolderPath, &H1F)
Do Until Value = ""
If Value = "." Or Value = ".." Then
If GetAttr(FolderPath & Value) = 16 Then
Folders(UBound(Folders)) = Value
ReDim Preserve Folders(UBound(Folders) + 1)
If Value = FileName Then
temp(0, UBound(temp, 2)) = FolderPath
temp(1, UBound(temp, 2)) = Value
temp(2, UBound(temp, 2)) = FileLen(FolderPath & Value)
ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
Value = Dir
For Each Folder In Folders
Recursive FileName, FolderPath & Folder & "\"
Where to put the code?
Follow these steps to insert the code into your workbook.
Go to VB Editor (Alt+F11).
Click on "Insert" on the top menu.
Click "Module" to insert a module into the workbook.
Paste macro to the code module.
Exit VB Editor.
Save the workbook as a macro-enabled workbook (*.xlsm).
If you don't the macro will be gone the next time you open the workbook.