How to import multiple file names into cells in Excel?

Supposing you have a folder with hundreds of files, and now, you want to import these file names into cells of a worksheet. Copying and pasting one by one will spend much time, in this article, I will talk about some quick tricks to help you import multiple file names from a folder into a worksheet.

Import multiple file names into worksheet cells with VBA code

Import multiple file names into worksheet cells with Kutools for Excel

Import multiple file names into worksheet cells with VBA code

The following VBA code can help you import the file names, file extensions and folder name into the worksheet cells, please do with following steps:

1. Launch a new worksheet that you want to import the file names.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Import multiple file names into cells of worksheet

Sub GetFileList()
'updateby Extendoffice
    Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xFiDialog As FileDialog
    Dim xPath As String
    Dim i As Integer
    Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
    If xFiDialog.Show = -1 Then
        xPath = xFiDialog.SelectedItems(1)
    End If
    Set xFiDialog = Nothing
    If xPath = "" Then Exit Sub
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFSO.GetFolder(xPath)
    ActiveSheet.Cells(1, 1) = "Folder name"
    ActiveSheet.Cells(1, 2) = "File name"
    ActiveSheet.Cells(1, 3) = "File extension"
    i = 1
    For Each xFile In xFolder.Files
        i = i + 1
        ActiveSheet.Cells(i, 1) = xPath
        ActiveSheet.Cells(i, 2) = Left(xFile.Name, InStrRev(xFile.Name, ".") - 1)
        ActiveSheet.Cells(i, 3) = Mid(xFile.Name, InStrRev(xFile.Name, ".") + 1)
    Next
End Sub

4. Then press F5 key to execute this code, and in the popped out Browse window, choose the folder that you want to import the file names form, see screenshot:

5. And then click OK button, and you will get the following result:

Note：If there are subfolders within your specific folder, the file names in the subfolders will not be imported.

Import multiple file names into worksheet cells with Kutools for Excel

If you need to import the file names both in the folder and the subfolders, please don’t worry, with Kutools for Excel’s Filename List utility, you can easily import all the file names in the specific folder including the subfolders.

After installing Kutools for Excel, please do as follows:

1. Click Kutools Plus> Import & Export > Filename List, see screenshot:

2. In the Filename List dialog box, do the following operations:

(1.) Click button to select the folder with the files you want to import;

(2.) Check Include files in subdirectories to import the filenames of the subfolders;

(3.) Specify the files type you want to import under the Files type section;

(4.) Select one file size unit you want to display from the File size unit section;

(5.) If you want to hyperlink the filenames and folders, please check Create hyperlinks option.

3. After finishing the settings, please click OK button, and the filenames both in folder and subfolders are imported into a new worksheet as following screenshot shown:

Tips: If you just want to import one specified type file names from specific folder, you can check Specify option in the Filename List dialog, and type the file extension into it, then it will only import the specified type file names from both folder and subfolders.

Download and free trial Kutools for Excel Now !

Related articles:

How to list all files in folder and subfolders into a worksheet?

How to list all filenames in a folder and create hyperlinks for them in Excel?