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