Get FileName list form a Folder in EXCEL

The code below retrieves the file in this directory and creates a list of their names and paths:

Sub GetFileNameList()

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Get the folder object
Set objFolder = objFSO.GetFolder("C:\TMP")

i = 1
'loops through each file in the directory and prints their names and path

For Each objFile In objFolder.Files

    'print file name
    Cells(i + 1, 1) = objFile.Name

    'print file path
    Cells(i + 1, 2) = objFile.path
    i = i + 1

Next objFile

End Sub