How do I list all file names in a directory

[Dave]

I am looking for a macro that first prompts the user to
select a directory and then lists all file names from that
directory and all sub directories in an excel column.

Thanks for any help

 

[Jim Cone]

Dave,

There are eleven pages of "macros" in my Excel add-in "List Files".
However the code is protected...
It finds files in a drive, directory or folder meeting criteria you specify.
It generates a list on a new Excel worksheet showing...
file location, name, type, size and last saved date.
A hyperlink is created for each file.
It is very easy to use and comes with a one page Word.doc install/use file.
It is available - free - upon direct request.
Remove xxx from my email address.

Regards,
Jim Cone
San Francisco, CA
(e-mail address removed)

 

[Gord Dibben]

Tushar Mehta has a directory listing add-in on his site.

Free for downloading.

http://www.tushar-mehta.com/excel/software/dirlist/index.html

Gord Dibben Excel MVP

 

[Don Guillett]

modify to suit
Sub anotherfindfiles()
Application.ScreenUpdating = False
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim MyFileLocation As String
MyFileLocation = "c:\yourfolder\*.xls"
FN = Dir(MyFileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub