Search for a file in folder and subfolders [UDF]
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.
A user defined function is a custom function that you can build yourself if none of Excel's built-in functions works for you.
Array formula in cell range B6:D7:
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
ListFiles(filename, path)
Arguments
filename | Required. The filename you are looking for. |
path | 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. |
VBA Macro
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 & "\" End If Recursive FileName, FolderPath k = Range(Application.Caller.Address).Rows.Count If k < UBound(temp, 2) Then Else For i = UBound(temp, 2) To k ReDim Preserve temp(UBound(temp, 1), i) temp(0, i) = "" temp(1, i) = "" temp(2, i) = "" Next i End If ListFiles = Application.Transpose(temp) ReDim temp(0) End Function
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 ReDim Folders(0) If Right(FolderPath, 2) = "\\" Then Exit Function Value = Dir(FolderPath, &H1F) Do Until Value = "" If Value = "." Or Value = ".." Then Else If GetAttr(FolderPath & Value) = 16 Then Folders(UBound(Folders)) = Value ReDim Preserve Folders(UBound(Folders) + 1) Else 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) End If End If End If Value = Dir Loop For Each Folder In Folders Recursive FileName, FolderPath & Folder & "\" Next Folder End Function
Where to put the code?
Follow these steps to insert the code into your workbook.
- Copy macro.
- Go to VB Editor (Alt+F11).
- Press with mouse on "Insert" on the top menu.
- Press with left mouse button on "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.
Search for a file in folder and subfolders using the FSO object
The following User defined Function, shown in the image above, allows you to search through folders and subfolders for a specified file name.
The first UDF above utilizes the DIR function that sometimes returns error 52 which translates to "Bad file name or number", the FSO object seems to handles this better.
VBA code
Public temp() As String 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 & "\" End If Recursive FileName, FolderPath k = Range(Application.Caller.Address).Rows.Count If k < UBound(temp, 2) Then Else For i = UBound(temp, 2) To k ReDim Preserve temp(UBound(temp, 1), i) temp(0, i) = "" temp(1, i) = "" temp(2, i) = "" Next i End If ListFiles = Application.Transpose(temp) ReDim temp(0) End Function
Function Recursive(FileName As String, FolderPath As String) Dim objFSO As Object Dim Value As String, Folders() As String Dim Folder As Variant, a As Long ReDim Folders(0) Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.GetFolder(FolderPath) If Right(FolderPath, 2) = "\\" Then Exit Function For Each objFile In objFolder.Files If objFile.Name = FileName Then temp(0, UBound(temp, 2)) = FolderPath temp(1, UBound(temp, 2)) = objFile.Name temp(2, UBound(temp, 2)) = objFile.Size ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1) End If Next objFile For Each objFolder In objFolder.Subfolders Folders(UBound(Folders)) = objFolder.Name ReDim Preserve Folders(UBound(Folders) + 1) Next objFolder For Each Folder In Folders On Error Resume Next Recursive FileName, FolderPath & Folder & "\" On Error GoTo 0 Next Folder End Function
Files and folders category
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
This article demonstrates macros that allow you to search for a text string(s) in multiple worksheets and workbooks located in […]
User defined function category
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
Excel categories
15 Responses to “Search for a file in folder and subfolders [UDF]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Thanks for this code.
I am not much of a VBA coder but I do pick up code and try to change to my requirements.
Can you help me with my query:
When my Folderpath is a system folder or a network folder - then I get #Value! all over. But if I change my folderpath to some sub-directory within, the code works.
For eg:
If my folderpath is:
C:\users\user\documents
It will not work
But if it is
C:\users\user\documents\Temp
The UDF works
Looking for your help!
Thanks!
Rasna,
Check the folder permissions, I believe that they may give you trouble.
Is there a way to work with all kinds of folders? I can read the folder; can add / delete items to the folder.. I just cannot delete the folder as it is a system folder. Should that make a difference? If yes, can we include it in our search criteria?
Thank you so much for your response! Really appreciate it.
Rasna
try this workbook:Check-if-file-exists-in-folder-or-subfolders-VBA-FSO.xlsm"
It seems to handle system folders as well.
Dear,
Your code is exactly what I need. But It doesn't work when I copy to other worksheets. I already active "trust center" to run VBA on my computer. the result return "#value!".
Also, your workbook doesn't allow to add or copy more line for functions. I am checking with more than 3 levels of sub folder.
Please help!
Thank you!
Dien
Code doesn't work in other worksheet,
You need to enter the user defined function as an array formula.
Here is how to enter an array formula using my example above.
1. Select cell range B6:D7 (Select a larger cell range if you need to).
2. Type =Listfiles(C3, B3)
3. Press and hold both CTRL + SHIFT simultaneously.
4. Press Enter.
5. Release all keys.
The are now curly brackets before and after the user defined function, don't enter these characters yourself. They appear when you successfully enter the array formula.
Like this:
{=Listfiles(C3, B3)}
Dear,
The code doesn't work on network drive (server). is there anyway to make it works?
Thanks
Anh
Code doesn't work in network drive,
Try the macro above that uses the FSO object or read my answer to Ransas question.
Hi Oscar,
Thank you for the code that finds files through system folders as well.. much much appreciated. It works for me.
Please do add it as another webpage within your site so that others can also find this useful code when they need it.
Thanks again!
Rasna
Rasna,
Thank you, I have added it to this article.
I had a user lose (moved or deleted) a subfolder on a network. Can this code be modified to search for a particular subfolder that has been misplaced?
I recommend Windows Explorer to find a particular subfolder that has been misplaced. You don't need Excel to do that.
Thanks Oscar for the great code! Really helps.
Is there a chance to somehow search for e.g. all ".docx" files? Meaning not searching for an exact name but a file type.
Thanks a lot for your help!!
Is it possible to search for files using a wildcard or a string. I want to search for file starting with a certain string like test*
Your code is great, but it doesn't work in server correct? For example if open one in a computer where the unit has the letter C:\\ and I try later with another computer with the letter K:\\ it will no work, is it correct? Do you know what I should make to fix it?
Thank you so much for you help.