How to Copy File Names from Folder to Excel (4 Easy Methods)

Here’s our sample dataset which contains 5 files in a folder named ExcelDemy. We’ll copy the names to an Excel sheet.

Method 1 – Using the Shortcut Keys to Copy File Names from Folder

Steps:

Select all the files by pressing Ctrl + A.
Press and hold the Shift key and right-click.
Click Copy as path from the context menu.

Go to your Excel worksheet, select a cell, and press Ctrl + V. We chose Cell B5.

Select all the copied values.
Press Ctrl + H to open the Find and Replace tool.
Insert the part of the address before the file name in the Find what box.
Keep the Replace with box empty.
Press Replace All.

Here is the result.

Method 2 – Copy File Names from a Folder with the FILES Function

Steps:

Copy the file path in a cell with an asterisk at the end. We copied in Cell B5. This cell reference will be used in the named range.

Click on Define Name from the Formulas tab.

Give a name to the named range.
Select Workbook from the Scope drop-down box.
Insert the following formula in the Refers to box and press OK–

=FILES(Sheet1!$B$5)

Insert the following formula in Cell B8:

=IFERROR(INDEX(FileNames,ROW()-7),"")

Hit the Enter button.

Formula Breakdown:

The FILES formula will return the file names from the specified folder.
We used the INDEX function that will return the names serially from the array by using the output of the FILES and ROW Note that we used ROW()-7, as we started from the 8th row onwards. So ROW()-7 will be 1 for the first name, 2 for the second name, and so on.
The IFERROR function will return the original output if it doesn’t encounter an error and will return a blank if it encounters one.

Use the Fill Handle tool to copy the formula.

All the file names are now available in the sheet.

Method 3 – Using Power Query to Copy File Names from a Folder

Steps:

Go to Data, choose Get Data, select From File, and pick From Folder.

Find the folder and press Open.

You will get a window like the image below. Click Transform Data.

You’ll get the Power Query window. It will have different columns with data. We’ll keep only the name column.

Select all the column headings except the name column.
Right-click your mouse and select Remove Columns from the context menu.

Press Close & Load.

A new sheet will open with the folder name as a sheet name.

Method 4 – Run VBA Code to Copy File Names from a Specific Folder

Case 4.1 – Copy All File Names

Steps:

Press Alt + F11 to open the VBA window.
Click Insert and select Module to open a new module.

Insert the following code:

Function GetNames(ByVal FolderPath As String) As Variant
Dim mitResult As Variant
Dim i As Integer
Dim mitFile As Object
Dim mitFSO As Object
Dim mitFolder As Object
Dim mitFiles As Object
Set mitFSO = CreateObject("Scripting.FileSystemObject")
Set mitFolder = mitFSO.GetFolder(FolderPath)
Set mitFiles = mitFolder.Files
ReDim mitResult(1 To mitFiles.Count)
i = 1
For Each mitFile In mitFiles
mitResult(i) = mitFile.Name
i = i + 1
Next mitFile
GetNames = mitResult
End Function

Save the file.
Go back to your sheet.

Paste the file path in a cell. We put it in Cell B5.

Use the following formula in Cell B8 and press Enter–

=IFERROR(INDEX(GetNames($B$5),ROW()-7),"")

Use the Fill Handle tool for copying the formula down.

Here are all the file names.

Case 4.2 – Copy All File Names with a Specific Extension

Steps:

Follow the first two steps from the previous section to open a new module.
Insert the following code in the module:

Function GetNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant
Dim mitResult As Variant
Dim i As Integer
Dim mitFile As Object
Dim mitFSO As Object
Dim mitFolder As Object
Dim mitFiles As Object
Set mitFSO = CreateObject("Scripting.FileSystemObject")
Set mitFolder = mitFSO.GetFolder(FolderPath)
Set mitFiles = mitFolder.Files
ReDim mitResult(1 To mitFiles.Count)
i = 1
For Each mitFile In mitFiles
If InStr(1, mitFile.Name, FileExt) <> 0 Then
mitResult(i) = mitFile.Name
i = i + 1
End If
Next mitFile
ReDim Preserve mitResult(1 To i - 1)
GetNamesbyExt = mitResult
End Function