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.
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:
5. And in the Browse window, please select the folder that you want to list all filenames including the subfolders, see screenshot:
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:
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.|
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 button 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.
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: