Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial


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 20150909
    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:

doc import filenames 1

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

doc import filenames 2

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.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. 

After installing Kutools for Excel, please do as follows:

1. Click Enterprise > Import / Export > Filename List, see screenshot:

doc import filenames 3

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

(1.) Click doc import filenames 5 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.

doc import filenames 4

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:

doc import filenames 6

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.

Click to know more about this Filename List feature…

Download and free trial Kutools for Excel Now !


Demo: Import multiple file names into worksheet cells with Kutools for Excel

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial 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?


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

  • Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Being used by 110,000 elites and 300+ well-known companies.

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    J · 4 months ago
    For the VBA code, how would you pull in the date modified as well?
    • To post as a guest, your comment is unpublished.
      skyyang · 4 months ago
      Hello,
      Add the date modified column for the imported filenames, please apply the following VBA code, please try it, hope it can help you!

      Sub GetFileList()
      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"
      ActiveSheet.Cells(1, 4) = "Date last modified"
      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)
      ActiveSheet.Cells(i, 4) = CDate(xFile.datelastmodified)
      Next
      End Sub
  • To post as a guest, your comment is unpublished.
    kaka · 5 months ago
    Thanks. It was awesome
  • To post as a guest, your comment is unpublished.
    dimas · 8 months ago
    saya coba yg pake vba di run bisa tapi pas di step browser nya bukan oke tapi open terus sampai ke file tertentu dan ketika di klik malah run time 26 path not found bisa bantu kenapa itu bisa terjadi?
  • To post as a guest, your comment is unpublished.
    agiltriyasmoko · 10 months ago
    Apakah bisa merename file dengan excel?
    terimakasih sangat membantu perkerjaan saya,
  • To post as a guest, your comment is unpublished.
    Carrin · 11 months ago
    Very helpful, thank you!