3 Quick Methods to Recursively List All Files in a Windows Folder in Excel Worksheet

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

1. First off, in Windows Explorer, access the specific Windows folder and copy the folder path.
2. Then, launch Firefox or Google Chrome.
3. Next, paste the copied folder path in the address bar of the Internet Browser.
4. After that, press “Enter” key.
5. At once, all the files in this Windows folder will be listed in the browser.
6. Then, press “Ctrl + A” to select all contents and “”Ctrl + C”” to copy them.
7. Lastly, paste them to an Excel worksheet.

Method 2: List Files in a Windows Folder via “Name Manager”

1. At first, create a new Excel worksheet.
2. Then, select Cell A1.
3. Next, go to “Formulas” tab and click “Name Manager” button.
4. In the popup dialog box, click “New” button.
5. Subsequently, in the next dialog box, input “Files” in “Name” field.
6. And change “=Sheet1!$A$1” to “=FILES(Sheet1!$A$1)” in “Refers to:” field.
7. Afterwards, click “OK” and close “Name Manager” dialog box.
8. Later, copy the Windows folder path in Cell A1 and add “\*” at the end of the path.
9. Next, select the Cell A3 and input the function “=INDEX(Files,ROW()-2)”.
10. At once, a file in this Windows folder will be listed, like the following image.
11. 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

1. For a start, create a new Excel file.
2. Next, access Excel VBA editor with reference to “How to Run VBA Code in Your Excel“.
3. 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

4. Now, click into the first subroutine and press “F5” key.
5. In the popup dialog box, navigate to and select a Windows folder which you want to list files from and click “OK”.
6. 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.

Author Introduction:

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