Importing List of file into excel worksheet is very popular task in several excel automation.
Such a list of files will empower user to work on file listed in worksheet. User can perform various calculation on these listed files as per requirement.

Please watch below video for detailed explanation:


Here is the VBA code to list files in a folder in a worksheet:


'Force the explicit delcaration of variables

Option Explicit

Sub List_AllFiles()

' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com

Dim rCntr As Long
Dim fPath As String
Dim fName As String
Dim rt_folder As String

' Below block of code take folder input from user

' Then based on folder path input it list all files in activesheet

rt_folder = "C:\"


    With Application.FileDialog(msoFileDialogFolderPicker)

        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list Files from"
        .InitialFileName = rt_folder
        .Show
            
        If .SelectedItems.Count <> 0 Then
            fPath = .SelectedItems(1) & "\"
            fName = Dir(fPath, 7)
            Do While fName <> ""
            ActiveCell.Offset(rCntr) = fName
            rCntr = rCntr + 1
            fName = Dir
            Loop
        End If
        
    End With
    
MsgBox "All File listed in sheet...", vbInformation, Title:="List all Files"

End Sub