code

Sub ListarDocumentos()

Dim caminhoPasta As String
Dim FSO As New FileSystemObject
Dim NomeArq As String, filepath As String
Dim myFolder As Folder
Dim myFile As File

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


caminhoPasta = ThisWorkbook.Worksheets(1).Cells(2, 5).Value

Range("A2:B200").ClearContents

Set myFolder = FSO.GetFolder(caminhoPasta) 'Comando para abrir a pasta no caminho definido em myPath

i = 2
For Each myFile In myFolder.Files 'Loop que pega cada arquivo contido na pasta
        NomeArq = myFile.Name
        ThisWorkbook.Worksheets(1).Cells(i, 1).Value = NomeArq
        ThisWorkbook.Worksheets(1).Cells(i, 2).Value = NomeArq
        i = i + 1
    Next
    
    
End Sub