Many users need to quickly list all files in a Windows folder and all of its subfolders in an Excel worksheet. Therefore, in this article, we will introduce you 3 rapid ways to realize it.
If you want to recursively list all files in a Windows folder in an Excel worksheet, you’ll surely dislike copying the file names to Excel manually. Undoubtedly, it is too tedious and time-consuming. Hence, here we will teach you 3 much smarter and more convenient approaches
Method 1: List Files in a Windows Folder via Firefox or Google Chrome
- First off, in Windows Explorer, access the specific Windows folder and copy the folder path.
- Then, launch Firefox or Google Chrome.
- Next, paste the copied folder path in the address bar of the Internet Browser.
- After that, press “Enter” key.
- At once, all the files in this Windows folder will be listed in the browser.
- Then, press “Ctrl + A” to select all contents and “”Ctrl + C”” to copy them.
- Lastly, paste them to an Excel worksheet.
Method 2: List Files in a Windows Folder via “Name Manager”
- At first, create a new Excel worksheet.
- Then, select Cell A1.
- Next, go to “Formulas” tab and click “Name Manager” button.
- In the popup dialog box, click “New” button.
- Subsequently, in the next dialog box, input “Files” in “Name” field.
- And change “=Sheet1!$A$1” to “=FILES(Sheet1!$A$1)” in “Refers to:” field.
- Afterwards, click “OK” and close “Name Manager” dialog box.
- Later, copy the Windows folder path in Cell A1 and add “\*” at the end of the path.
- Next, select the Cell A3 and input the function “=INDEX(Files,ROW()-2)”.
- At once, a file in this Windows folder will be listed, like the following image.
- Finally, just copy this function down several rows to list the other files until you see the “#REF!” error. The error means that all files have been listed.
Method 3: Recursively List Files in a Windows Folder via Excel VBA
- For a start, create a new Excel file.
- Next, access Excel VBA editor with reference to “How to Run VBA Code in Your Excel“.
- Then, copy the following code into “ThisWorkbook” project.
Sub BatchListAllFiles_FolderSubfolders() Dim strWindowsFolder As String 'Select the source Windows folder With Application.FileDialog(msoFileDialogFolderPicker) .InitialFileName = Application.DefaultFilePath & "\" .Title = "Please select a folder to list Files from" .InitialFileName = "E:\" .Show If .SelectedItems.Count > 0 Then strWindowsFolder = .SelectedItems(1) & "\" End If End With With ActiveSheet .Cells(1, 1) = "Name" .Cells(1, 1).Font.Bold = True .Cells(1, 2) = "Path" .Cells(1, 2).Font.Bold = True .Cells(1, 3) = "Size(Bytes)" .Cells(1, 3).Font.Bold = True .Cells(1, 4) = "Type" .Cells(1, 4).Font.Bold = True .Cells(1, 5) = "Created" .Cells(1, 5).Font.Bold = True End With Call LoopFolders(strWindowsFolder) End Sub Sub LoopFolders(strFolderPath As String) Dim objFileSystem As Object Dim objFolder As Object Dim objFile As Object Dim nLastRow As Integer Set objFileSystem = CreateObject("Scripting.FileSystemObject") Set objFolder = objFileSystem.GetFolder(strFolderPath) For Each objFile In objFolder.Files With ActiveSheet nLastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1 .Range("A" & nLastRow) = objFile.Name .Range("B" & nLastRow) = objFile.Path .Range("C" & nLastRow) = objFile.Size .Range("D" & nLastRow) = objFile.Type .Range("E" & nLastRow) = objFile.DateCreated .Columns("A:E").AutoFit End With Next 'Process all folders and subfolders recursively If objFolder.SubFolders.Count > 0 Then For Each objSubFolder In objFolder.SubFolders 'Skip the system and hidden folders If ((objSubFolder.Attributes And 2) = 0) And ((objSubFolder.Attributes And 4) = 0) Then LoopFolders (objSubFolder.Path) End If Next End If End Sub
- Now, click into the first subroutine and press “F5” key.
- In the popup dialog box, navigate to and select a Windows folder which you want to list files from and click “OK”.
- Eventually, when macro finishes, return to the Excel worksheet. All the files in the Windows folder and its subfolders have been listed, as shown in the following screenshot.
Excel Crashes from Time to Time
Like other software, Excel can crash now and then, which can directly destroy the currently opened Excel file. In such cases, a majority of users will hope that Excel can auto recover the file on restart. However, more often than, the reality is not as what you expect. Then, it would be essential to resort to an Excel repair tool, like DataNumen Excel Repair.
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover mdf and outlook repair software products. For more information visit www.datanumen.com