View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default 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