Note: The other languages of the website are Google-translated. Back to English
English English▼
X
en Englishar Arabichy Armenianzh-CN Chinese (Simplified)zh-TW Chinese (Traditional)cs Czechda Danishnl Dutchfr Frenchde Germanel Greekhu Hungarianid Indonesianga Irishit Italianja Japaneseko Koreanpl Polishpt Portuguesero Romanianru Russiansl Slovenianes Spanishsv Swedishth Thaitr Turkishuk Ukrainianvi Vietnamesecy Welsh

How to list all files in folder and subfolders into a worksheet?

Have you ever tried to list all filenames from a folder into a worksheet including the files located within its subfolders? In fact, there is no direct way for us to list the filenames from a folder and its subfolder in Excel, however, today, I will introduce some quick tricks to solve this job.

List all filenames in folder and subfolder with VBA code

List all filenames in folder and subfolder quickly and easily with Kutools for Excel

Normally, Excel has no build in feature to deal with this task, but, you can apply the following VBA code to complete this problem.

1. Activate a new worksheet which will list the filenames.

2. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA code: List all filenames in folder and subfolder

Sub MainList()
'Updateby Extendoffice
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
If folder.Show <> -1 Then Exit Sub
xDir = folder.SelectedItems(1)
Call ListFilesInFolder(xDir, True)
End Sub
Sub ListFilesInFolder(ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)
Dim xFileSystemObject As Object
Dim xFolder As Object
Dim xSubFolder As Object
Dim xFile As Object
Dim rowIndex As Long
Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.GetFolder(xFolderName)
rowIndex = Application.ActiveSheet.Range("A65536").End(xlUp).Row + 1
For Each xFile In xFolder.Files
  Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
  rowIndex = rowIndex + 1
Next xFile
If xIsSubfolders Then
  For Each xSubFolder In xFolder.SubFolders
    ListFilesInFolder xSubFolder.Path, True
  Next xSubFolder
End If
Set xFile = Nothing
Set xFolder = Nothing
Set xFileSystemObject = Nothing
End Sub
Function GetFileOwner(ByVal xPath As String, ByVal xName As String)
Dim xFolder As Object
Dim xFolderItem As Object
Dim xShell As Object
xName = StrConv(xName, vbUnicode)
xPath = StrConv(xPath, vbUnicode)
Set xShell = CreateObject("Shell.Application")
Set xFolder = xShell.Namespace(StrConv(xPath, vbFromUnicode))
If Not xFolder Is Nothing Then
  Set xFolderItem = xFolder.ParseName(StrConv(xName, vbFromUnicode))
End If
If Not xFolderItem Is Nothing Then
  GetFileOwner = xFolder.GetDetailsOf(xFolderItem, 8)
Else
  GetFileOwner = ""
End If
Set xShell = Nothing
Set xFolder = Nothing
Set xFolderItem = Nothing
End Function

4. After pasting the code into the Module, press F5 key to run this code, and a Macros dialog box pops up, select the MainList macro name, and then click Run button, see screenshot:

doc list files in folder subfolder 1

5. And in the Browse window, please select the folder that you want to list all filenames including the subfolders, see screenshot:

doc list files in folder subfolder 2

6. After specifying the folder, then click OK button, and all the filenames in the folder and its subfolders have been listed into current worksheet from cell A2, see screenshots:

doc list files in folder subfolder 3
1
doc list files in folder subfolder 4

With above code, you can just list the filenames, sometimes, you need to list other attributes, such as file size, file type, created time, containing folder and so. Kutools for Excel contains a useful function – Filename List, with this feature, you can quickly list all or specific types of filenames in a folder and its subfolders.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.
Go to Download
Free Trial 30 daysPurchase
PayPal / MyCommerce

After installing Kutools for Excel, please do with following steps:

1. Click Enterprise > Import & Export > Filename List, see screenshot:

2. In the Filename List dialog box, do the following operations:

A: Click doc list files in folder subfolder 7button to choose the folder that you want to list the filenames;

B: Specify the file type that you want to list from the Files type section;

C: Select one file size unit you want to display from the File size unit section.

Note: To list the filenames from the subfolder, please check Include files in subdirectories, you can also check the Include hidden files and folders as you need. If you check Create hyperlinks option, it will create hyperlinks for each filenames and folders.

Download and free trial Now !

3. Then click OK button, all of the files contained in the selected folder and its subfolders have been displayed with following attributes in a new worksheet. See screenshot:

doc list files in folder subfolder 8

Click to know more details about this Filename List utility.

Download and free trial Kutools for Excel Now !

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (20)
No ratings yet. Be the first to rate!
Adrian Badea
Adrian Badea
This comment was minimized by the moderator on the site
Good article Thanks :D
Reply
0
0
Anilkumar
Anilkumar
This comment was minimized by the moderator on the site
dear, Thanks for this wonderful utility.....!!! anilkumar
Reply
0
0
SATISH
SATISH
This comment was minimized by the moderator on the site
HOWTO MAKE THIS QUERY WORK TO GET THE FILENAMES LISTED FROM B2 cell instead of A2 ?
Reply
0
0
skyyang
skyyang     SATISH
This comment was minimized by the moderator on the site
Hi, Satish,
The following code can help you to put the filenames to any cell you selected, please try it:

Sub MainList()
On Error Resume Next
Set xRg = Application.InputBox("Please select a cell to put the filenames：", "KuTools For Excel", Selection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xRg = xRg(1)
Set Folder = Application.FileDialog(msoFileDialogFolderPicker)
If Folder.Show <> -1 Then Exit Sub
xDir = Folder.SelectedItems(1)
Call ListFilesInFolder(xRg, xDir, True)
End Sub
Sub ListFilesInFolder(ByVal xRg As Range, ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)
Dim xFileSystemObject As Object
Dim xFolder As Object
Dim xSubFolder As Object
Dim xFile As Object
Dim rowIndex As Long
Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.GetFolder(xFolderName)
rowIndex = 1
For Each xFile In xFolder.Files
xRg.Formula = xFile.Name
Set xRg = xRg.Offset(rowIndex)
rowIndex = 1
Next xFile
If xIsSubfolders Then
For Each xSubFolder In xFolder.SubFolders
ListFilesInFolder xRg, xSubFolder.Path, True
Next xSubFolder
End If
Set xFile = Nothing
Set xFolder = Nothing
Set xFileSystemObject = Nothing
End Sub
Reply
Report
0
0
Nir
Nir
This comment was minimized by the moderator on the site
great!!! love it, thanks
Reply
0
0
Ankit
Ankit
This comment was minimized by the moderator on the site
Not sure.. but does it consider long folders. i wrote a similar code and it fails where the folder/file path exceeds 256 characters ? Thoughts please ?
Reply
0
0
Ana
Ana
This comment was minimized by the moderator on the site
Thank you so much for the useful article!
How can I automatically update the list and spot the changes?
Reply
0
0
David
David
This comment was minimized by the moderator on the site
This helped me to convert my code to get the recursive call, but I wonder about the way you use objects. I prefer to define my objects as what they are, be they workbooks, worksheets or file system objects. (dim wbDest as excel.workbook, dim wsDest as excel.worksheet for example. This way I never have to worry about what the active sheet is.

I also wondered about the use of .show - if.show <>-1, and can't help feeling that it would be easier for people to understand if instead you used if.show = false.

And in terms of writing to the destination, I felt that using cells and formula seemed to be a long way round

So in my code I have, for example,
wsDest.Range("B" & CurRow) = fil.name
(CurRow = Current Row)

I'm not criticising, just wondering if you do it like this for any reason other than personal preference. - Always willing to learn


But apart from that thanks for the help
Reply
0
0
Brett
Brett
This comment was minimized by the moderator on the site
Thankyou for this code it has helped me with a request a work, I am now able to import these results into Access for further transformation.Appreciate it.
Reply
0
0
jumpjack
jumpjack
This comment was minimized by the moderator on the site
Sub "GetFileOwner()" in code above is not used.
Reply
0
0
skyyang
skyyang     jumpjack
This comment was minimized by the moderator on the site
Hello, jumpjack,
The above code works well in my Excel, which Excel version do you use?
Reply
Report
0
0
Leandro Barbosa
Leandro Barbosa
This comment was minimized by the moderator on the site
Extremamente elegante este código!
Reply
0
0
Chris K
Chris K
This comment was minimized by the moderator on the site
What was the purpose of the parameter ByVal xIsSubfolders As Boolean?
Reply
0
0
James
James
This comment was minimized by the moderator on the site
My VBA is saying that the variable folder is not defined. Anyone know why this is?
Reply
0
0
JDay
JDay
This comment was minimized by the moderator on the site
Really new to VBA. how do i use the above code but have the file path built into it so i don't have to search for it every time?
Reply
0
0
Prasanna Venkatesh
Prasanna Venkatesh
This comment was minimized by the moderator on the site
Hey, so if I have to just extract an extension from the whole list, where should I make changes?
Reply
0
0
Peter
Peter
This comment was minimized by the moderator on the site
Does it work on MAC too?
Reply
0
0
twmcc2
twmcc2
This comment was minimized by the moderator on the site
I have modified your code to make it recycle and re-run the folder dialog continuously until you press Cancel.Unfornatually it generates some errors.1. If subfolders become involved in a folder selected then the next folder selected becomes out of numerical order.2. If subfolders become involved in a folder selected the Cancel button has to be repeated and repeated depending on how many folders you have added.
Code:
Sub MainList()
'Updateby Extendoffice
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
If folder.Show <> -1 Then Exit Sub
xDir = folder.SelectedItems(1)
Call ListFilesInFolder(xDir, True)
End SubSub ListFilesInFolder(ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)
Dim xFileSystemObject As Object
Dim xFolder As Object
Dim xSubFolder As Object
Dim xFile As Object
Dim rowIndex As Long
Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.GetFolder(xFolderName)
rowIndex = Application.ActiveSheet.Range("A65536").End(xlUp).Row + 1
For Each xFile In xFolder.Files
Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
rowIndex = rowIndex + 1
Next xFile
If xIsSubfolders Then
For Each xSubFolder In xFolder.SubFolders
ListFilesInFolder xSubFolder.Path, True
Next xSubFolder
End If
Set xFile = Nothing
Set xFolder = Nothing
Set xFileSystemObject = Nothing
End Sub

any ideas ?
Reply
Report
0
0
twmcc2
twmcc2
This comment was minimized by the moderator on the site
Sorry... I gave you the wrong code (below), here is the code I modified..
Code:
Sub MainList()
On Error Resume Next
Dim xFileSystemObject As Object
Dim xFolder As Object
Dim xSubFolder As Object
Dim xFile As Object
Dim rowIndex As Long
Dim answer As Variant
answer = False
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a Folder"
.AllowMultiSelect = False
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
If folder.Show = -1 Then
xDir = folder.SelectedItems(1)
Else
Exit Sub
End If
End With

Call ListFilesInFolder(xDir, True)
End Sub

Sub ListFilesInFolder(ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)
Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.GetFolder(xFolderName)
rowIndex = Application.ActiveSheet.Range("A65536").End(xlUp).Row + 1

' Add a space then the Folder Name to the Worksheet
rowIndex = rowIndex + 1
With Application.ActiveSheet.Cells(rowIndex, 1)
.Value = xFolder.Name
.Font.Size = 12
.Font.FontStyle = "Bold Italic"
End With
rowIndex = rowIndex + 1

For Each xFile In xFolder.Files
Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
rowIndex = rowIndex + 1
Next xFile
If xIsSubfolders Then
For Each xSubFolder In xFolder.SubFolders
ListFilesInFolder xSubFolder.Path, True
Next xSubFolder
rowIndex = rowIndex + 1
End If

Set xFile = Nothing
Set xFolder = Nothing
Set xFileSystemObject = Nothing

Call MainList
End Sub


Reply
Report
0
0
d2b
d2b
This comment was minimized by the moderator on the site
Nice work, exactly what I was trying to create. But this is 1000% better.
Reply
0
0
There are no comments posted here yet
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations