How to Import Multiple File Names into Cells in Excel

This post will guide you how to import file names only into your current worksheet throught VBA code and Power Query. When working with large amounts of data, it is often useful to import only the file names, rather than the file contents. This can be achieved using either VBA code or Power Query.

Import Multiple File Names into Cells with Power Query

You can use Power Query to import file names into Excel. Here are the steps:

Step1: Open a new or existing Excel workbook. Click on the “Data” tab in the Excel ribbon. And click on the “Get Data” option in the “Get & Transform Data” section and choose “From File“, then choose “From Folder” in the dropdown menu.

Step2: Browse and select the folder that contains the files you want to import. Click Open button.

Step3: Click on the “Transform Data” button to open Power Query Editor.

Step4: In Power Query Editor, select the Name column and right click on it. then click on “Remove Other Columns”.

Step5: Once you have finished editing the data, click on the “Close & Load” button to import the data into a new worksheet.

Step6: you can see that the only file names would be imported successfully.

Import Multiple File Names into Cells with VBA Code

You can also use VBA code to import file names into Excel. Just do the following steps:

Step1: Open a new or existing Excel workbook.

Step2: Press “Alt + F11” shortcut to open the Microsoft VBA Editor.

Step3: Insert a new module by clicking “Insert” > “Module“.

Step4: Copy and paste the below code into the module window.

Sub ImportFileNames_excelgeek()

    Dim MyFolder As String
    Dim MyFile As String
    Dim i As Integer

       ' Prompt the user to select a folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select the folder that contains the files you want to import"
        .Show
        If .SelectedItems.Count > 0 Then
            MyFolder = .SelectedItems(1)
        Else
            MsgBox "No folder selected.", vbExclamation, "Error"
            Exit Sub
        End If
    End With

    ' Get the first file in the folder
    MyFile = Dir(MyFolder & "\*.*")

    ' Loop through all files in the folder
    Do While MyFile <> ""
        ' Increment the row counter
        i = i + 1
        ' Insert the file name in the cell
        Cells(i, 1).Value = MyFile
        ' Get the next file in the folder
        MyFile = Dir
    Loop
End Sub

Step5: Run the code by clicking “Run” > “Run Sub/UserForm” or by pressing “F5“.

Step6: Select the folder that contains the files you want to import. Such as: mergeFolder.

Step7: The code will loop through all the files in the specified folder and insert the file names in the first column of the active worksheet.

Conclusion

Importing file names can be a useful way to manage large amounts of data in Excel. Whether you prefer to use VBA code or Power Query, both options provide a way to automate the process and import file names quickly and efficiently.