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.
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.
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 30 days.|
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.