List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. 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.
User defined Function Syntax
ListFiles(FolderPath)
Arguments
| Parameter | Text |
| FolderPath | Required. The path to the folder you want to use. |
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
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 […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Excel categories
16 Responses to “List files in a 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.
[...] 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.
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...