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.

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.

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.

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.

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.

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.

Gary,

How would I modify the program to make it search by title, instead of
filename?

"Gary''s Student" wrote:

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.

Another strange thing: I keep a shortcut, to the Excel file containing the
macro, in a folder on my desktop. When the Excel file containing the macro
is opened directly from its own folder, the macro has worked fine. But if I
open it using the shortcut, the macro will not find files every time (i.e.,
sometimes yes, sometimes no).

Apparently, in some cases the macro obtains the information about the
working folder from the folder from which the workbook or shortcut was
opened, and will only search the at folder level and below.

Do you know whether or not this is the source of this bizarre problem? And
if it is, is there any way around it, so that the macro will at least begin
its search at the actual folder in which its its Excel file resides? (This
is besides the problem that it will not start at the top, C:, as specified in
the code, which I can deal with.)



"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.

Yes.

We can build on what we have. ListFiles creates a list of files in the
worksheet. Each file may or may not have a title.

Once that list has been created, we can use Pearson's
DSOFile.OleDocumentProperties
code to get the properties (like Title) without opening each file.
Pearson's code can be found at:

http://www.cpearson.com/excel/docprop.htm

I tried this out:

Sub surface()
Dim FileName As String
Dim DSO As DSOFile.OleDocumentProperties
Set DSO = New DSOFile.OleDocumentProperties
FileName = "C:\a.xls"
DSO.Open sfilename:=FileName
Debug.Print DSO.SummaryProperties.ApplicationName
Debug.Print DSO.SummaryProperties.Author
MsgBox (DSO.SummaryProperties.Title)
' lots of other properties
DSO.Close


End Sub


and it worked. Follow his instructions:

first, download the reference from the Microsoft Site and install it
second, activate it with Tools References in the VBE window
--
Gary's Student


"hmm" wrote:

Gary,

How would I modify the program to make it search by title, instead of
filename?

"Gary''s Student" wrote:

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.