FileSearch macro returning incorrect docs?
 

I have a macro in an Excel workbook that takes the values in certain cells
to create a search string. The macro then searches all the Word docs in a
single folder for docs containing that string. Normally it works well -
normally, though, I use only single words. Today, however, I used phrases,
and the search returned two documents that didn't fit what I thought my
criteria were!

I stepped through this and moused over my variables, and they all looked
correct. In cell E9, I had "Blackbox Code: 1.9" - this is the one that
caused the false returns. One document had "Blackbox Code: 1.7.9.2", which
I guess is close; the other had "Blackbox Code: 1.3.5" - it did not contain
"1.9" anywhere! One of the other cell values was "XM/SC Class: ABD", and it
gave me no problems. Additional note: the three variables were put in the
search string with ANDs.

If anyone can make suggestions on improving this code to keep this from
happening again, I would be most grateful.

Ed

Sub z()

Dim lngIndex As Long
Dim strSearchFor As String
Dim strLocation As String
Dim rngFiles As Range
Dim strName As String
Dim ListEnd As String

strLocation = ThisWorkbook.Path

If Range("B15") < "" Then
ListEnd = Range("B65536").End(xlUp).Row
Range("B15:B" & ListEnd).ClearContents
End If

strLocation = ThisWorkbook.Path

If Range("E5") < "" Then
strSearchFor = Range("E5").Text
If Range("E7") < "" Then
If Range("D6") = "And" Then
strSearchFor = strSearchFor & " and " & Range("E7").Text
Else
strSearchFor = strSearchFor & " or " & Range("E7").Text
End If
If Range("E9") < "" Then
If Range("D8") = "And" Then
strSearchFor = strSearchFor & " and " & Range("E9").Text
Else
strSearchFor = strSearchFor & " or " & Range("E9").Text
End If
End If
End If
End If

If strSearchFor = "" Then
MsgBox "Nothing to search for", vbExclamation
Exit Sub
End If

strName = Range("E3").Text

With Application.FileSearch
.NewSearch
.LookIn = strLocation
.SearchSubFolders = False
.TextOrProperty = strSearchFor
.MatchTextExactly = True
.Filename = "L5-" & strName & "*.doc"
.Execute

Set rngFiles = Range("B15")
rngFiles.Offset(0, 0) = "Found " & .FoundFiles.Count & " containing
" & .TextOrProperty
For lngIndex = 1 To .FoundFiles.Count
ActiveSheet.Hyperlinks.Add Anchor:=rngFiles.Offset(lngIndex, 0),
Address:=.FoundFiles.Item(lngIndex)
Next
End With


End Sub

FileSearch macro returning incorrect docs?
 

Just guessing, but this activity seems to be using the <period as an
or....Or the end of a sentence string.??
as in "Blackbox Code: 1" or ".7" or ".9" or ".2"
This thoery doesn't completely jive with your results yeilding "Blackbox
Code: 1.9"...in that case maybe an and/or.
i dunno
I've looked through microsoft't technet with no results. After all you did
have .MatchTextExactly = True

good luck,
ricky

<<snip "Blackbox Code: 1.7.9.2", which<<snip

FileSearch macro returning incorrect docs?
 

Thanks for the assist, Ricky. If maybe you stumble onto something someday,
I'd appreciate a shout. But I guess I just live with it.

Ed

"Ricky M. Medley" wrote in message
...
Just guessing, but this activity seems to be using the <period as an
or....Or the end of a sentence string.??
as in "Blackbox Code: 1" or ".7" or ".9" or ".2"
This thoery doesn't completely jive with your results yeilding "Blackbox
Code: 1.9"...in that case maybe an and/or.
i dunno
I've looked through microsoft't technet with no results. After all you
did
have .MatchTextExactly = True

good luck,
ricky

<<snip "Blackbox Code: 1.7.9.2", which<<snip