Solved: List only the filename

[protegeone]

Hi Everyone :hi: ,

Still a beginner, ( not giving up though )..
The below code works fine, but could not make it list only the name of the found file(s).

Ideally the result should only be the name of the file, without the extension and path.

Private Sub listfiles()
Set pa = Application.FileSearch
With pa
.LookIn = "C:\Downloads\Bucsai"
.SearchSubFolders = True
.Filename = "*.pdf"

If .Execute(SortBy:=msoSortByFileName) = 1 Then

For i = 1 To .FoundFiles.Count
Range("A500").End(xlUp).Offset(1, 0).Value = .FoundFiles(i)
Next i

ElseIf .Execute(SortBy:=msoSortByFileName) > 1 Then

For i = 1 To .FoundFiles.Count
Range("A500").End(xlUp).Offset(1, 0).Value = .FoundFiles(i)
Next i

Else

End If
End With
End Sub


Thanks for your help

[XLGibbs]

http://vbaexpress.com/kb/getarticle.php?kb_id=837

Check my KB entry for either a working sample, or for reference to the code you are looking for to parse out the name.

I hope it helps.

[Bob Phillips]

Private Sub listfiles()
Dim ary
Set pa = Application.FileSearch
With pa
.LookIn = "C:\myTest" '"C:\Downloads\Bucsai"
.SearchSubFolders = True
.Filename = "*.pdf"

If .Execute(SortBy:=msoSortByFileName) > 0 Then

For i = 1 To .FoundFiles.Count
ary = Split(.FoundFiles(i), "\")
Range("A500").End(xlUp).Offset(1, 0).Value = Left(ary(UBound(ary)), InStr(ary(UBound(ary)), ".") - 1)
Next i

End If

End With
End Sub

[protegeone]

Thanks for the help :bow: