By Sophia | Last Updated
As modern office workers, sometimes our supervisor would ask us to list some files' names in one specified directory in Excel. Here we would show you some ways to list files in one directory to the worksheet in Excel. Just follow me.
Step 1: Find the specified directory you like and copy the path of in the explorer.
Step 2: Open a browser and paste the path into the address bar of it and press Enter. Then you can see all the files in that directory is displayed in the browser.
Step 3: Place cursor to contents of the files and press Ctrl+A to choose the whole contents and then press Ctrl+C to copy all of them.
Step 4: Open an Excel document, press Ctrl+V at the same time to paste all the files to a worksheet in Excel.
Sometimes if you don't need to list all the information of the files except the names of those files, you can try the following way.
Step 1: Open a worksheet in Excel and click Developer>>Visual Basic or press Alt+F11 to bring up Microsoft Visual Basic for Applications window. Choose the target worksheet in the left pane of the window and navigate to Insert>>Module to open a module window.
Step 2: Copy and paste the following codes to the module window and then click Run>>Run Macro F5 to execute the codes.
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
Step 3: In the coming window, just navigate to the target files' directory in the explorer, and click OK to choose the corresponding directory.
Now you can see that all the files' names in the directory are listed in the worksheet.
Related Links:
