Skip to main content

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

If you want to generate a list of file names within a directory in a worksheet, you can process the following ways to get a list of files from a directory in worksheet quickly.


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:

Notes:
  • 1. With this method, only the files in the main folder can be displayed, the files in the subdirectory can not be listed.
  • 2. The hyperlinks of the file names are not available.

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 psecifc type 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:

=FILES(Sheet1!$A$1)

Note: In this formula, Sheet1!$A$1 is the cell of the worksheet contains the file path you are 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:

=IFERROR(INDEX(Filenames,ROW(A1)),"")

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

Notes:
  • 1. You should save this workbook as Excel Macro-Enabled Workbook format, if you want the formulas work well after the file is closed and reopened.
  • 2. If you want to list all files with a specific extension, such as list all docx file names, in this case, you just need to use *docx* instead of *, similarly, for xlsx files, please use *xlsx*.


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:

Note:Only the file names in the main folder can be listed.

List all file names from a folder and sub-folders into a worksheet with a powerful feature

The Filename List utility of Kutools for Excel can easily generate a list of files from a directory in a worksheet as below demo shown.    Click to download Kutools for Excel!

doc list all filenames 9

Note:To apply this Filename List, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

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 doc-list-files-button 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:

Click to Download Kutools for Excel and free trial Now!


More relative articles:

  • Create A List Of All Worksheet Names From A Workbook
  • Supposing, you have a workbook with multiple worksheets, now you want to list all of the sheet names in current workbook, is there any quick method for creating a list of theses sheet names in Excel without typing them one by one? This tutorial is giving instructions on how to list worksheet names in Excel.
  • Copy Or Move Files From One Folder To Another Based On A List
  • If you have a list of file names in a column in a worksheet, and the files locate in a folder in your computor. But, now, you need to move or copy these files which names are listed into the worksheet from their original folder to another one as following screenshot shown. How could you finish this task as quickly as you can in Excel?
  • Navigate Between Worksheets By Using Drop Down List
  • Supposing, you have a workbook which contains multiple worksheets, now, you need to create a drop down list or combo box which lists all sheet names and when you select one sheet name from the drop down list, it will jump to that sheet immediately. This article, I will introduce how to navigate between worksheets by using a drop down list in Excel.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!
Comments (80)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I've been using vb6 since years. That was very easy to copy all the file names of directory and sub-directory. It was possible to manipulate the name of the files as you wanted. Unfortunately, it has been abandonned...
This comment was minimized by the moderator on the site
Is there any way to get a list of files in a Microsoft Teams/SharePoint folder including sub folders? I can synch the folder locally but then the hyperlinks generated are local. I really want the links to the Teams location for purpose of sharing with the team.
This comment was minimized by the moderator on the site
السلام عليكم ...شكرا جزيلا وفقكم الله لكل خير...معلومات قيمة ومفيدة ..نفعتني كثيرا وفرحت بها كثيرا
This comment was minimized by the moderator on the site
I'm 63 years old. Have been looking for how to do this this (directory to Excel to with attributes) for years! Tried many methods so complicated that I had to learn new programming language (i.e. command prompt and command shell, I am subject matter expert, IT user; not dedicated IT). But thought it should be simple so kept looking. AND HERE IT IS!! So easy my now elderly brain gets it. Thank you, thank you. Bless you! All hail extendoffice! You rock.
Hmmm. Does anybody else have programming how-tos on their bucket list?
This comment was minimized by the moderator on the site
How do you automatically refresh the list after you change a file name?
This comment was minimized by the moderator on the site
you are genius
This comment was minimized by the moderator on the site
How to list all file names from a SharePoint link into a Worksheet?
This comment was minimized by the moderator on the site
HI, does this line give an error if you folder path is longer?

ActiveSheet.Hyperlinks.Add Cells(I, 1), xFile.Path, , , xFile.Name
This comment was minimized by the moderator on the site
Amazing trick ! Unfortunately is working only for certain folders and I couldnt identify whats the selection criteria. I only can tell that Microsoft Excel Security Notice says "Microsoft Office has identify a potential security concern. This location may be unsafe. C:\C:\Users\popal". I dont understand where is "C:\C:" comming from but I m getting the same "C:\C:" when I hover the mouse over the hypelink. Thanks for any suggestion
This comment was minimized by the moderator on the site
very very useful help and clear instructions. appreciate the time spent on this . thanks.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations