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

If you want to generate a list of files in a directory or list files in a folder 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.

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubbleGet a list of files in worksheet by using a web browser

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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 (list files in worksheet) in worksheet, see screenshot: (It’s applicable in Windows XP, Windows Vista and Windows 7. )

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

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:

Option Explicit
Sub GetFileNames()
Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
InitialFoldr$ = "C:\" '<<< Startup folder to begin searching from
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. 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 80 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, specify the folder you want to list in worksheet by clicking doc-list-files-button button, and specify other settings. See screenshot:

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.

For more detailed information about Filename List, please visit Filename List feature description.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

-2#Manu Adam2013-10-28 05:27
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-ALUMINIUM-29NOV2013_MCX113928.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-ALUMINIUM-29NOV2013_MCX113928.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)
Reply | Reply with quote | Quote
-2#Allison2013-10-29 22:39
Thank you for posting this! Web browser method was really simple
Reply | Reply with quote | Quote
+2#Nagesh2013-11-28 10:28
very useful information...
thanks...

Keep it up....
Reply | Reply with quote | Quote
0#deborah2013-12-03 20:09
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.
Reply | Reply with quote | Quote
0#Mario Ortega2013-12-27 14:32
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?
Reply | Reply with quote | Quote
0#Anil Maurya2014-01-01 06:50
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
Reply | Reply with quote | Quote
+2#Atul2014-01-08 12:50
Dear All could some one give me way to use file name in macro
Reply | Reply with quote | Quote
0#Vic2014-01-09 20:13
Thank you very much for this useful information.
Reply | Reply with quote | Quote
+1#Nav2014-01-29 15:26
Genius in Simplicity..!
Reply | Reply with quote | Quote
+1#yves2014-01-30 08:21
Thanks a lot for this quick solution :-)
Reply | Reply with quote | Quote
0#CS2014-01-31 18:35
Thank you so very much!!!!! Just what I needed.
Reply | Reply with quote | Quote
+1#Ayman2014-02-19 14:49
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.
Reply | Reply with quote | Quote
0#Atul2014-02-21 08:40
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
Reply | Reply with quote | Quote
+1#Pete2014-03-19 10:00
Just do Ctrl+H then replace .pdf with nothing
You could also do text to columns, delimited, other "."
Reply | Reply with quote | Quote
0#lovelymidwestgirl2014-03-19 15:58
This just made my day - Thanks!
Reply | Reply with quote | Quote
0#Atul2014-03-20 03:47
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
Reply | Reply with quote | Quote
0#Betty F2014-03-23 01:22
Four steps is too many for the web browser method? Not really
Reply | Reply with quote | Quote
0#Aditi Shah2014-03-28 09:17
awesome method.
thanks a ton!!
this is amazingly helpful....
Reply | Reply with quote | Quote
+1#eva2014-04-01 10:39
nice.... :-)

Is there any macro to auto update the excel from the file directory...
when new files are added in the directory
Reply | Reply with quote | Quote
-1#Crystal2014-04-02 14:47
Thank you! Can't tell you what a lifesaver (and time saver) this was for me!
Reply | Reply with quote | Quote
0#Mahesh2014-04-05 19:33
Thank you. you saved my time.
Reply | Reply with quote | Quote

Add comment


Security code
Refresh