Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel returning an incorrect total | Excel Discussion (Misc queries) | |||
Lookup returning incorrect value | Excel Discussion (Misc queries) | |||
IF function returning incorrect result | Excel Worksheet Functions | |||
formula returning incorrect blanks | Excel Worksheet Functions | |||
formula returning incorrect blanks | Excel Discussion (Misc queries) |