File names in a directory

  • Thread starter Thread starter ianripping
  • Start date Start date
Try the following. It is not fully tested and contains no error-handling:
It's written to list the files from a floppy but you can change that to your
folder. From Vasant. HTH Otto

Sub CatalogFloppies()
Do Until MsgBox("Any more floppies?", vbYesNo) = vbNo
MsgBox "Insert floppy disk and click OK."
Dim i As Long
With Application.FileSearch
.NewSearch
.LookIn = "A:\"
.SearchSubFolders = True
.FileType = msoFileTypeAllFiles
If .Execute > 1 Then
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(Rows.Count, 1). _
End(xlUp).Offset(1, 0) = .FoundFiles(i)
Next i
End If
End With
Loop
End Sub
 
I like (Putting the below in a Standard Module):
Be in a Blank Worksheet before running the code:
The code will list downward from A1: A50+ the Full path
In Cell C1 I enter =MID(A1,21,255) and copy down the 21 trims off the
beginning "C:\My Documents\Temp\" (21 in this case)
to give you the pure file names.

Sub ListfilesInDirectory()
Set fs = Application.FileSearch
With fs
..LookIn = "C:\My Documents\Temp" '<<< Change to Suit your needs
..Filename = "*.xls"
If .Execute > 0 Then
ActiveCell = Range("A1")
For I = 1 To .FoundFiles.Count
ActiveCell.FormulaR1C1 = .FoundFiles(I)
ActiveCell.Offset(1, 0).Select
Next I
Else
End If
End With
End Sub

HTH
 
Back
Top