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

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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?


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
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.
    Balaji · 3 months ago
    Another easy method is there. Just copy the directory path where you have saved the documents, and paste that link in browser (chrome/mozilla). You will get the name list and just copy paste into excel. Cheers!
  • To post as a guest, your comment is unpublished.
    amit kuamr sahu · 4 months ago
    i want to add table,only folder name,and data modified as well?
    • To post as a guest, your comment is unpublished.
      skyyang · 19 days ago
      Hello,
      Do you want to list all folder names from a specific main folder? Please give your problem more detailed.
  • To post as a guest, your comment is unpublished.
    amit kumar sahu · 4 months ago
    i want to mention only folder name.what is vba code for that.
  • To post as a guest, your comment is unpublished.
    lu · 5 months ago
    is there a way to put the folder path already in the code?
  • To post as a guest, your comment is unpublished.
    J · 11 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 · 10 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 · 1 years ago
    Thanks. It was awesome
  • To post as a guest, your comment is unpublished.
    dimas · 1 years 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 · 1 years ago
    Apakah bisa merename file dengan excel?
    terimakasih sangat membantu perkerjaan saya,
  • To post as a guest, your comment is unpublished.
    Carrin · 1 years ago
    Very helpful, thank you!
  • To post as a guest, your comment is unpublished.
    Myk · 1 years ago
    Hey,
    I have a ecxel sheet which have some product names, and also have a folder which have some pdf files named same as in cell data, like if cell A2 value is apple1, Pdf file name is apple1.pdf, i want to know which name file is missing, can we get that in excel somwhow..
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello,Myk,
      First, you can apply the above code to insert the filenames into the worksheet, list the two columns of filenames into one worksheet, and then you can use the Select Same & Different cells feature of Kutools to identify and highlight the missing filenames, see screenshot:

      Please try it, thank you!