FileSearch.Execute does not find *.eml or *.lnk files
 

Hi friends,
Given below is a part of my macro. The method Execute in FileSearch
class does not find email file (*.eml) or shortcut files (*.lnk). It
would not be finding other types of files also, but these two are the
ones which i tested failed. Other files like *.doc, *.xls are working
for the FileSearch. Please help.

Set fs = Application.FileSearch
With fs
.LookIn = sLookin
sFileTypeChoice = InputBox("What files are to be searched? Ex.
'doc' for word files", "File Type Choice")
If sFileTypeChoice = "" Then .Filename = "*.*" Else .Filename =
"*." & sFileTypeChoice
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Range("B" & (i + 2)).Select
Range("B" & (i + 2)).Hyperlinks.Add Anchor:=Selection,
Address:=.FoundFiles(i), TextToDisplay:=.FoundFiles(i)
Selection.Replace What:=sLookin & "\", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
Next i
Range("A1").Value = "Files Found: " & .FoundFiles.Count & "
Location: " & sLookin
Else
MsgBox "There were no files found."
Exit Sub
End If
End With

FileSearch.Execute does not find *.eml or *.lnk files
 

RosH,
Can't say why those 2 extension should be ignored, but the whole .FileSearch
in Office seems flakey to say the least, in which files it decides fits your
criteria.
Dir() is much more reliable.

NickHk

"RosH" wrote in message
ups.com...
Hi friends,
Given below is a part of my macro. The method Execute in FileSearch
class does not find email file (*.eml) or shortcut files (*.lnk). It
would not be finding other types of files also, but these two are the
ones which i tested failed. Other files like *.doc, *.xls are working
for the FileSearch. Please help.

Set fs = Application.FileSearch
With fs
.LookIn = sLookin
sFileTypeChoice = InputBox("What files are to be searched? Ex.
'doc' for word files", "File Type Choice")
If sFileTypeChoice = "" Then .Filename = "*.*" Else .Filename =
"*." & sFileTypeChoice
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Range("B" & (i + 2)).Select
Range("B" & (i + 2)).Hyperlinks.Add Anchor:=Selection,
Address:=.FoundFiles(i), TextToDisplay:=.FoundFiles(i)
Selection.Replace What:=sLookin & "\", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
Next i
Range("A1").Value = "Files Found: " & .FoundFiles.Count & "
Location: " & sLookin
Else
MsgBox "There were no files found."
Exit Sub
End If
End With

FileSearch.Execute does not find *.eml or *.lnk files
 

Thanks NickHK,
I used Dir() and the macro is working fine now. Just a little
changes as given below made my macro so simple. Once again thankx.

sFirstFile = Dir(sLookfor, vbNormal)

Do Until sFirstFile = ""
nCounter = nCounter + 1
Selection.Offset(1, 0).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=sLookin &
"\" & sFirstFile, _
TextToDisplay:=sFirstFile
sFirstFile = Dir()
Loop

Range("B2").Value = nCounter

FileSearch.Execute does not find *.eml or *.lnk files
 

RosH,
By the way, normally it is not necessary to .Select objects in Excel, unless
you have a reason to do so.
e.g.
With ActiveSheet
.Hyperlinks.Add Anchor:=.Range("FirstCell").Offset(nCounter , 0)....
End With

NickHK

"RosH" wrote in message
oups.com...
Thanks NickHK,
I used Dir() and the macro is working fine now. Just a little
changes as given below made my macro so simple. Once again thankx.

sFirstFile = Dir(sLookfor, vbNormal)

Do Until sFirstFile = ""
nCounter = nCounter + 1
Selection.Offset(1, 0).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=sLookin &
"\" & sFirstFile, _
TextToDisplay:=sFirstFile
sFirstFile = Dir()
Loop

Range("B2").Value = nCounter