How to list files in a directory to worksheet in Excel?

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.

Get a list of files in worksheet by using a web browser;
Too many steps, it's not a handy trick.

Using VBA to list files of a folder in a worksheet;
Too complicated, need to know VBA.

List files in a folder or subdirectory in worksheet with Filename list.
It's quite handy.


arrow blue right bubbleGet a list of files in 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: (It’s applicable in Windows XP, Windows Vista and Windows 7. )

1. Go to copy the path of the folder (kte data) in Explorer. For example, the path of this folder is: C:\Users\dt\Desktop\kte data.

doc-list-files1

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-files2

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-files3

Disadvantage: this method cannot list files in the subdirectory.


arrow blue right bubbleUsing VBA to list files of a folder in a worksheet

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

1. Open a worksheet, click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and then input the following codes in the module:

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

2. Then click doc-list-files-4 button to run the operation, 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-files4

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

doc-list-files5

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.


arrow blue right bubbleList files in a folder or subdirectory in worksheet with Filename list

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 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now

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 Enterprise > Import/Export > Filename List…

doc-list-files6

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

(1.) clickdoc-list-files-button button to specify the folder which contains the files you want to list;

(2.) Specify the file type that you want to list under the Files type section;

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

doc-list-files7

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

doc-list-files8

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 for more detailed information about Filename List.

Free Download Kutools for Excel Now


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments  

Permalink +2 Manu Adam
Dear Sir, Thanks for a very nice informative article on Excel. I was able to fetch the file names from a folder in one column with the help of your module. Now I have a few queries. I will be very grateful to you if you could find some of your precious time to resolve them.
Scenario: What I am trying to do and my target:
I get realtime data in csv files from my broker terminal. The moment market starts, these csv files (with respective stock names) are created in a particular folder, named data. All these CSV files have three columns TIME PRICE VOLUME.
Files names are usually like this: FUTCOM-ALUMINIU M-29NOV2013_MCX 113928.csv
I am fetching these file names in first column of my RTdata.xlsm.

Query 1: When I run your module it asks (searches) for folder. Can I give the full path in module so that it automatically goes that folder instead of searching for it. Where to insert folder path.

Query 2: The module fetches full file name. I want it to be trimmed like this: From FUTCOM-ALUMINIU M-29NOV2013_MCX 113928.csv to FUTCOM-ALUMINIUM-29NOV2013.

Query3: How to further fetch data from three columns TIME PRICE VOLUME from each csv files into my RTdata.xlsm in the row of their respective names. Data from just the last row of each csv files (as they are dynamically changing in real time).

I do realise that it is asking for too much. But I humbly request you to kindly guide me. Thanks & Regards. Manu Adam (New Delhi, India)
2013-10-28 05:27 Reply Reply with quote Quote
Permalink 0 Bharat
Hello Manu Adam, I have a similar query as you query 1. If you have found a solution for that please share the solution or mail me at . Any help will b really appreciable.

thanks in Advance.
2016-05-03 09:53 Reply Reply with quote Quote
Permalink 0 Hanmant Musale
Awesome tutorial!!!! Thanks a lot for this quick solution :-)
2017-02-01 13:38 Reply Reply with quote Quote
Permalink +5 Allison
Thank you for posting this! Web browser method was really simple
2013-10-29 22:39 Reply Reply with quote Quote
Permalink +6 Nagesh
very useful information...
thanks...

Keep it up....
2013-11-28 10:28 Reply Reply with quote Quote
Permalink +2 deborah
you guys rock. great add on program. I will be getting the paid version. this was great for working on getting a directory print out of files for a business that had a bad employee deleting files. now to see if it also will print all the files in the directory out for me. at the very least I should be able to use the file list to create a script to print with. thanks a lot.
2013-12-03 20:09 Reply Reply with quote Quote
Permalink 0 Mario Ortega
I have previously found a mail merge excel utility that would fill columnar info into the form and print it. Problem is I need to create Excel files of the printed info as well. This program creates the files as I would like but does it have the capability to fill into the created files as well?
2013-12-27 14:32 Reply Reply with quote Quote
Permalink 0 Anil Maurya
Dear All

Thanks for VBA code i.e. "Using VBA to list files of a folder in a worksheet"
It is realy usefull for generating the list of file name.

I am thankfull, if you can provide the code that will select the folder automatically as per define path.

Regards
Anil M
2014-01-01 06:50 Reply Reply with quote Quote
Permalink +3 Atul
Dear All could some one give me way to use file name in macro
2014-01-08 12:50 Reply Reply with quote Quote
Permalink +1 Vic
Thank you very much for this useful information.
2014-01-09 20:13 Reply Reply with quote Quote
Permalink +2 Nav
Genius in Simplicity..!
2014-01-29 15:26 Reply Reply with quote Quote
Permalink +2 yves
Thanks a lot for this quick solution :-)
2014-01-30 08:21 Reply Reply with quote Quote
Permalink +1 CS
Thank you so very much!!!!! Just what I needed.
2014-01-31 18:35 Reply Reply with quote Quote
Permalink +2 Ayman
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.
2014-02-19 14:49 Reply Reply with quote Quote
Permalink 0 Atul
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
2014-02-21 08:40 Reply Reply with quote Quote
Permalink +1 Pete
Just do Ctrl+H then replace .pdf with nothing
You could also do text to columns, delimited, other "."
2014-03-19 10:00 Reply Reply with quote Quote
Permalink +1 lovelymidwestgirl
This just made my day - Thanks!
2014-03-19 15:58 Reply Reply with quote Quote
Permalink 0 Atul
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
2014-03-20 03:47 Reply Reply with quote Quote
Permalink 0 Betty F
Four steps is too many for the web browser method? Not really
2014-03-23 01:22 Reply Reply with quote Quote
Permalink +1 Aditi Shah
awesome method.
thanks a ton!!
this is amazingly helpful....
2014-03-28 09:17 Reply Reply with quote Quote
Permalink +2 eva
nice.... :-)

Is there any macro to auto update the excel from the file directory...
when new files are added in the directory
2014-04-01 10:39 Reply Reply with quote Quote
Permalink +1 Crystal
Thank you! Can't tell you what a lifesaver (and time saver) this was for me!
2014-04-02 14:47 Reply Reply with quote Quote
Permalink 0 Mahesh
Thank you. you saved my time.
2014-04-05 19:33 Reply Reply with quote Quote
Permalink +2 Chris
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.
2014-04-30 15:43 Reply Reply with quote Quote
Permalink 0 Sammi
PowerQuery add-in with Excel-2013 solves this in a easy way.
Under From-File there is a From-Folder option.
2014-05-05 11:44 Reply Reply with quote Quote
Permalink -1 Liz
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!
2014-05-08 22:59 Reply Reply with quote Quote
Permalink 0 Durga
Thank u very much u saved my time :) .
2014-05-13 15:03 Reply Reply with quote Quote
Permalink 0 Himani
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?
2014-05-14 05:20 Reply Reply with quote Quote
Permalink 0 Manolo
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.
2014-06-03 21:16 Reply Reply with quote Quote
Permalink -1 Brad H
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."
2015-07-17 14:51 Reply Reply with quote Quote
Permalink 0 Jenn
Thank you! This is SO HELPFUL.
2014-06-04 20:24 Reply Reply with quote Quote
Permalink 0 precious
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.
2014-06-06 10:16 Reply Reply with quote Quote
Permalink 0 Shrinidhi Acharya
:lol: Awesome Sir.. U R Great....
2014-06-18 13:52 Reply Reply with quote Quote
Permalink 0 Raghu Krishnan
Thanks for the macro..It was really very helpful

Can you please tell me what is the significance of number 7 with Dir function
2014-07-15 15:13 Reply Reply with quote Quote
Permalink 0 Prafulla Jha
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...
2014-07-22 10:40 Reply Reply with quote Quote
Permalink 0 tj
how can I get only the filename with extentsion ".txt"
2014-09-01 03:08 Reply Reply with quote Quote
Permalink 0 abo tahe
Bulk Rename Utility very good
2014-09-04 17:21 Reply Reply with quote Quote
Permalink +1 Soumya
Thanks! That was awesome!
2014-09-18 12:16 Reply Reply with quote Quote
Permalink 0 Ripu
Hi,

Thanks a lot to share this way.
really its very simply
2014-09-27 10:04 Reply Reply with quote Quote
Permalink 0 Lejo Mathews David
You have made my life better ..... :-)
2014-09-30 09:19 Reply Reply with quote Quote
Permalink 0 ้鸿
thank you very มากมาย krab
Kob khun krab,Alot of help here
2014-10-02 12:39 Reply Reply with quote Quote
Permalink +1 Yusuf Basith
Add this line in the loop to generate hyperlink for the files

ActiveSheet.Hyperlinks.Add Anchor:=ActiveC ell.Offset(xRow ), Address:=xDirec t$ & xFname$, TextToDisplay:= xFname$
2014-10-10 09:42 Reply Reply with quote Quote
Permalink 0 Ed
Kudos for the browser + Excel trick. I'd used batch files before but that is one neat trick with Excel!
2014-11-17 15:17 Reply Reply with quote Quote
Permalink 0 Peter Li
Very good website. Learn a lot from it. :D
2015-01-08 04:34 Reply Reply with quote Quote
Permalink -1 ezhirkho
Thankyou very much sir for your wonderful input
2015-01-31 07:48 Reply Reply with quote Quote
Permalink 0 mohamed
the hyperlink doesn't work there is a message(can not open the specified file any one can help me??
2015-02-21 16:20 Reply Reply with quote Quote
Permalink 0 Martin
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.
2015-03-13 18:07 Reply Reply with quote Quote
Permalink -1 Ram
Thank you.. It is useful information.

We can paste directly into excel and select remove duplicate option to see the list details
2015-05-14 17:26 Reply Reply with quote Quote
Permalink 0 sachin badole
Thanks u so much , i am trying to find out solution from many days..but now its awesome.
2015-07-29 13:36 Reply Reply with quote Quote
Permalink 0 stanly
hi,

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

thx,
stanl
2015-08-12 11:57 Reply Reply with quote Quote
Permalink 0 MT Khan
Thank you. It helped me to copy list of files in a folder to bring in Excel.
2015-08-22 08:51 Reply Reply with quote Quote
Permalink 0 Jitendra Kambli
Great trick.... thanks
2015-12-05 08:49 Reply Reply with quote Quote
Permalink 0 Mikeschoon
Thanks bro!

Tips like these makes the internet win! Keep up the great work! :lol:
2015-12-28 13:09 Reply Reply with quote Quote
Permalink 0 Gagandeep Singh
Can i auto update the list of files imported in excel?
2016-05-21 05:16 Reply Reply with quote Quote
Permalink 0 Kay
THANK YOU SO MUCH! Web-browser worked like a charm! Saved me hours of work.
2016-08-12 23:43 Reply Reply with quote Quote

Add comment


Security code
Refresh