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.

Recommended Productivity Software

Office Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).

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

Hint


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.


Is your problem solved?

Recommended Productivity Tools

The following tools will greatly save your time and effort, which one do you prefer?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.

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 More than 120 powerful advanced functions which designed for Excel:

  • 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