I have modified the code below from a reply to a question in 2007 from
Patrick Kirk. I am using MS Office Excel 2003.

For my use I would prefer the results within 2 possible scenarios;

1) a ListBox so that I may choose just see or select a file that was found
and proceed to work with it, such as open an XLS, if the file is an XLS file.
2) a MsgBox, but each file found must be displayed on separate lines within
the MsgBox

The difference for me too is that this IS NOT for a user form but rather a
macro that can be assigned to a button.

Suggestions?


Sub findFile()
Dim showThis As String
Dim i As Integer

Set Fs = Application.FileSearch
With Fs
'Change below to give the name of the Directory you want to search
.LookIn = "C:\Fun Stuff\Excel\"
.SearchSubFolders = True
.Filename = "msgbox"
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
showThis = showThis & " " & .FoundFiles(i)
'ListBox1.AddItem (.FoundFiles(i))
Next i
MsgBox .FoundFiles.Count & showThis
Else
MsgBox "No files found."
End If
End With
End Sub

I believe that your listbox would have to be on a userform, or you would have
to use a sheet form control or data validation list (it really all depends on
how this will be used, and I don't have enough info to suggest which might be
most appropriate).

If you just need to view the list but not select one, then msgbox would work
(although it may have a max string length, I've never checked). To get them
listed on individual lines, I use chr(13) out of habit, but I suspect you
could also use vbcrlf

showThis = showThis & chr(13) & .FoundFiles(i)

HTH,
Keith

"GEdwards" wrote:

I have modified the code below from a reply to a question in 2007 from
Patrick Kirk. I am using MS Office Excel 2003.

For my use I would prefer the results within 2 possible scenarios;

1) a ListBox so that I may choose just see or select a file that was found
and proceed to work with it, such as open an XLS, if the file is an XLS file.
2) a MsgBox, but each file found must be displayed on separate lines within
the MsgBox

The difference for me too is that this IS NOT for a user form but rather a
macro that can be assigned to a button.

Suggestions?


Sub findFile()
Dim showThis As String
Dim i As Integer

Set Fs = Application.FileSearch
With Fs
'Change below to give the name of the Directory you want to search
.LookIn = "C:\Fun Stuff\Excel\"
.SearchSubFolders = True
.Filename = "msgbox"
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
showThis = showThis & " " & .FoundFiles(i)
'ListBox1.AddItem (.FoundFiles(i))
Next i
MsgBox .FoundFiles.Count & showThis
Else
MsgBox "No files found."
End If
End With
End Sub

Thanks Keith, works great.

I tested the MsgBox and there is a restriction of 22 lines of text allowed.
I guess I now need to find a scrollable MsgBox, and I would still like the
idea of having a ListBox I could pick from.


"ker_01" wrote:

I believe that your listbox would have to be on a userform, or you would have
to use a sheet form control or data validation list (it really all depends on
how this will be used, and I don't have enough info to suggest which might be
most appropriate).

If you just need to view the list but not select one, then msgbox would work
(although it may have a max string length, I've never checked). To get them
listed on individual lines, I use chr(13) out of habit, but I suspect you
could also use vbcrlf

showThis = showThis & chr(13) & .FoundFiles(i)

HTH,
Keith

"GEdwards" wrote:

I have modified the code below from a reply to a question in 2007 from
Patrick Kirk. I am using MS Office Excel 2003.

For my use I would prefer the results within 2 possible scenarios;

1) a ListBox so that I may choose just see or select a file that was found
and proceed to work with it, such as open an XLS, if the file is an XLS file.
2) a MsgBox, but each file found must be displayed on separate lines within
the MsgBox

The difference for me too is that this IS NOT for a user form but rather a
macro that can be assigned to a button.

Suggestions?


Sub findFile()
Dim showThis As String
Dim i As Integer

Set Fs = Application.FileSearch
With Fs
'Change below to give the name of the Directory you want to search
.LookIn = "C:\Fun Stuff\Excel\"
.SearchSubFolders = True
.Filename = "msgbox"
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
showThis = showThis & " " & .FoundFiles(i)
'ListBox1.AddItem (.FoundFiles(i))
Next i
MsgBox .FoundFiles.Count & showThis
Else
MsgBox "No files found."
End If
End With
End Sub