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 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 with a powerful feature
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 folderin 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 Sub4. 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!
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
[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?
- Get List Of Sheet Names In Google Sheets
- This article, I will introduce some methods to get the name of current sheet name or a list of sheet names in Google sheets.
- 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.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, chartsand 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.
Read More... Free Download... Purchase...
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!
Read More... Free Download... Purchase...
- 77 Comments
- Login
- Sort by Newest
- Best
- Popular
- Newest
- Oldest
Say something here...
symbols left.
You are guest
- Login Now
or post as a guest, but your post won't be published automatically. Post
Loading comment... The comment will be refreshed after 00:00.
- To post as a guest, your comment is unpublished.Janet L Austin · 5 months agoI'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?
- Reply
- To post as a guest, your comment is unpublished.· 5 months agoHow do you automatically refresh the list after you change a file name?
- Reply
- To post as a guest, your comment is unpublished.bj · 7 months agoyou are genius
- Reply
- To post as a guest, your comment is unpublished.sunny · 8 months agoHow to list all file names from a SharePoint link into a Worksheet?
- Reply
- To post as a guest, your comment is unpublished.Drish · 1 years agoHI, does this line give an error if you folder path is longer?
ActiveSheet.Hyperlinks.Add Cells[I, 1], xFile.Path, , , xFile.Name- Reply
- To post as a guest, your comment is unpublished.Cristian · 2 years agoAmazing 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
- Reply
- To post as a guest, your comment is unpublished.Nilly · 2 years agovery very useful help and clear instructions. appreciate the time spent on this . thanks.
- Reply
- To post as a guest, your comment is unpublished.· 3 years agoIs 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.
- Reply
- To post as a guest, your comment is unpublished.skyyang · 3 years agoHello, Kollars,
Could you give some more details of your problem?
Or you can insert a screenshot for explaining your problem.- Reply
- To post as a guest, your comment is unpublished.· 3 years agoHi 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?- Reply
- To post as a guest, your comment is unpublished.skyyang · 3 years agoDear 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.- Reply
- To post as a guest, your comment is unpublished.· 3 years agoThank 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.
- Reply
- To post as a guest, your comment is unpublished.skyyang · 3 years agoHi, Kollars,
We will consider your suggestion, thank you very much!- Reply
- To post as a guest, your comment is unpublished.Rick · 4 years agoExcellent help and clear instructions, appreciate the time spent on this
- Reply
- To post as a guest, your comment is unpublished.Stefan Eriksson · 4 years agoYou 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.- Reply
- To post as a guest, your comment is unpublished.Jasper · 8 months agoThis is great! You can also just put the line:dir /b > list.txtin a batch file, like files.batThen users can copy this file in a directory, and double click it to get the list.txt file.You could even have excel read in the external txt file, so you only need to double click the batch file to refresh the data in excel
- Reply
- To post as a guest, your comment is unpublished.Jon Slack · 2 years agoWhen 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++].- Reply
- To post as a guest, your comment is unpublished.Marijke · 4 years agoThis is fabulous, thanks so much!
- Reply
- To post as a guest, your comment is unpublished.Chris · 4 years agoGreat Info! Easy and to the point. Love your work.
- Reply
- To post as a guest, your comment is unpublished.Raymond · 4 years agoThank you for the simple solution! BRAVO !!!!
- Reply
- To post as a guest, your comment is unpublished.Lisa · 4 years agoExcellent info! This saved hours of work! Thank you!
- Reply
- To post as a guest, your comment is unpublished.Sajith · 5 years agoThanks, the info very usefull
- Reply
- To post as a guest, your comment is unpublished.Kay · 5 years agoTHANK YOU SO MUCH! Web-browser worked like a charm! Saved me hours of work.
- Reply
- To post as a guest, your comment is unpublished.Gagandeep Singh · 5 years agoCan i auto update the list of files imported in excel?
- Reply
- To post as a guest, your comment is unpublished.Mikeschoon · 6 years agoThanks bro!
Tips like these makes the internet win! Keep up the great work! :lol:- Reply
- To post as a guest, your comment is unpublished.Jitendra Kambli · 6 years agoGreat trick.... thanks
- Reply
- To post as a guest, your comment is unpublished.MT Khan · 6 years agoThank you. It helped me to copy list of files in a folder to bring in Excel.
- Reply
- To post as a guest, your comment is unpublished.stanly · 6 years agohi,
this is great, can this be done on the 2nd level subdirectory?
thx,
stanl- Reply
- To post as a guest, your comment is unpublished.sachin badole · 6 years agoThanks u so much , i am trying to find out solution from many days..but now its awesome.
- Reply
- To post as a guest, your comment is unpublished.Ram · 6 years agoThank you.. It is useful information.
We can paste directly into excel and select remove duplicate option to see the list details- Reply
- To post as a guest, your comment is unpublished.Martin · 7 years agoThank 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.
- Reply
- To post as a guest, your comment is unpublished.mohamed · 7 years agothe hyperlink doesn't work there is a message[can not open the specified file any one can help me??
- Reply
- To post as a guest, your comment is unpublished.ezhirkho · 7 years agoThankyou very much sir for your wonderful input
- Reply
- To post as a guest, your comment is unpublished.Peter Li · 7 years agoVery good website. Learn a lot from it. :D
- Reply
- To post as a guest, your comment is unpublished.Ed · 7 years agoKudos for the browser + Excel trick. I'd used batch files before but that is one neat trick with Excel!
- Reply
- To post as a guest, your comment is unpublished.Yusuf Basith · 7 years agoAdd this line in the loop to generate hyperlink for the files
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset[xRow], Address:=xDirect$ & xFname$, TextToDisplay:=xFname$- Reply
- To post as a guest, your comment is unpublished.้鸿 · 7 years agothank you very มากมาย krab
Kob khun krab,Alot of help here- Reply
- To post as a guest, your comment is unpublished.Lejo Mathews David · 7 years agoYou have made my life better ..... :-]
- Reply
- To post as a guest, your comment is unpublished.Ripu · 7 years agoHi,
Thanks a lot to share this way.
really its very simply- Reply
- To post as a guest, your comment is unpublished.Soumya · 7 years agoThanks! That was awesome!
- Reply
- To post as a guest, your comment is unpublished.abo tahe · 7 years agoBulk Rename Utility very good
- Reply
- To post as a guest, your comment is unpublished.tj · 7 years agohow can I get only the filename with extentsion ".txt"
- Reply
- To post as a guest, your comment is unpublished.Prafulla Jha · 7 years agoHiiii
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...- Reply
- To post as a guest, your comment is unpublished.Raghu Krishnan · 7 years agoThanks for the macro..It was really very helpful
Can you please tell me what is the significance of number 7 with Dir function- Reply
- To post as a guest, your comment is unpublished.Shrinidhi Acharya · 7 years ago:lol: Awesome Sir.. U R Great....
- Reply
- To post as a guest, your comment is unpublished.precious · 7 years agohi,
thank you so much for your help this tool works wonders. who could have thought I can do more than thousand files a day.- Reply
- To post as a guest, your comment is unpublished.Jenn · 7 years agoThank you! This is SO HELPFUL.
- Reply
- To post as a guest, your comment is unpublished.Manolo · 7 years agoHi 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.- Reply
- To post as a guest, your comment is unpublished.Brad H · 6 years agoDid 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."- Reply
- To post as a guest, your comment is unpublished.Himani · 7 years agoHi,
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?- Reply
- To post as a guest, your comment is unpublished.Durga · 7 years agoThank u very much u saved my time :] .
- Reply
- To post as a guest, your comment is unpublished.Liz · 7 years agoLoved 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!- Reply
- To post as a guest, your comment is unpublished.Sammi · 7 years agoPowerQuery add-in with Excel-2013 solves this in a easy way.
Under From-File there is a From-Folder option.- Reply
- To post as a guest, your comment is unpublished.Chris · 7 years agoThis 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.- Reply
- To post as a guest, your comment is unpublished.Mahesh · 8 years agoThank you. you saved my time.
- Reply
- To post as a guest, your comment is unpublished.Crystal · 8 years agoThank you! Can't tell you what a lifesaver [and time saver] this was for me!
- Reply
- To post as a guest, your comment is unpublished.eva · 8 years agonice.... :-]
Is there any macro to auto update the excel from the file directory...
when new files are added in the directory- Reply
- To post as a guest, your comment is unpublished.Aditi Shah · 8 years agoawesome method.
thanks a ton!!
this is amazingly helpful....- Reply
- 1
- 2