Finding data among different excel file

[SamuelXiao]

My problem is, I have different xls files. e.g. 001.xls, 002.xls,
003.xls and so on. I know that among these files, there is one
information the cell A1 is 17, what I want to do is to find out which
xls file's cell A1 contains the data 17. How can I do this? Any help
would be appreciated.

 

[muddan madhu]

assumed you have 10 excel files, if you have more than that,
change a = 1 to desired number .. Run this macro.

Sub finder()
For a = 1 To 10
On Error GoTo Err:
Workbooks.Open ("00" & a & ".xls")
For x = 1 To Worksheets.Count
Worksheets(x).Select
If Range("a1").Value = 17 Then
MsgBox ActiveWorkbook.Name & " " & ActiveSheet.Name
Else
End If
Next
Next
Err:
Exit Sub
End Sub

 

[SamuelXiao]

assumed you have 10 excel files, if you have more than that,
change a = 1 to desired number .. Run this macro.

Sub finder()
For a = 1 To 10
On Error GoTo Err:
Workbooks.Open ("00" & a & ".xls")
For x = 1 To Worksheets.Count
Worksheets(x).Select
If Range("a1").Value = 17 Then
MsgBox ActiveWorkbook.Name & " " & ActiveSheet.Name
Else
End If
Next
Next
Err:
Exit Sub
End Sub

I try your code but it's invalid. Moreover, actually my files are not
exactly the same name as 001, 002 and so on. It can be any name.xls.
So what can I do it? I search google and find a software called
xlsgen, it has a search tool which is similar to what I want to do,
but it is not free. Are there any freeware to do so? Thanks.

 

[muddan madhu]

try this

Sub RunCodeOnAllXLSFiles()
Dim i As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:\Documents and Settings\muddanmadhu\Desktop\Madhu
\EXCEL Files"
.FileType = msoFileTypeExcelWorkbooks

If .Execute > 0 Then 'Workbooks in folder
For i = 1 To .FoundFiles.Count ' Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(.FoundFiles(i))
For x = 1 To Worksheets.Count
Worksheets(x).Select
If Range("a1").Value = 17 Then
MsgBox ActiveWorkbook.Name & " " & ActiveSheet.Name
Else
End If
Next
Next i
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

source ozgrid

 

[SamuelXiao]

try this

Sub RunCodeOnAllXLSFiles()
    Dim i As Integer
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    On Error Resume Next

    Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = "C:\Documents and Settings\muddanmadhu\Desktop\Madhu
\EXCEL Files"
        .FileType = msoFileTypeExcelWorkbooks

        If .Execute > 0 Then 'Workbooks in folder
            For i = 1 To .FoundFiles.Count ' Loop through all.
                 'Open Workbook x and Set a Workbook  variable to it
                Set wbResults = Workbooks.Open(.FoundFiles(i))
                 For x = 1 To Worksheets.Count
                 Worksheets(x).Select
                  If Range("a1").Value = 17 Then
                  MsgBox ActiveWorkbook.Name & " " & ActiveSheet.Name
                   Else
               End If
                 Next
            Next i
        End If
    End With

    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

source ozgrid

I try this code, changed the file path and so on. But when it runs,
it always goes to the wrong worksheet. Is it I have done something
wrong? How can I use Macro correctly? Thanks.