search multiple files
Posted by Shawn on October 12, 2001 6:29 PM
Hello,
How would I go about creating something to search the same cell in multiple xls files all stored in the same folder and returning the name of each file with the search contents without opening each file?
Sounds too complex for a macro to me (unless there is a command I don't see) so if there is some other type of tool I could use can someone suggest one?
Thank you,
Shawn
Posted by Shawn on October 14, 2001 12:50 PM
I figured it out, if you are curious here's the code:
Option Compare Text
---------------------------------------
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/12/2001 by Shawn
'
'
Range("A5:D65536").Select
Selection.ClearContents
sname = Range("b2").Value
outnum = 5
outnam = 5
qnumb = "='C:\My Documents\new\[quotenumbergenerator.xls]'!A1"
Range("f3").Select
ActiveCell.Formula = qnumb
thesheet = 1
Do While thesheet <= Range("f3").Value
pname = "='C:\My Documents\new\[" & thesheet & ".xls]Cover'!A13"
Range("f2").Select
ActiveCell.Formula = pname
parname = "*" & Range("b2").Value & "*"
Range("f4").Select
ActiveCell.Formula = parname
If Range("f2").Value Like Range("f4").Value Then
Range("a" & outnum).Select
ActiveCell.Formula = thesheet
Range("b" & outnam).Select
ActiveCell.Formula = "='C:\My Documents\new\[" & thesheet & ".xls]Cover'!A13"
outnum = outnum + 1
outnam = outnam + 1
End If
thesheet = thesheet + 1
Range("f1").Select
ActiveCell.Formula = thesheet
Loop
End Sub
Pretty specific to what I'm doing but if someone needs an idea of how to get started this should be it.
-Shawn
