How to list all file names from a folder and sub-folders into a worksheet?

If you need to generate a list of file names from a directory into an Excel worksheet, the following methods will help you do this quickly and efficiently. Choose the approach that best suits your needs.

• List all file names from a folder into worksheet by using a web browser
• List all file names from a folder into worksheet by using formula
• List all file names from a folder into worksheet by using VBA code
• List all file names from a folder and sub-folders into a worksheet by uisng Kutools

List all file names from a folder into worksheet by using a web browser

If you want to list files in a folder by using the web browser, you should make sure you have one of the web browsers (Firefox, Opera and Google Chrome) installed in your computer. Supposing you need to list files of following folder in worksheet, see screenshot: 

1. Go to copy the path of the folder ("Folder Test") in Explorer. For example, the path of this folder is: "C:\Users\AddinTestWin10\Desktop\Folder Test".

2. Open one of the web browsers (FireFox, Opera and Google Chrome) and paste the folder path in the address bar and press Enter key. See screenshot:

3. Click "Ctrl+A" to select all contents in the web browser and press "Ctrl+C" to copy them.

4. Open Excel and directly paste (using "Ctrl+V" shortcuts to paste) them in a worksheet. See screenshot:

List all file names from a folder into worksheet by using formula

In Excel, you can also use a formula to get the list of all filenames or specific types of filenames from a folder, please do with following steps:

1. Copy and paste the file path into a cell, and then type  \* after the file path as below screenshot shown:

2. Then, click "Formula" > "Name Manager", see screenshot:

3. In the "Name Manager" dialog box, click "New" button, see screenshot:

4. In the popped out "New Name" dialog box, specify a name into the "Name" text box, and then enter the below formula into the "Refers to" text box, see screenshot:

Note: In this formula, "Sheet1!$A$1" is the cell of the worksheet contains the file path you instered in step 1.

5. And then, click "OK" > "Close" to close the dialogs, and then enter the following formula into a blank cell where you want to list the file names, and drag the fill hanlde down untill blank cells are displayed, now, all file names in the specified folder have been listed as below screenshot shown:

Note: In this formula, "Filenames" is the range name you are created in step 4, and "A1" is the cell contains the file path.

List all file names from a folder into worksheet by using VBA code

Using the following VBA to list files in a folder in a worksheet:

1. Open a worksheet, and click to select a cell where you want to put 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 file names from a folder into a worksheet

Sub listfiles()
'Updateby Extendoffice
    Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xFiDialog As FileDialog
    Dim xPath As String
    Dim I As Integer
    Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
    If xFiDialog.Show = -1 Then
        xPath = xFiDialog.SelectedItems(1)
    End If
    Set xFiDialog = Nothing
    If xPath = "" Then Exit Sub
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFSO.GetFolder(xPath)
    For Each xFile In xFolder.Files
        I = I + 1
        ActiveSheet.Hyperlinks.Add Cells(I, 1), xFile.Path, , , xFile.Name
    Next
End Sub

4. Then press F5 key to run the code, a new window "Browse" will be displayed. Select the folder you would like to be listed the filenames.

5. Then click OK, the files in the specific folder have been listed into the worksheet with hyperlinks. See screenshot:

List all file names from a folder and sub-folders into a worksheet by using Kutools

The "Filename List" utility of "Kutools for Excel" can easily generate a list of files from a directory in a worksheet.

1. Open Excel, Click "Kutools Plus" > "Import / Export" > "Filename List…, "see screenshot:

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

(1) Click button to specify the folder which contains the files you want to list;

(2) Check the "Include files in subdirections" option to list all file names in subfolders or check the "Include hidden files and folders" option to list all names of hidden files as well as file names in hidden folders;

(3) Specify the file type that you want to list under the "Files type" section;

(4) Select one file size unit you want to display from the "File size unit" section you need.

(5) Check the "Create hyperlinks" option as you need.

3. Click OK. It will generate a list of files in the directory or subdirectory in a new worksheet. See screenshot:

More relative articles: