Application.Filesearch yields incorrect results??
 

I use Application.Filesearch in an Excel macro to search through a series of
Word docs for a keyword. The search is initiated and the keyword(s) entered
via Userform. Hyperlinks to docs matching the search criteria are written
to a blank area on the worksheet.

The search works, except it just gave me some wrong answers. I entered
ABC-01. When I clicked the link and brought up the first doc, I did a Find
for ABC-01 - it was not found, but ABC-02 was!

Does the hyphen throw this off? Might other punctuations or non-alpha
characters? Any thoughts?

Ed

The Excel code:

Sub SearchKeyword()

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 = ActiveWorkbook.Path

If Range("BA15000") < "" Then
ListEnd = Range("BA65536").End(xlUp).Row
Range("BA15000:BA" & ListEnd).ClearContents
End If

If UserForm2.TextBox2.Text < "" Then
strSearchFor = UserForm2.TextBox2.Text
End If
If UserForm2.TextBox3.Text < "" Then
strSearchFor = strSearchFor & " and " & UserForm2.TextBox3.Text
End If
If UserForm2.TextBox4.Text < "" Then
strSearchFor = strSearchFor & " and " & UserForm2.TextBox4.Text
End If
If UserForm2.TextBox5.Text < "" Then
strSearchFor = strSearchFor & " and " & UserForm2.TextBox5.Text
End If

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

strName = UserForm2.TextBox1.Text

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

ActiveSheet.Columns("BA").ColumnWidth = 80

Set rngFiles = Range("BA15000")
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

Unload UserForm2

Range("BA15000").Select

End Sub

Application.Filesearch yields incorrect results??
 

one argument to filesearch is

.MatchTextExactly = True

from help: True if the specified file search will find only files whose body
text or file properties contain the exact word or phrase that you've
specified.

doesn't say what the default setting is.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
I use Application.Filesearch in an Excel macro to search through a series
of
Word docs for a keyword. The search is initiated and the keyword(s)
entered
via Userform. Hyperlinks to docs matching the search criteria are written
to a blank area on the worksheet.

The search works, except it just gave me some wrong answers. I entered
ABC-01. When I clicked the link and brought up the first doc, I did a
Find
for ABC-01 - it was not found, but ABC-02 was!

Does the hyphen throw this off? Might other punctuations or non-alpha
characters? Any thoughts?

Ed

The Excel code:

Sub SearchKeyword()

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 = ActiveWorkbook.Path

If Range("BA15000") < "" Then
ListEnd = Range("BA65536").End(xlUp).Row
Range("BA15000:BA" & ListEnd).ClearContents
End If

If UserForm2.TextBox2.Text < "" Then
strSearchFor = UserForm2.TextBox2.Text
End If
If UserForm2.TextBox3.Text < "" Then
strSearchFor = strSearchFor & " and " & UserForm2.TextBox3.Text
End If
If UserForm2.TextBox4.Text < "" Then
strSearchFor = strSearchFor & " and " & UserForm2.TextBox4.Text
End If
If UserForm2.TextBox5.Text < "" Then
strSearchFor = strSearchFor & " and " & UserForm2.TextBox5.Text
End If

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

strName = UserForm2.TextBox1.Text

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

ActiveSheet.Columns("BA").ColumnWidth = 80

Set rngFiles = Range("BA15000")
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

Unload UserForm2

Range("BA15000").Select

End Sub

Application.Filesearch yields incorrect results??
 

Good thought, Tom - but I added that in there and it didn't help. Clicked
the second link, did a Ctrl+F for "ABC-01", just as entered into the
Userform, and no good. "ABC" was in the doc, though. Maybe this is just a
limitation to live with and work around.

Ed

"Tom Ogilvy" wrote in message
...
one argument to filesearch is

.MatchTextExactly = True

from help: True if the specified file search will find only files whose
body
text or file properties contain the exact word or phrase that you've
specified.

doesn't say what the default setting is.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
I use Application.Filesearch in an Excel macro to search through a
series
of
Word docs for a keyword. The search is initiated and the keyword(s)
entered
via Userform. Hyperlinks to docs matching the search criteria are
written
to a blank area on the worksheet.

The search works, except it just gave me some wrong answers. I entered
ABC-01. When I clicked the link and brought up the first doc, I did a
Find
for ABC-01 - it was not found, but ABC-02 was!

Does the hyphen throw this off? Might other punctuations or non-alpha
characters? Any thoughts?

Ed

The Excel code:

Sub SearchKeyword()

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 = ActiveWorkbook.Path

If Range("BA15000") < "" Then
ListEnd = Range("BA65536").End(xlUp).Row
Range("BA15000:BA" & ListEnd).ClearContents
End If

If UserForm2.TextBox2.Text < "" Then
strSearchFor = UserForm2.TextBox2.Text
End If
If UserForm2.TextBox3.Text < "" Then
strSearchFor = strSearchFor & " and " & UserForm2.TextBox3.Text
End If
If UserForm2.TextBox4.Text < "" Then
strSearchFor = strSearchFor & " and " & UserForm2.TextBox4.Text
End If
If UserForm2.TextBox5.Text < "" Then
strSearchFor = strSearchFor & " and " & UserForm2.TextBox5.Text
End If

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

strName = UserForm2.TextBox1.Text

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

ActiveSheet.Columns("BA").ColumnWidth = 80

Set rngFiles = Range("BA15000")
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

Unload UserForm2

Range("BA15000").Select

End Sub