Application.Filesearch yields incorrect results??

E

Ed

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
 
T

Tom Ogilvy

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.
 
E

Ed

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top