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

Do you have a lot of files in your file explorer to copy the names in an Excel worksheet and searching ways to avoid manually copying them? Then we think, you have visited the right place. We’ll show the best 4 methods to copy file names from a folder to Excel.

How to Copy File Names from Folder to Excel: 4 Effective Ways

Here’s our sample dataset which contains 5 files in a folder named ‘ExcelDemy’.

1. Using Shortcut Keys to Copy File Names from Folder

First, we’ll learn the easiest method to copy file names from a folder to Excel. Nothing, we’ll just use the common copying shortcut keys Ctrl + C.

Steps:

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

• Later, go to your Excel worksheet, select a cell, and press Ctrl + V. We selected Cell B5.

Now our target is to remove everything except the File name. For that, we’ll use the Find and Replace tool.

• Select all the copied values.
• Next, press Ctrl + H to open the Find and Replace tool.
• Type part of the address before the file name on the Find what box.
• Keep the Replace with box empty.
• Finally, just press Replace All.

Now look, there are remaining only the file names.

Read More: How to Get Filename from Path in Excel

2. Copy File Names from Folder by Using FILES Function

We can copy file names from a folder to Excel by using a function too. The FILES function can be used for that, but it is not a worksheet function. It only works with named ranges.

Steps:

• First, copy the file path in a cell with an asterisk at the end. We coped in Cell B5. This cell reference will be used in the named range.

• Then click on Define Name from the Formulas ribbon.

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

• After that, insert the following formula in Cell B8–

• Hit the Enter button.

• Lastly, use the Fill Handle tool to copy the formula.

Now, all the file names are available in the sheet.

Read More: How to Rename Files in a Folder in Excel

3. Using Power Query to Copy File Names from Folder

If you want to avoid formulas then there is another exclusive way to copy file names from a folder to Excel. The Excel Power Query is the tool that can do it.

Steps:

• Firstly, click as follows: Data > Get Data > From File > From Folder.

• Browse the folder and press Open.

• After a while, you will get a window like the image below. Just click Transform Data.

Then we’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.
• Then right-click your mouse and select Remove Columns from the context menu.

• Finally, just press Close & Load.

A few moments later, a new sheet will open up with the folder name as a sheet name.

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

Using VBA is the quickest way to copy file names. We’ll make a User Defined Function (UDF) and then use it for the path address.

4.1 Copy All File Names

Our first  User Defined Function (UDF) function will copy all the file names of every extension. The function name will be Get Names.

Steps:

• Press Alt + F11 to open the VBA window.
• Then click Insert > Module to open a new module.

• Then type the following codes-

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

• Next, go back to your sheet.

• Later, copy the file path in a cell with an. We coped in Cell B5.

• Now our function is ready, it has only one argument. write the following formula in Cell B8 and press Enter–

• Finally, use the Fill Handle tool for copying the formula.

Here are all the file names.

4.2 Copy All File Names with a Specific Extension

Now we’ll make a function- GetNamesbyExt that will have two arguments- file path and extension. So it will return the file names according to the extension name.

Steps:

• Follow the first two steps from the previous section to open a new module.
• Insert the following codes in a new 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

• Next, go back to your sheet.

• Copy the file path in a cell and insert the extension name in another cell.

• Then type the following formula in Cell B10 and press Enter–

• Use Fill Handle to finish.

It gives the file names with xlsx extension.

Our folder has doc files too, insert docx in Cell B7 and it will return the doc file names.

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.

Conclusion

That’s all for the article. I hope the above procedures will be good enough to copy file names from a folder to Excel. Feel free to ask any question in the comment section and please give me feedback.

<< Go Back to Excel File Name | Excel Files | Learn Excel