Searching Multiple Workbooks

[faxo]

Hi,

Is it possible to write a macro that will take in a name and search not only the current opened workbook but also all those workbooks contained in a specific folder.

Any help is greatly appreciated.

Thanks,

Fax.

 

[sweater_vests_rock]

hi fax.

i think this should work for you. the program takes a search string from the user, then loops through all worksheets in all open workbooks searching for the string. if the string is found, the user is notified of the location and asked if he/she should like the search to continue. if the user responds with "no", then the last cell found is selected.

hope this helps.
ben.

some testing, limited error-trapping

Sub SearchExcel()

    Dim strSearch As String
    Dim rngFind As Range
    Dim msg As Integer
    
    Dim wb As Workbook, ws As Worksheet

'   Get search string from the user
    strSearch = InputBox("Enter search string: ", Title:="Find in Excel")
    
    Application.ScreenUpdating = False
    
    For Each wb In Excel.Workbooks
        For Each ws In wb.Worksheets
            
            With ws.UsedRange
                                
'               Search for the string
                On Error Resume Next
                Set rngFind = .Find(What:=strSearch, After:=Range("A1"), _
                    LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
                
                If Not rngFind Is Nothing Then
                    firstFind = rngFind.Address
                    Do
'                       Alert user if search string found & query if search should continue
                        msg = MsgBox("Search string found!" & Chr(13) & Chr(13) & _
                            "Cell: " & rngFind.Address(False, False) & Chr(13) & _
                            "Worksheet: " & rngFind.Worksheet.Name & Chr(13) & _
                            "Workbook: " & rngFind.Worksheet.Parent.Name & Chr(13) & Chr(13) & _
                            "Would you like to continue searching?", vbYesNo)
                
                        If msg = vbYes Then
                            Set rngFind = .FindNext(rngFind)
                        Else
'                           Select the cell if query ended
                            With rngFind
                                .Worksheet.Parent.Activate
                                .Worksheet.Activate
                                .Select
                                GoTo EndSub:
                            End With
                        End If
                    Loop While rngFind.Address <> firstFind
                End If
                On Error GoTo 0
            End With
        Next ws
    Next wb
                
EndSub:
    Application.ScreenUpdating = True
    Set rngFind = Nothing
    Set ws = Nothing
    Set wb = Nothing

End Sub

 

[faxo]

Hi Ben,

Thanks for helping me out on this. I've done a bit of testing.
I hope I've done it right.
I have 2 workbooks in a folder. book2.xls and book3.xls.

I pasted the code into book2.xls in the vb editor.
With book2.xls and book3.xls open I run the macro It finds all instances of the string on sheet1 of book2.xls. So far so good. Unfortunately it doesn't find any instances on other sheets within the book2.xls or in book3.xls.

I am pretty new to coding so I am not to sure where to start looking.

Thanks,

Fax.

 

[sweater_vests_rock]

fax.

i can't replicate your error. i have two workbooks saved in seperate folders, with the macro in one. with both workbooks open, i find all occurrences in both workbooks across multiple sheets.

the code only looks for Whole matches, not part -- is this causing problems?

ben m.

 

[faxo]

Hi Ben,

I've tested again. This time with 1 open workbook
I have text in cell A1 of sheet 1 and the same text in cell C4 of sheet2.
My Code is in module1.
When I run the macro it finds the text in cell A1 of sheet1, but then when I click yes to find the second instance the message box pops up again but still displays 'found in cell A1 of sheet1'. On clicking yes again the macro ends. It doesn't seem to find the sheet2 instance in cell C4????

I am stumped!!!


Fax.

 

[sweater_vests_rock]

fax.

i've tested again to in a new workbook, and again i cannot get this error! the program searches for values, not formulas...could that be causing a problem?

also, could there be any left-over search instructions from previous FIND routines? a lot of the FIND settings are inherited if not overwritten in the code. i thought that i had tagged all the important ones, but perhaps not. when you goto the "Find Wizard" via CTRL+F, what are the advanced options toggled to?

this won't solve any problems, but the below code might be a nice bit of functionality if you ever get this working. try putting this code at the end of the routine (right before the EndSub: line)

'   If no range has been selected, check to select last range
    msg = MsgBox("No additional matches found.  " & _
        "Would you like to go to the final match?", vbYesNo)
    If msg = vbYes Then
        With rngFind
            .Worksheet.Parent.Activate
            .Select
        End With
    End If

ben.

Hi Ben,

I've tested again. This time with 1 open workbook
I have text in cell A1 of sheet 1 and the same text in cell C4 of sheet2.
My Code is in module1.
When I run the macro it finds the text in cell A1 of sheet1, but then when I click yes to find the second instance the message box pops up again but still displays 'found in cell A1 of sheet1'. On clicking yes again the macro ends. It doesn't seem to find the sheet2 instance in cell C4????

I am stumped!!!


Fax.

 

[faxo]

Hi Ben,

I've checked the ctrl-f after running the macro.
Match entire cell contents is checked.

But the 'within' field is toggled to 'sheet'. Should this be toggled to 'workbook'??

Fax.

 

[sweater_vests_rock]

fax.

But the 'within' field is toggled to 'sheet'. Should this be toggled to 'workbook'??

that shouldn't be a problem. you put the code in a standard module, not a sheet module, right? your issues sound like in this post (about midway down the page)

http://www.mrexcel.com/board2/viewt...=0&postorder=asc&highlight=workbook&&start=10

cheers. ben.

 

[faxo]

Well,

After a LOT of debugging, I used a little of your code and alittle of the code from the link you posted. NOW IT WORKS!!!!
Needs a little tweaking, but IT WORKS.

Thanks for all your time and help Ben.

Fax.

 

[sweater_vests_rock]

hi fax.

it would be great if you would post the final code.

cheers. ben.