List file name only

[Otto Moehrbach]

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

 

[Gary Keramidas]

this will list all of the excel files in the path of the current workbook in
column A

Sub GetFileList()
Dim ThePath As String
ThePath = ThisWorkbook.Path
fname = Dir(ThePath & "\*.xls")
i = 1
Do While fname <> ""
Range("A" & i) = fname
fname = Dir()
i = i + 1
Loop
End Sub

 

[Otto Moehrbach]

Thanks Gary, I'll give that a try. Otto

this will list all of the excel files in the path of the current workbook
in column A

Sub GetFileList()
Dim ThePath As String
ThePath = ThisWorkbook.Path
fname = Dir(ThePath & "\*.xls")
i = 1
Do While fname <> ""
Range("A" & i) = fname
fname = Dir()
i = i + 1
Loop
End Sub

 

[Rick Rothstein \(MVP - VB\)]

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

The following keeps the approach you attempted above...

Sub GetFileList()
Dim c As Integer
Dim Dest As Range
Set Dest = Range("a1")
With Application.FileSearch
.NewSearch
.LookIn = "c:\temp"
.FileType = msoFileTypeAllFiles
.Execute
For c = 1 To .FoundFiles.Count
Dest.Value = Mid$(.FoundFiles(c), _
InStrRev(.FoundFiles(c), "\") + 1)
Set Dest = Dest.Offset(1)
Next c
End With
End Sub

Rick

 

[Dave Peterson]

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.