List file name only
Option Explicit
Sub GetFileList()
Dim Dest As Range
Dim c As Long
Dim ThePath As String
Dim myName As String
Set Dest = ActiveSheet.Range("a1")
ThePath = "C:\my documents\excel\"
With Application.FileSearch
.NewSearch
.LookIn = ThePath
For c = 1 To .Execute
myName = Mid(.FoundFiles(c), InStrRev(.FoundFiles(c), "\") +
1)
Dest.Value = myName
Set Dest = Dest.Offset(1)
Next c
End With
End Sub
instrrev was added in xl2k.
Otto Moehrbach wrote:
Excel XP, Win XP
I want get a listing of all the files in a specific folder (ThePath). The
following code does this but the listing includes the path before every file
name. How do I change this code to give only the file names, for instance,
FileName.xls.
Thanks for your time. Otto
Sub GetFileList()
Dim c As Integer
With Application.FileSearch
.NewSearch
.LookIn = ThePath
For c = 1 To .Execute
Dest.Value = .FoundFiles(c)
Set Dest = Dest.Offset(1)
End If
Next c
End With
End Sub
--
Dave Peterson
|