FoundFiles

[Sprinks]

Is there a way, short of string manipulation functions to return the filename
only of a FoundFiles.Item ? I will be loading filenames into a List box
without their path. The current state of the code merely lists them in a
MsgBox, after removing the folder path selected by the GetFolderName function
(which uses Application.FileDialog). This method, however, will not remove
the remainder of the path from files in subfolders of the main folder.

Thank you for any assistance.

Sprinks

' Dimension variables.
Dim myarray()
Dim fs As Object
Dim i As Integer
Dim strMsg As String

' Declare filesearch object.
Set fs = Application.FileSearch

' Set folder to search.
With fs
.SearchSubFolders = True
.LookIn = GetFolderName()
End With

If fs.LookIn = "" Then
' User pressed Cancel
Exit Sub
End If

' Set file name to search for.
fs.Filename = "*.xls"

' Execute the file search, and check to see if the file(s) are
' present.
If fs.Execute > 0 Then

' Redimension the array to the number of files found.
ReDim myarray(fs.FoundFiles.Count)

' Loop through all found file names and fill the array.
For i = 1 To fs.FoundFiles.Count
myarray(i) = fs.FoundFiles(i)
Next i
Else
' Display message if no files were found.
MsgBox "No files were found"
End If

strMsg = ""
For i = 1 To fs.FoundFiles.Count
strMsg = strMsg & Mid(fs.FoundFiles.Item(i), Len(fs.LookIn) + 2) &
vbCrLf
Next i

MsgBox strMsg

 

[Tom Ogilvy]

You can use the split function

v = split(.foundfiles(i),"\")
fname = v(ubound(v))

 

[Sprinks]

Great! Thanks, Tom.

Sprinks

You can use the split function

v = split(.foundfiles(i),"\")
fname = v(ubound(v))