Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

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 with Kutools for Excel.


List all filenames in a folder and subfolders into a worksheet:

With Kutools for Excel's Filename List feature, you can quickly list all file names from a folder including subfolders into a new worksheet.

doc list all filenames 9

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


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\DT168\Desktop\Folder test.

doc list all filenames 1

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:

doc list all filenames 2

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:

doc list all filenames 3

Disadvantage: this method cannot list files in the subdirectory.


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 in a folder

Option Explicit
Sub GetFileNames()
Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
InitialFoldr$ = "C:\" 
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
ActiveCell.Offset(xRow) = xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
End Sub

4. Then click doc-list-files-4 button to run the code, a new window Please select a folder to list files from will be displayed. Select the folder you would like to be listed the filenames.

doc list all filenames 4

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

doc list all filenames 5

Disadvantage: By using this VBA, you can only get a list of filenames in a worksheet, no hyperlinks link to files and no other information. it’s not easy to get a list of files in a folder in a worksheet.


List all file names from a folder and sub-folders into a worksheet with Kutools for Excel

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

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. 

If you have installed Kutools for Excel, you can quickly get a list of files in a folder in worksheet as follows:

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

doc list all filenames 6

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

doc list all filenames 7

(1.) Clickdoc-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 worksheet. See screenshot:

doc list all filenames 8

Advantage: you can specify to list the files of the subdirectory or not, to create hyperlinks for each file or folder. You can also specify to list the files in the list according to the extensions of file types. It’s quite easy for all Excel users.

Click to Download Kutools for Excel and free trial Now!


List files in a folder or subdirectory in worksheet with Kutools for Excel

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Cristian · 24 days ago
    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
  • To post as a guest, your comment is unpublished.
    Nilly · 3 months ago
    very very useful help and clear instructions. appreciate the time spent on this . thanks.
  • To post as a guest, your comment is unpublished.
    Jeff Kollars · 10 months ago
    Is there a way to add a single filename to a row with all of the additional info that comes with using the Kutools import filenames function? I populated a spreadsheet using the import filenames function and now I need to add additional files. Thanks in advance.
    • To post as a guest, your comment is unpublished.
      skyyang · 10 months ago
      Hello, Kollars,
      Could you give some more details of your problem?
      Or you can insert a screenshot for explaining your problem.
      • To post as a guest, your comment is unpublished.
        Jeff Kollars · 10 months ago
        Hi Skyyang,

        So, say that I've opened a blank spreadsheet and imported a filenames list from a folder using Kutools "Import Filename List". Later I add a new file to that folder. Now I want to import the filename for only that file into the same spreadsheet. Is this possible and how do I do it?
        • To post as a guest, your comment is unpublished.
          skyyang · 10 months ago
          Dear Kollars,
          If you have new file names need to import, you should apply the Filename List again. And it will import all the filenames once more.
          • To post as a guest, your comment is unpublished.
            Jeff Kollars · 10 months ago
            Thank you Skyyang. It would be good to have a way to import only selected files into the list. Maybe a feature you could include in a future release. Thanks.
            • To post as a guest, your comment is unpublished.
              skyyang · 10 months ago
              Hi, Kollars,
              We will consider your suggestion, thank you very much!
  • To post as a guest, your comment is unpublished.
    Rick · 1 years ago
    Excellent help and clear instructions, appreciate the time spent on this
  • To post as a guest, your comment is unpublished.
    Stefan Eriksson · 1 years ago
    You can use the terminal as well.

    Start cmd.exe.
    Use the 'cd' command and go to the folder with the files: cd "C:\Users\dt\Desktop\kte data\".
    Run the command 'dir /b > list.txt'.
    Run the command 'notepad.exe list.txt'.
    Select and copy the content with CTRL + A and CTRL + C.
    Exit notepad with ALT + F4.
    Open Excel.
    Paste the content into Excel with CTRL + V.
    Done.

    I wish more Windows users could use the terminal to solve simple things like this.