Search Excel files for text and keywords?

I am using Office 2003 on Windows XP.

You can MANUALLY search closed Excel files and determine which ones contain
certain text and/or key words. This can be performed from Windows Explorer,
but also from inside Excel, through the "File-Open" dialog box. Click Tools
then Search. This is a useful tool to help identify or pinpoint specific
files.

The macro recorder does not capture these keystrokes.

QUESTION: Is it possible to perform such a search programmatically? If so,
could someone please post example code?

Thanks much in advance for your assistance.

quartz:

reference Application.FileSearch object

try,

With Application.FileSearch
.NewSearch
'Search Your Path
.LookIn = "C:\My Documents"
.SearchSubFolders = True
'key words
.TextOrProperty = "jan*"
.FileType = msoFileTypeExcelWorkbooks
.Execute
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
'Your Code
Next i
Else
MsgBox "There were no files found."
End If
End With


--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"quartz" wrote:

> I am using Office 2003 on Windows XP.
>
> You can MANUALLY search closed Excel files and determine which ones contain
> certain text and/or key words. This can be performed from Windows Explorer,
> but also from inside Excel, through the "File-Open" dialog box. Click Tools
> then Search. This is a useful tool to help identify or pinpoint specific
> files.
>
> The macro recorder does not capture these keystrokes.
>
> QUESTION: Is it possible to perform such a search programmatically? If so,
> could someone please post example code?
>
> Thanks much in advance for your assistance.
>

Thanks a lot!

Just one more question, how do you specify whether to search for "Text" or a
file "Property" or can you not specify this?

"chijanzen" wrote:

> quartz:
>
> reference Application.FileSearch object
>
> try,
>
> With Application.FileSearch
> .NewSearch
> 'Search Your Path
> .LookIn = "C:\My Documents"
> .SearchSubFolders = True
> 'key words
> .TextOrProperty = "jan*"
> .FileType = msoFileTypeExcelWorkbooks
> .Execute
> If .Execute() > 0 Then
> MsgBox "There were " & .FoundFiles.Count & _
> " file(s) found."
> For i = 1 To .FoundFiles.Count
> MsgBox .FoundFiles(i)
> 'Your Code
> Next i
> Else
> MsgBox "There were no files found."
> End If
> End With
>
>
> --
> 天行健，君�*�以自強不息
> 地勢坤，君�*�以厚德載物
>
> http://www.vba.com.tw/plog/
>
>
> "quartz" wrote:
>
> > I am using Office 2003 on Windows XP.
> >
> > You can MANUALLY search closed Excel files and determine which ones contain
> > certain text and/or key words. This can be performed from Windows Explorer,
> > but also from inside Excel, through the "File-Open" dialog box. Click Tools
> > then Search. This is a useful tool to help identify or pinpoint specific
> > files.
> >
> > The macro recorder does not capture these keystrokes.
> >
> > QUESTION: Is it possible to perform such a search programmatically? If so,
> > could someone please post example code?
> >
> > Thanks much in advance for your assistance.
> >

quartz:

'Search file
..Filename = "jan*.xls"
or .Filename = "jan*.txt"


'Search key words
..TextOrProperty = "San*"


'FileType
..FileType = msoFileTypeExcelWorkbooks

msoFileTypeAllFiles
msoFileTypeBinders
msoFileTypeCalendarItem
msoFileTypeContactItem
msoFileTypeCustom
msoFileTypeDatabases
msoFileTypeDataConnectionFiles
msoFileTypeDesignerFiles
msoFileTypeDocumentImagingFiles
msoFileTypeExcelWorkbooks
msoFileTypeJournalItem
msoFileTypeMailItem
msoFileTypeNoteItem
msoFileTypeOfficeFiles
msoFileTypeOutlookItems
msoFileTypePhotoDrawFiles
msoFileTypePowerPointPresentations
msoFileTypeProjectFiles
msoFileTypePublisherFiles
msoFileTypeTaskItem
msoFileTypeTemplates
msoFileTypeVisioFiles
msoFileTypeWebPages
msoFileTypeWordDocuments

--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"quartz" wrote:

> Thanks a lot!
>
> Just one more question, how do you specify whether to search for "Text" or a
> file "Property" or can you not specify this?
>
> "chijanzen" wrote:
>
> > quartz:
> >
> > reference Application.FileSearch object
> >
> > try,
> >
> > With Application.FileSearch
> > .NewSearch
> > 'Search Your Path
> > .LookIn = "C:\My Documents"
> > .SearchSubFolders = True
> > 'key words
> > .TextOrProperty = "jan*"
> > .FileType = msoFileTypeExcelWorkbooks
> > .Execute
> > If .Execute() > 0 Then
> > MsgBox "There were " & .FoundFiles.Count & _
> > " file(s) found."
> > For i = 1 To .FoundFiles.Count
> > MsgBox .FoundFiles(i)
> > 'Your Code
> > Next i
> > Else
> > MsgBox "There were no files found."
> > End If
> > End With
> >
> >
> > --
> > 天行健，君�*�以自強不息
> > 地勢坤，君�*�以厚德載物
> >
> > http://www.vba.com.tw/plog/
> >
> >
> > "quartz" wrote:
> >
> > > I am using Office 2003 on Windows XP.
> > >
> > > You can MANUALLY search closed Excel files and determine which ones contain
> > > certain text and/or key words. This can be performed from Windows Explorer,
> > > but also from inside Excel, through the "File-Open" dialog box. Click Tools
> > > then Search. This is a useful tool to help identify or pinpoint specific
> > > files.
> > >
> > > The macro recorder does not capture these keystrokes.
> > >
> > > QUESTION: Is it possible to perform such a search programmatically? If so,
> > > could someone please post example code?
> > >
> > > Thanks much in advance for your assistance.
> > >

OK,

But my files aren't on my local drive. They are on a network drive, so
.LookIn = "C:\My Documents" woun't work. Can I but there also the
server name and share catalog? The drive letter may vary from user to
user. Or should I research for the servers physical drive addresses, as
all the files are still in one place?