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.

Search Excel files for text and keywords?
 

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.

Search Excel files for text and keywords?
 

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.

Search Excel files for text and keywords?
 

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.

Search Excel files for text and keywords?
 

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?