File-search results in worksheet
Check back tomorow
--
Gary''s Student
"hmm" wrote:
Thanks Gary; it works. The trick is that it will only search folders that
are offspring of the Excel file's folder.
Can it be implemented in the form of a function, so that I can insert it in
a table?
"Gary''s Student" wrote:
the routine looks only on the C drive. I tried putting
arte
in A1 and got stuff like:
C:\Documents and Settings\All Users\Application Data\Webroot\Spy
Sweeper\SpySweeperGettingStarted.chm
C:\Documents and Settings\All Users\Desktop\Adobe Photoshop Album Starter
Edition 3.0.lnk
C:\Documents and Settings\All Users\Start Menu\Programs\Adobe\Adobe
Photoshop Album Starter Edition 3.0.lnk
C:\Documents and Settings\All Users\Start
Menu\Programs\Creative\Documentation\Getting Started Manual.lnk
C:\Documents and Settings\All Users\Start Menu\Programs\EPSON\Film
Factory\EPSON PhotoStarter3.0.lnk
C:\Documents and Settings\All Users\Start Menu\Programs\EPSON\Film
Factory\Getting Started Guide.lnk
C:\Documents and Settings\All Users\Start
Menu\Programs\Games\GameChannel\Blackhawk Striker\Help.lnk
--
Gary''s Student
"hmm" wrote:
Thanks GS.
I tried your macro. It showed "There were no files found," even with
strings for which I am sure a file exists (it would work from Windows
search). In debug, the program skipped the "Then" and went to the "Else"; as
if .Execute() was always zero. Watch values of s and s2 were as expected.
Perhaps there is an error somewhere?
"Gary''s Student" wrote:
Put your string in cell A1 and run:
Sub ListFiles()
Dim s As String
s = Range("A1").Value
s2 = "*" & s & "*.*"
With Application.FileSearch
.NewSearch
.LookIn = "C:"
.SearchSubFolders = True
.Filename = s2
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(i + 1, 1).Value = _
.FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
1. the code is adapted from a Tom Ogilvy posting
2. it takes a long time to run (at least on my old computer)
--
Gary's Student
"hmm" wrote:
I need a formula for Excel whose input is a string, and whose output is a
list of path\filename found for filenames containing the string.
|