Search all excel files in root directory

[Whittick88]

Hi, i have been looking for a macro to search all workbooks in a folder (my plan was to drop a workbook with required macro into this folder so using ActiveWorkbook.Path) and return the name and sheet of workbook and row the item was found (or just jump to the location)

if anyone can help i would be very greatful.

On a side note i have tried the windows search function using 'file contains' but this doesn't seem to work half the time.

 

[Trevor G]

The following code will search through a folder and place the file names in Sheet 1.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> mefiles1()<br><SPAN style="color:#007F00">'List files in a folder</SPAN><br><SPAN style="color:#007F00">'Clear anything first</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> F <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>Sheets("Sheet1").Range("A:A").Clear<br><br>F = Dir("m:\Access Files\*.xls")<br>Range("A1").Activate<br><br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> Len(F) > 0<br>ActiveCell.Formula = F<br>ActiveCell.Offset(1, 0).Select<br><br>F = Dir()<br><SPAN style="color:#00007F">Loop</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Whittick88]

Hi, thanks for your reply. This has the ability to search the file names, however I am wanting to search the contents of the files in a folder for a specefic string (say an item number), then return (if found) the location of the string searched for. So, in essence:

Open all files in directory
Search for user input
Return location

or something along those lines

thanks

 

[Trevor G]

Ok here is some code that will open all workbooks in a folder and do something.

It may help if you can answer the following questions.

Does the search have to be through all worksheets in the workbook it opens. Do you want the cell.Address and where is it to store the results?

Sub Open_My_Files()
Dim MyFile As String
MyPath = "M:\Access Files\"
MyFile = Dir(MyPath)
Do While MyFile <> ""
If MyFile Like "*.xls" Then
Workbooks.Open MyPath & MyFile
'Something different has to happen here like a loop?
'Sheets(1).Select
'Range("C3") = 6
ActiveWorkbook.Close True
End If
MyFile = Dir
Loop
End Sub

 

[Whittick88]

Hi, thanks for the updated code! I would like to search all sheets yes, initially just returning the filename of the file the string was found in in A1 of the master workbook would be ideal

 

[Trevor G]

Ok still confused (slightly).

Are you saying you have a text entry in Cell A1 of a workbook and you want to find that and in Cell A2 place the location it was found in?

Can you copy and paste a sample of the Master sheet to help me understand better.

 

[Whittick88]

sorry there isnt a specefic example as its more of a solution to a future problem.

Basically I have a folder with, say 10 workbooks in, lets call them 1,2,3,4,5,6,7,8,9,10 .xls. I want to be able to drop this workbook, lets call it search.xls (which will contain the macro) into the folder of workbooks, hit run and it search each workbook (1,2,3 etc) for a string (the string will be entered into search.xls in either a message box or say cell a1).

Then any matches will return the name of the workbook they were found in.

so, for example: 123456789 was found in "3.xls" (to be printed in search.xls)

not sure if that makes any more sense actually... :s

 

[Trevor G]

Shown below are sets of code which can be adapted and then tweeked to give you what you want.

The first is the open all workbooks in a folder, the second is to search for something (Tom) in cells in all worksheets, in this example it colours the cell in the workbook where it finds the match. This is where you would need to adapt it to return the workbook name, then sheet name and cell Address into your Search workbook.

Sub Open_My_Files()
Dim MyFile As String
MyPath = "M:\Access Files\"
MyFile = Dir(MyPath)
Do While MyFile <> ""
If MyFile Like "*.xls" Then
Workbooks.Open MyPath & MyFile
'Something different has to happen here like a loop?
'Sheets(1).Select
'Range("C3") = 6
ActiveWorkbook.Close True
End If
MyFile = Dir
Loop
End Sub

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> AllSheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> FirstAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> MySearch <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myColor <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> I <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> sh <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br>    <SPAN style="color:#007F00">'Fill in the search Value and color Index</SPAN><br>    MySearch = Array("Tom")<br>    myColor = Array("3")<br><br>    <SPAN style="color:#007F00">'You can also use more values in the Array</SPAN><br>    <SPAN style="color:#007F00">'MySearch = Array("Jane", "Jackie", "Craig")</SPAN><br>    <SPAN style="color:#007F00">'myColor = Array("3", "6", "10")</SPAN><br><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sh <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br><br>        <SPAN style="color:#007F00">'Fill in the Search range, for a range on each sheet</SPAN><br>        <SPAN style="color:#007F00">'you can use sh.Range("B1:D100")</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> sh.Range("A1:IV600")<br><br>            <SPAN style="color:#007F00">'Change the fill color to "no fill" in all cells</SPAN><br>            .Interior.ColorIndex = xlColorIndexNone<br><br>            <SPAN style="color:#00007F">For</SPAN> I = <SPAN style="color:#00007F">LBound</SPAN>(MySearch) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(MySearch)<br><br>                <SPAN style="color:#007F00">'If you want to find a part of the rng.value then use xlPart</SPAN><br>                <SPAN style="color:#007F00">'if you use LookIn:=xlValues it will also work with a</SPAN><br>                <SPAN style="color:#007F00">'formula cell that evaluates to MySearch(I)</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> Rng = .Find(What:=MySearch(I), _<br>                                After:=.Cells(.Cells.Count), _<br>                                LookIn:=xlFormulas, _<br>                                LookAt:=xlWhole, _<br>                                SearchOrder:=xlByRows, _<br>                                SearchDirection:=xlNext, _<br>                                MatchCase:=False)<br><br>                <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Rng <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                    FirstAddress = Rng.Address<br>                    <SPAN style="color:#00007F">Do</SPAN><br>                        Rng.Interior.ColorIndex = myColor(I)<br>                        <SPAN style="color:#00007F">Set</SPAN> Rng = .FindNext(Rng)<br>                    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> Rng <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And Rng.Address <> FirstAddress<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> I<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> sh<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

The second code comes from Rondebruin website

http://www.rondebruin.nl/tips.htm

 

[Whittick88]

You're a star Trevor. Thanks for your help

 

[Trevor G]

I hope it works for you.