FileSearch macro returning incorrect docs?

[Ed]

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

 

[Helmut Weber]

Hi Ed,
besides the fact, that filesearch searches for text
_or_ property, it seemably searches the _whole_ doc,
not only the text that would be displayed,
but deleted text as well. Maybe even word's hidden
content. That is 19 or so kByte of a new blank doc.
If you want to be sure, you probably have to create
an instance of word and use the the "find" methode.
Greetings from Bavaria, Germany
Helmut Weber
"red.sys" & chr$(64) & "t-online.de"
Word 97, NT 4.0

 

[Ed]

Thank you for replying, Helmut. I don't know if that would apply here.
These docs are created by using a Visual Basic program to separate very long
docs (ASCII database outputs complied and saved as Word files) into several
individual docs. So these are created by copying a range from the original
and pasting into a New Document - no typing, no deleted text.

Is there a better method to use than FileSearch?

Ed

 

[Helmut Weber]

Hi Ed,
there is Word's "find" method, as I indicated,
and nothing else, AFAIK, for processing word-docs,
as there is always a remainder of at least 19 KByte,
the contents of which are a MS-secret. I search some
hundreds of docs of 2 to 3 pages every night and put
excerpts into a database, which lasts about one hour.
But may I ask, why You use word-docs at all. If it is
only ascii-database output, I would try to stick to the
txt-format. Copying ranges is possible, though. Open
as many txt-files as You like, define and copy ranges,
and save the result as txt. Which may seem to be tricky,
but for that there are very simple solutions.
Greetings from Bavaria, Germany
Helmut Weber
"red.sys" & chr$(64) & "t-online.de"
Word 97, NT4.0

 

[Ricky M. Medley]

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

 

[Ed]

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

 

[Ed]

The compiled Word docs are sent to me - someone else accesses the database
and outputs the info as Word. For me, even if they were .txt files, I'd
open and use them in Word anyway, just for the macro functionality. The VB
program (not written by me) that slices the big docs into little ones can
handle and save as either .txt or .doc, but I'm not sure about how the
database operator is set up. (They're using a custom-made program from the
80s written in Pascal, I think, to access and retrieve info. Someone's set
up a "job-security shop" modifying the program each time a new Windows OS
comes out! They may have met their match with XP, though, and we may
*finally* get to upgrade to 90s technology! <G>)

Will the Find method work from within my Excel macro? Or will I need to
rewrite that in Word, and call it up from Excel? If the latter, how do I
pass the variables?

Ed

 

[Helmut Weber]

Hi Ed,
in principle it is as easy as that:
The "late binding" method with "getobject"
assumes that an instance of word is already running.
Dim oW As Object
Set oW = GetObject(, "Word.Application")
oW.Documents.Open "co-000077.doc" ' or a variable
With oW.Selection.Find
.Text = "ferrite core" ' or a variable
If .Execute Then
MsgBox "found" ' your code
End If
End With
oW.ActiveDocument.Saved = True
oW.ActiveDocument.Close
There is another method called early binding,
which I prefer, to create a new instance of word,
which requires a reference to the word library.
Unfortunately I can't test it, as I have to juggle
with different word-versions. You may record the action
in word, paste the macro into Excel and simply add "oW",
as you see in the sample code. You got to take care
of find-paramters like .clearformatting and others.
Greetings from Bavaria, Germany
Helmut Weber
"red.sys" & chr$(64) & "t-online.de"
Word 97, NT 4.0