Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.FileSearch | Excel Discussion (Misc queries) | |||
Month function yields incorrect solution | Excel Worksheet Functions | |||
ACCRINT formula yields incorrect result | Excel Worksheet Functions | |||
VBA Application.FileSearch | Excel Programming | |||
FileSearch macro returning incorrect docs? | Excel Programming |