How to Get List of File Names in a Folder using Excel

Microsoft Excel is a powerful program when it comes to automation. If you have a list of files in a folder and want to copy in Excel then doing it manually could take ages. 

Luckily, Excel can access files and folders in your system. You can use it to get all the information that you want such as file path, file name, MiMe type, and file size.

Then you can use its functions to sort and delete duplicates or rename them all at once. In this article, I will show you step-by-step how to get a list of file names in a folder using Excel.

Using Power Query

The easiest way to get the list of file names in Excel without relying on Macros or Addins is by using the Get and Transform feature. This feature allows you to get information such as: 

• File name
• Extension
• Date accessed
• Date modified
• Date created
• Folder path

Here are the steps:

1. Open the Excel file where you want to copy the file names

2. Click on the Data Tab> New Query under the Get and Transform Group

3. Next click on From File > From Folder

Note that the above steps may vary depending on the Excel version that you are using. But basically, you can get all these options under the get and transform group 

Once you click from the folder you will get a dialogue box like the one below

4. You can either paste your folder path here and click okay or click on browse option and select the folder from the file  explorer

5. Once you select the folder, Excel will show you a preview of the files in the folder and how it will look when you load it.

Note that the list displayed here is just some of the files.

6. To get the full list you need to click Load

That’s simply it now you can edit to remove columns that you don’t need and so on. 

Use VBA Code

You can also use VBA Code to get the list of file names from an Excel folder and other relevant information. Apart from just loading the names, you can also use VBA code to rename files in a folder. 

This proves that VBA code can be very useful when it comes to organizing files on a server or on your computer.

The following VBA code allows you to paste the folder path and then select the Extension types of only the files you want to appear. 

Here are the steps to get file names. 

1. Open your Excel file if you haven’t already

2. Open the VBA editor by clicking the Developer tab> Visual Basic. Or use the shortcut Alt+F11 for Windows and Opt +F11 on Mac

3. On the VBA editor click on insert>module

4. Copy and paste the code below

Option Explicit

Sub GetFileNamesInFolder()
    Dim folderPath As String
    Dim extensionType As String
    Dim ws As Worksheet
    Dim fileName As String
    Dim rowCounter As Long

    ' Prompt user for folder path
    folderPath = InputBox("Enter the folder path:")

    ' Check if folder path is provided
    If folderPath = "" Then
        MsgBox "Please enter a valid folder path.", vbExclamation
        Exit Sub
    End If

    ' Prompt user for extension type or choose all extensions
    extensionType = InputBox("Enter the extension type (e.g., xls) or type 'all' for all extensions:")

    ' Set reference to the active sheet
    Set ws = ActiveSheet

    ' Clear existing data in the first column of the active sheet
    ws.Columns(1).Clear

    ' Initialize row counter
    rowCounter = 1

    ' List file names in the first column of the active sheet
    If extensionType = "all" Then
        fileName = Dir(folderPath & "\*.*")
        Do While fileName <> ""
            ws.Cells(rowCounter, 1).Value = fileName
            rowCounter = rowCounter + 1
            fileName = Dir
        Loop
    Else
        fileName = Dir(folderPath & "\*." & extensionType)
        Do While fileName <> ""
            ws.Cells(rowCounter, 1).Value = fileName
            rowCounter = rowCounter + 1
            fileName = Dir
        Loop
    End If
End Sub

5. Run the code by clicking on the green play icon

6. You will get a pop-up prompting you to type the folder path. Paste the path to your folder and click ok

7. The next dialogue box will ask you to select the type of extension. Type all to list all the files or type each extension eg. docx, xlsx, pdf… and press ok 

8. The file names will be listed in the first columns of the active sheet in your Excel workbook

Use an Addin

Excel Addins add extra functionality or simplify existing functions and steps. Most add-ins provide the option to get file names from an Excel folder alongside other useful information such as extension and size. Let’s take for example using Kutools

1. Download and install Kutools from the official website

2. Open the Excel software and click on the Kutools Plus Tab

3. Next click on the Import and Export dropdown option then choose Filenamelist

4. Click the folder icon to browse the folder you want to list of files. Or you can paste the path directly 

5. Under File Types, you can select all files or choose only the extension formats that you wish. 

6. Finally click okay and the files will be listed

Use CMD

1. Open the folder containing the files you want to copy to Excel

2. On the address bar, delete the path and type CMD then Press Enter

This will launch CMD in the current file path

3. Type the following command and press enter

dir/ b

All the file names in the current folder will be listed including their extension

4. Highlight all the names using your mouse and copy (Note that you will not see any action but the files will be copied)

5. Launch your Excel file and paste the file names.

6. Now you can sort, filter, or separate the extension type using formula and flash fill as you like

Final Thoughts

Excel can be a very useful tool when it comes to organizing your files on a server or your computer system. You can easily get the file names in Excel using the above method. Apart from just listing the file names, you can use Excel to delete, modify, or even rename files.