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.
Recommended Productivity Tools
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.
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:
Disadvantage: this method cannot list files in the subdirectory.
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 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.
3. Then click OK, the files in the specific folder have been listed into the worksheet. See screenshot:
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.
The Filename List utility of Kutools for Excel can easily generate a list of files from a directory in a worksheet.
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…
2. In the Filename List dialog box,do the following operations:
(1.) click 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.
3. Click OK. It will generate a list of files in the directory or subdirectory in worksheet. See screenshot:
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.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
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...