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