Search cell value in more than one workbook
 

Is there anyway I can serch for a cell value in more than one workbook?
I have workbooks that are saved in one folder nad I need to be able to find
cell value and determin in which file (or workbook) it appears. Using the
find and replace would look only in one workbook.
Thank you

Search cell value in more than one workbook
 

Hi,

I think you need code for this. The code below opens every workbook in a
directory and call a sub where you can look for your value

Sub LoopThroughDirectory()
Application.DisplayAlerts = False
'Change this to your directory
MyPath = "C:\"
ActiveFile = Dir(MyPath & "*.xls")
Do While ActiveFile < ""
Workbooks.Open Filename:=MyPath & ActiveFile
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
ActiveWorkbook.Close
ActiveFile = Dir()
Loop
Application.DisplayAlerts = True
End Sub

Sub DoSomething(Book As Workbook)
'test cell value
MsgBox ActiveWorkbook.Name

End Sub

Mike

"DreamKid" wrote:

Is there anyway I can serch for a cell value in more than one workbook?
I have workbooks that are saved in one folder nad I need to be able to find
cell value and determin in which file (or workbook) it appears. Using the
find and replace would look only in one workbook.
Thank you

Search cell value in more than one workbook
 

Thank Mike for the information.
I ran the code and it opens he file display a message and then closes the
file then to the next. It does not allow me to run the find box or to enter
what to find. Any idea?
Thank you

"Mike H" wrote:

Hi,

I think you need code for this. The code below opens every workbook in a
directory and call a sub where you can look for your value

Sub LoopThroughDirectory()
Application.DisplayAlerts = False
'Change this to your directory
MyPath = "C:\"
ActiveFile = Dir(MyPath & "*.xls")
Do While ActiveFile < ""
Workbooks.Open Filename:=MyPath & ActiveFile
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
ActiveWorkbook.Close
ActiveFile = Dir()
Loop
Application.DisplayAlerts = True
End Sub

Sub DoSomething(Book As Workbook)
'test cell value
MsgBox ActiveWorkbook.Name

End Sub

Mike

"DreamKid" wrote:

Is there anyway I can serch for a cell value in more than one workbook?
I have workbooks that are saved in one folder nad I need to be able to find
cell value and determin in which file (or workbook) it appears. Using the
find and replace would look only in one workbook.
Thank you