Search for a file in folder and subfolders [UDF]
Table of Contents
1. 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.
1.1 User Defined Function Syntax
ListFiles(filename, path)
1.2 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. |
1.3 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
1.4 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.
2. 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
3. List files in a folder and sub folders - UDF
This section of the article demonstrates a user defined function that lists files in a given folder and sub folders. A user defined function is a custom function in Excel that anyone can build, simply copy the VBA code and paste to your workbook's code module and you are good to go.
Array formula in cell range B3:D9:
To enter an array formula, select the cell range, type the formula, 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.
3.1 User defined Function Syntax
ListFiles(FolderPath)
3.2 Arguments
Parameter | Text |
FolderPath | Required. The path to the folder you want to use. |
3.3 Where to copy vba code?
- Press Alt-F11 to open visual basic editor
- Press with left mouse button on Module on the Insert menu
- Copy and paste vba code, both functions below.
- Exit visual basic editor
'Declare a public variable meaning it will exist for both functions below. Public temp() As String 'Name function and declare parameters Function ListFiles(FolderPath As String) 'Declare arguments and data types Dim k As Long, i As Long 'Redimension array variable temp so it can contain more values if needed. ReDim temp(2, 0) 'Add and ending backslash to pathe if missing If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath & "\" End If 'Start second function named Recursive with folder path Recursive FolderPath 'Count cells the UDF is using k = Range(Application.Caller.Address).Rows.Count 'Return a message if cell range needs to be larger in order to show all values 'or fill array values with blanks to avoid error values when no more values is to be shown If k < UBound(temp, 2) Then MsgBox "There are more rows, extend user defined function" 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 'Return values ListFiles = Application.Transpose(temp) ReDim temp(0) End Function
'Name function and declare parameter Function Recursive(FolderPath As String) 'Declare variables and data types Dim Value As String, Folders() As String Dim Folder As Variant, a As Long 'Redimension array variable Folders so it can contain more folder paths if needed. ReDim Folders(0) 'If folder path ends with \\ then end function If Right(FolderPath, 2) = "\\" Then Exit Function 'Open folder path and populate Value with file names/folders Value = Dir(FolderPath, &H1F) 'Repeat code until all file names have been retrieved Do Until Value = "" 'Ignore file name . and .. If Value = "." Or Value = ".." Then Else 'If value is a folder then save value to variable Folders If GetAttr(FolderPath & Value) = 16 Then Folders(UBound(Folders)) = Value ReDim Preserve Folders(UBound(Folders) + 1) Else 'If value is a file name then save path, name and size to variable temp 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 'Continue with next value in folder Value = Dir Loop 'Start a new instance of function Recursive for each new folder For Each Folder In Folders Recursive FolderPath & Folder & "\" Next Folder End Function
4. Unzip files in folder and sub folders
The VBA macro demonstrated in this article lets you unzip all zip files in a folder to a destination folder you specify. It continues with subfolders until all zip files have been unzipped.
4.1. VBA Code
Actually, there are two macros. Run macro named UnzipFiles to start unzipping files.
'Name macro Sub UnzipFiles() 'Dimension variables and declare data types Dim myfolder As Variant Dim destfolder As Variant 'Ask for a source folder With Application.FileDialog(msoFileDialogFolderPicker) .Show 'Save selected folder path to variable myfolder myfolder = .SelectedItems(1) & "\" End With 'Ask for a destination folder With Application.FileDialog(msoFileDialogFolderPicker) .Show destfolder = .SelectedItems(1) & "\" End With 'Run macro named Recursive with parameters myfolder and destfolder Call Recursive(myfolder, destfolder) End Sub
'Name macro and dimension parameters Sub Recursive(FolderPath As Variant, destfolder As Variant) 'Dimension variables and declare data types Dim Value As String, Folders() As String Dim Folder As Variant, a As Long Dim SApp As Object 'Redimension variable Folders ReDim Folders(0) 'If...Then statement 'Check if last two characters are // and stop macro if true If Right(FolderPath, 2) = "\\" Then Exit Sub 'Dir function returns a String representing the name of a file, directory, or folder, save string to variable Value Value = Dir(FolderPath, &H1F) 'Do Until ... Loop statement Keep iterating lines between Do Until and Loop Do Until Value = "" 'If ... Then ... Else ... Endif statement 'Check if variable Value is equal to . or .. If Value = "." Or Value = ".." Then 'Continue here if variable Value is not equal to . or .. Else 'If ... Then ... Else ... Endif statement 'Check if attribute for file is equal to 16 or 48 indicating it is a folder If GetAttr(FolderPath & Value) = 16 Or GetAttr(FolderPath & Value) = 48 Then 'Save Folder name to variable Folders Folders(UBound(Folders)) = Value 'Add another container to variable Folders, in other words, increase size of array variable Folders by 1 ReDim Preserve Folders(UBound(Folders) + 1) 'Continue here if attribute for file is equal to 16 or 48 indicating it is a folder Else 'Check if last four characters in filename equals .zip If Right(Value, 4) = ".zip" Then 'Unzip file to destination folder Set SApp = CreateObject("Shell.Application") SApp.Namespace(destfolder).CopyHere _ SApp.Namespace(FolderPath & Value).items End If End If End If Value = Dir Loop 'Start macro Recursive for each folder in variable Folders For Each Folder In Folders Call Recursive(FolderPath & Folder & "\", destfolder) Next Folder End Sub
4.2. Where to put the code?
- Press Alt + F11 to open the Visual Basic Editor (VBE).
- Press with left mouse button on "Insert" on the top menu.
- Press with left mouse button on "Module" to create a new module.
- Paste VBA code to code window.
- Exit VBE and return to Excel.
Note, save your workbook with file extension *.xlsm (macro-enabled workbook).
-
- Press with left mouse button on "File" on the ribbon.
- Press with left mouse button on "Save as...".
- Press with mouse on the drop-down list below the file name.
- Select file extension *.xlsm
- Press with left mouse button on "Save".
4.3. How to run the macro?
- Press Alt + F8 to open the Macro" dialog box.
- Press with left mouse button on the macro name to select it.
- Press with left mouse button on "Run" button.
4.4. What happens when you run the macro?
- Select a folder you want to search.
- Select a destination folder. This is where all unzipped files will be copied to.
- Macro unzips all zip files in folder and subfolders.
- Macro ends.
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 […]
Table of Contents Split values equally into groups Rearrange values based on category - VBA 1. Split values equally […]
Excel categories
33 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.
Contact Oscar
You can contact me through this contact form
[...] List files in a folder and subfolders [...]
Oscar,
What check in the code above, are doing here
Value = ".."
Also,
Using the VBA Help, I notice there is nothing list as &H1F for the Dir Function. Can you explain that part in your code?
Dir(FolderPath, &H1F)
Thanks for your tutorials... Great job as usual.
chrisham,
What check in the code above, are doing here
Value = ".."
I think you are talking about this line?
List a directory in command prompt and you will find that the first two lines are named . and ..
I think they are reference to the current folder and the parent folder.
Using the VBA Help, I notice there is nothing list as &H1F for the Dir Function. Can you explain that part in your code?
Dir(FolderPath, &H1F)
I don´t think you need it:
https://www.techonthenet.com/excel/formulas/dir.php
Thanks Oscar... Got it!
Hi There,
I get the message: "There are more rows, extend user defined function"
Is there anyways can I skip that? Because I want to see the whole of the L drive.
Many Thanks,
Ross
Ross,
Yes, delete this line from function ListFiles():
Hi,
I got the #VALUE! error. Any ideas why? I'm using Excel 2010 and changed my settings per this link:
https://production-scheduling.com/run-macros-excel-2007-2010/
Thanks.
ACL,
did you enter the custom function as an array formula?
Oscar, what do you mean enter the function as an array formula? How would I do that?
Nevermind, I found my answer here: https://excelexperts.com/vba-tips-list-files-in-a-folder
I suppose this is what the hex-number is used for.
Syntax for dir is dir[(path [, attributes])]
Attribute &H1F is as binary: 011111, which means that attributes with values 1, 2, 4, 8, 16 shall be listed.
Use dir(path,&H10) only folders will be listed. &H14 will list folders and system files.
Zeth,
Thank you, I did not know.
Hi. Can you please guide me how to edit this code so that I want to select only directories with a specific pattern at each level and only .edf files.
Thanks in advance.
Sobia
hello,
first af all I want to thank you to share this Code with us.it work fine but I want to make somthing additional. I would like to add some informations (in new columns) to each File/Filename. Is that possible? Is there any uniq identifier of the files readable with with VBA?
Thank You in advance for your Reply.
Zoltan
I would like to add some informations (in new columns) to each File/Filename. Is that possible?
Yes, make temp array variable larger. What kind of information?
Is there any uniq identifier of the files readable with with VBA?
Not that I am aware of, Windows won't allow you to have two identical file names in the same folder. The folder path together with file name makes each file unique.
Dear Sir
I have a zip file in D:\ , which I want to unzip so used the code to unzip the file(file is csv format).
The macro searches the folder and shows no files to unzip.
However there are two files to unzip which the macro is unable to detect
Please help.
Thank you very much for sharing your codes.
Hello Sir
I thank you to give us such nice information. actually I'm searching for a excel macro that will create a list of files in a given folder (Very similar as above), only thing when I run same macro for next time it will first compare the old list of file names in excel range with new one of given folder & then it will give remark at end of each row like,
if file not found then "File is deleted or moved"
If new file found then "New file added"
If same file found then "No change" etc....
Thank You in advance for your Reply.
Avinash...
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.