Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I have a problem trying to find or work out how to do a word search from an excel 2007 sheet into a bunch of files in a folder.
<o> </o>
Basically I want to be able to write a word in an excel(2007) cell, run some VBA that searched thought a bunch of excel file, then return the file names in another column as hyperlinks to the files that the word was found in.
<o> </o>
The excel 2003 VBA I’ve been using is below, it doesn’t work in 2007, our work is extremely busy these days and I’m running out of time to figure it out.
<o> </o>
Thanks
<o> </o>
Sub NameSearch()<o></o>
Dim fs As FileSearch<o></o>
<o> </o>
Dim lLen As Long, i As Long<o></o>
Dim parts<o></o>
<o> </o>
Set fs = Application.FileSearch<o></o>
fs.NewSearch<o></o>
With fs<o></o>
With .PropertyTests<o></o>
.Add _<o></o>
Name:="Text or Property", _<o></o>
Condition:=msoConditionIncludesPhrase, _<o></o>
Value:=Range("m11")<o></o>
End With<o></o>
.LookIn = ThisWorkbook.Path & "\Files"<o></o>
.SearchSubFolders = False<o></o>
.Filename = "*"<o></o>
.MatchTextExactly = False<o></o>
.FileType = msoFileTypeAllFiles<o></o>
End With<o></o>
ActiveSheet.Unprotect Password:="1"<o></o>
Range("o15000").Select<o></o>
Selection.ClearContents<o></o>
If fs.Execute() > 0 Then<o></o>
For i = 1 To fs.FoundFiles.Count<o></o>
parts = <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Split</st1lace></st1:City>(Trim(Replace(Dir(fs.FoundFiles(i)), ".xls", "")))<o></o>
Cells(i, 15) = parts(UBound(parts))<o></o>
Cells(i, 16).FormulaR1C1 = "=Hyperlink(" & Chr(34) & fs.FoundFiles(i) _<o></o>
& Chr(34) & ",R[0]C[-1])"<o></o>
Next i<o></o>
Range("p15005").Select<o></o>
With Selection.Font<o></o>
.Name = "Arial"<o></o>
.Size = 14<o></o>
.Strikethrough = False<o></o>
.Superscript = False<o></o>
.Subscript = False<o></o>
.OutlineFont = False<o></o>
.Shadow = False<o></o>
End With<o></o>
Columns("o:q").Select<o></o>
Selection.Sort Key1:=Range("q1"), Order1:=xlDescending, Header:=xlGuess, _<o></o>
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<o></o>
DataOption1:=xlSortNormal<o></o>
Range("c11").Select<o></o>
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1"<o></o>
Range("d11:E11").Select<o></o>
Else<o></o>
Range("d11:E11").Select<o></o>
MsgBox "There were no files found."<o></o>
End If<o></o>
End Sub
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I have a problem trying to find or work out how to do a word search from an excel 2007 sheet into a bunch of files in a folder.
<o> </o>
Basically I want to be able to write a word in an excel(2007) cell, run some VBA that searched thought a bunch of excel file, then return the file names in another column as hyperlinks to the files that the word was found in.
<o> </o>
The excel 2003 VBA I’ve been using is below, it doesn’t work in 2007, our work is extremely busy these days and I’m running out of time to figure it out.
<o> </o>
Thanks
<o> </o>
Sub NameSearch()<o></o>
Dim fs As FileSearch<o></o>
<o> </o>
Dim lLen As Long, i As Long<o></o>
Dim parts<o></o>
<o> </o>
Set fs = Application.FileSearch<o></o>
fs.NewSearch<o></o>
With fs<o></o>
With .PropertyTests<o></o>
.Add _<o></o>
Name:="Text or Property", _<o></o>
Condition:=msoConditionIncludesPhrase, _<o></o>
Value:=Range("m11")<o></o>
End With<o></o>
.LookIn = ThisWorkbook.Path & "\Files"<o></o>
.SearchSubFolders = False<o></o>
.Filename = "*"<o></o>
.MatchTextExactly = False<o></o>
.FileType = msoFileTypeAllFiles<o></o>
End With<o></o>
ActiveSheet.Unprotect Password:="1"<o></o>
Range("o15000").Select<o></o>
Selection.ClearContents<o></o>
If fs.Execute() > 0 Then<o></o>
For i = 1 To fs.FoundFiles.Count<o></o>
parts = <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Split</st1lace></st1:City>(Trim(Replace(Dir(fs.FoundFiles(i)), ".xls", "")))<o></o>
Cells(i, 15) = parts(UBound(parts))<o></o>
Cells(i, 16).FormulaR1C1 = "=Hyperlink(" & Chr(34) & fs.FoundFiles(i) _<o></o>
& Chr(34) & ",R[0]C[-1])"<o></o>
Next i<o></o>
Range("p15005").Select<o></o>
With Selection.Font<o></o>
.Name = "Arial"<o></o>
.Size = 14<o></o>
.Strikethrough = False<o></o>
.Superscript = False<o></o>
.Subscript = False<o></o>
.OutlineFont = False<o></o>
.Shadow = False<o></o>
End With<o></o>
Columns("o:q").Select<o></o>
Selection.Sort Key1:=Range("q1"), Order1:=xlDescending, Header:=xlGuess, _<o></o>
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<o></o>
DataOption1:=xlSortNormal<o></o>
Range("c11").Select<o></o>
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1"<o></o>
Range("d11:E11").Select<o></o>
Else<o></o>
Range("d11:E11").Select<o></o>
MsgBox "There were no files found."<o></o>
End If<o></o>
End Sub