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 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-4button 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 as below demo shown.    Click to download Kutools for Excel!

doc list all filenames 9

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.) Click doc-list-files-buttonbutton 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 30 days. Download the free trial now!


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
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 · 1 years 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 · 1 years ago
    very very useful help and clear instructions. appreciate the time spent on this . thanks.
  • To post as a guest, your comment is unpublished.
    jkoll66@gmail.com · 2 years 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 · 2 years 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.
        jkoll66@gmail.com · 2 years 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 · 2 years 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.
            jkoll66@gmail.com · 2 years 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 · 2 years ago
              Hi, Kollars,
              We will consider your suggestion, thank you very much!
  • To post as a guest, your comment is unpublished.
    Rick · 2 years ago
    Excellent help and clear instructions, appreciate the time spent on this
  • To post as a guest, your comment is unpublished.
    Stefan Eriksson · 2 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.
    • To post as a guest, your comment is unpublished.
      Jon Slack · 9 months ago
      When you have imported your text into Excel, it's still text. It's in long lines of text you then need to write formulas for, to break it up into useable data. Then you need to manually remove the superfluous data and blank lines you don't need. This solution is a way of getting a list of files into Excel, but without a huge amount of manual manipulation, it's useless.
      Read moreIt boggles me that even today, there appears to be no useful way of getting a directory listing into Excel (or indeed, any other program that isn't notepad or the vastly superior Notepad++).
  • To post as a guest, your comment is unpublished.
    Marijke · 2 years ago
    This is fabulous, thanks so much!
  • To post as a guest, your comment is unpublished.
    Chris · 2 years ago
    Great Info! Easy and to the point. Love your work.
  • To post as a guest, your comment is unpublished.
    Raymond · 3 years ago
    Thank you for the simple solution! BRAVO !!!!
  • To post as a guest, your comment is unpublished.
    Lisa · 3 years ago
    Excellent info! This saved hours of work! Thank you!
  • To post as a guest, your comment is unpublished.
    Sajith · 3 years ago
    Thanks, the info very usefull
  • To post as a guest, your comment is unpublished.
    Kay · 4 years ago
    THANK YOU SO MUCH! Web-browser worked like a charm! Saved me hours of work.
  • To post as a guest, your comment is unpublished.
    Gagandeep Singh · 4 years ago
    Can i auto update the list of files imported in excel?
  • To post as a guest, your comment is unpublished.
    Mikeschoon · 4 years ago
    Thanks bro!

    Tips like these makes the internet win! Keep up the great work! :lol:
  • To post as a guest, your comment is unpublished.
    Jitendra Kambli · 4 years ago
    Great trick.... thanks
  • To post as a guest, your comment is unpublished.
    MT Khan · 5 years ago
    Thank you. It helped me to copy list of files in a folder to bring in Excel.
  • To post as a guest, your comment is unpublished.
    stanly · 5 years ago
    hi,

    this is great, can this be done on the 2nd level subdirectory?

    thx,
    stanl
  • To post as a guest, your comment is unpublished.
    sachin badole · 5 years ago
    Thanks u so much , i am trying to find out solution from many days..but now its awesome.
  • To post as a guest, your comment is unpublished.
    Ram · 5 years ago
    Thank you.. It is useful information.

    We can paste directly into excel and select remove duplicate option to see the list details
  • To post as a guest, your comment is unpublished.
    Martin · 5 years ago
    Thank you for the macro to list files in a directory. It worked awesome. I searched everywhere for this and I cannot begin to tell you how complicated they made it; yours is so simple.
  • To post as a guest, your comment is unpublished.
    mohamed · 5 years ago
    the hyperlink doesn't work there is a message(can not open the specified file any one can help me??
  • To post as a guest, your comment is unpublished.
    ezhirkho · 5 years ago
    Thankyou very much sir for your wonderful input
  • To post as a guest, your comment is unpublished.
    Peter Li · 5 years ago
    Very good website. Learn a lot from it. :D
  • To post as a guest, your comment is unpublished.
    Ed · 6 years ago
    Kudos for the browser + Excel trick. I'd used batch files before but that is one neat trick with Excel!
  • To post as a guest, your comment is unpublished.
    Yusuf Basith · 6 years ago
    Add this line in the loop to generate hyperlink for the files

    ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(xRow), Address:=xDirect$ & xFname$, TextToDisplay:=xFname$
  • To post as a guest, your comment is unpublished.
    ้鸿 · 6 years ago
    thank you very มากมาย krab
    Kob khun krab,Alot of help here
  • To post as a guest, your comment is unpublished.
    Lejo Mathews David · 6 years ago
    You have made my life better ..... :-)
  • To post as a guest, your comment is unpublished.
    Ripu · 6 years ago
    Hi,

    Thanks a lot to share this way.
    really its very simply
  • To post as a guest, your comment is unpublished.
    Soumya · 6 years ago
    Thanks! That was awesome!
  • To post as a guest, your comment is unpublished.
    abo tahe · 6 years ago
    Bulk Rename Utility very good
  • To post as a guest, your comment is unpublished.
    tj · 6 years ago
    how can I get only the filename with extentsion ".txt"
  • To post as a guest, your comment is unpublished.
    Prafulla Jha · 6 years ago
    Hiiii
    any buddy pls solve my problem...
    my excel file is locked i have lost my password.
    i can use all method in upper mention all step (3. Click OK. It will generate a list of files in the directory or subdirectory in worksheet. See screenshot:)
    bt my excel file is not remove password...
    so pls. give any ideas...
  • To post as a guest, your comment is unpublished.
    Raghu Krishnan · 6 years ago
    Thanks for the macro..It was really very helpful

    Can you please tell me what is the significance of number 7 with Dir function
  • To post as a guest, your comment is unpublished.
    Shrinidhi Acharya · 6 years ago
    :lol: Awesome Sir.. U R Great....
  • To post as a guest, your comment is unpublished.
    precious · 6 years ago
    hi,

    thank you so much for your help this tool works wonders. who could have thought I can do more than thousand files a day.
  • To post as a guest, your comment is unpublished.
    Jenn · 6 years ago
    Thank you! This is SO HELPFUL.
  • To post as a guest, your comment is unpublished.
    Manolo · 6 years ago
    Hi there!
    This tool works for me, is very good. But I have a problem, I have to analyze a folder that has 30 sub folders and also like 60,000 images. The system says it doesn't work with more than 10,000 files. What can I do?
    I wish you could help me.

    Best regards.
    • To post as a guest, your comment is unpublished.
      Brad H · 5 years ago
      Did you find a solution for this problem?
      "# Manolo 2014-06-03 21:16
      Hi there!
      This tool works for me, is very good. But I have a problem, I have to analyze a folder that has 30 sub folders and also like 60,000 images. The system says it doesn't work with more than 10,000 files. What can I do?
      I wish you could help me.

      Best regards."
  • To post as a guest, your comment is unpublished.
    Himani · 6 years ago
    Hi,
    Is this necessary that we should have google chrome or mozilla browser? As I have only internet explorer and after executing this code, dialogue box gets opened but after selecting the folder name, it gives an error bad file name or path.

    Could you please let me know what might be the issue?
  • To post as a guest, your comment is unpublished.
    Durga · 6 years ago
    Thank u very much u saved my time :) .
  • To post as a guest, your comment is unpublished.
    Liz · 6 years ago
    Loved the VB code, very useful, except i was wondering if there's a way to enlist files from a subfolder contained within the folder you selected to get the files from.

    Thanks!
  • To post as a guest, your comment is unpublished.
    Sammi · 6 years ago
    PowerQuery add-in with Excel-2013 solves this in a easy way.
    Under From-File there is a From-Folder option.
  • To post as a guest, your comment is unpublished.
    Chris · 6 years ago
    This way is easier for me:

    Go to the folder you are interested in with windows explorer and type a period into the search box. This will generate a list of every folder and file in that directory. Then ctrl+a to select all and shift+right click -> copy as path then paste into excel. Using this method, if you only wanted pdfs for example you can just search for .pdf instead.
  • To post as a guest, your comment is unpublished.
    Mahesh · 6 years ago
    Thank you. you saved my time.
  • To post as a guest, your comment is unpublished.
    Crystal · 6 years ago
    Thank you! Can't tell you what a lifesaver (and time saver) this was for me!
  • To post as a guest, your comment is unpublished.
    eva · 6 years ago
    nice.... :-)

    Is there any macro to auto update the excel from the file directory...
    when new files are added in the directory
  • To post as a guest, your comment is unpublished.
    Aditi Shah · 6 years ago
    awesome method.
    thanks a ton!!
    this is amazingly helpful....
  • To post as a guest, your comment is unpublished.
    Betty F · 6 years ago
    Four steps is too many for the web browser method? Not really
  • To post as a guest, your comment is unpublished.
    Atul · 6 years ago
    Thanks pete i guess that could be one way by adding a recorded macro but it would be simple if first micro itself generates list of file names without extensions still thanks a lot will try this today
  • To post as a guest, your comment is unpublished.
    lovelymidwestgirl · 6 years ago
    This just made my day - Thanks!
  • To post as a guest, your comment is unpublished.
    Atul · 6 years ago
    Could some one help me to list file names without extensions or a way to remove .pdf or .doc from name of files.
    Thanks in advance
    • To post as a guest, your comment is unpublished.
      Pete · 6 years ago
      Just do Ctrl+H then replace .pdf with nothing
      You could also do text to columns, delimited, other "."
  • To post as a guest, your comment is unpublished.
    Ayman · 6 years ago
    OMG, this is really helpful.
    I had a folder with 1850 files and i needed to list them as a requirement for Engineering review.

    one small change might affect milestone submission date. this solution rescued me.

    thanks a lot.