Cookies help us deliver our services. By using our services, you agree to our use of cookies.
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 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

Using VBA to list files of a folder in a worksheet

List files in a folder or subdirectory in 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!


Get 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: 

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.


Using 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, 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 files in a folder or subdirectory in 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

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

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.
    deborah · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Nagesh · 5 years ago
    very useful information...
    thanks...

    Keep it up....
  • To post as a guest, your comment is unpublished.
    Allison · 5 years ago
    Thank you for posting this! Web browser method was really simple
  • To post as a guest, your comment is unpublished.
    Manu Adam · 5 years ago
    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)
    • To post as a guest, your comment is unpublished.
      Hanmant Musale · 1 years ago
      Awesome tutorial!!!! Thanks a lot for this quick solution :-)
    • To post as a guest, your comment is unpublished.
      Bharat · 2 years ago
      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 bharatsharma116@gmail.com. Any help will b really appreciable.

      thanks in Advance.