Copy all file names in a directory into excel

P

Peo Sjoblom

Here's a macro by Tom Ogilvy


Sub ListFiles()
With Application.FileSearch
.NewSearch
.LookIn = "C:\MyFolder"
.SearchSubFolders = False
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(i + 1, 1).Value = _
.FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
D

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
 
Top