Automatic file name list in Excel

[Paddy123]

Hi there, i once got shown an Excel sheet that had a button, that once clicked took you to file explorer, you then clicked on the file you wanted and it generated a list of the file names automatically. I am looking to recreate this but don't know where to start, everthing i google has a step where you have to copy and paste the files and then go from there, but i am looking for it to be done just from Excel. Can anyone help?

 

[JEC]

Don’t you mean click on a folder and then give you the list of all files(not only xls files) in this folder (and subfolders?)

 

[JEC]

Dim oFSO As Object, ar(2000, 0), x As Long

Sub jec()
 Set oFSO = CreateObject("Scripting.FileSystemObject")
 With Application.FileDialog(4)
   If .Show = 0 Then Exit Sub
   GetFiles .SelectedItems(1)
   If x Then Cells(2, 2).Resize(x) = ar
   Erase ar: x = 0
 End With
End Sub

Sub GetFiles(xFold)
 Dim Obj As Object
 If Right(xFold, 1) <> "\" Then xFold = xFold & "\"
 With oFSO.GetFolder(xFold)
    For Each Obj In .Files
       ar(x, 0) = Obj.Name
       x = x + 1
    Next
    For Each Obj In .SubFolders
       GetFiles xFold & Obj.Name
    Next
 End With
End Sub

 

[Paddy123]

Don’t you mean click on a folder and then give you the list of all files(not only xls files) in this folder (and subfolders?)

yes exactly, all file names in the folder and subfolder.

 

[JEC]

Ok, create a new module and paste this complete code. Then run macro jec and it should work as desired.

 

[Paddy123]

Ok, create a new module and paste this complete code. Then run macro jec and it should work as desired.

Thank you that is perfect!