VBA - Using a List Box as a Search Through Multiple Workbooks

[steimel386]

I'm trying to create a macro that is essentially the search function in Windows, but only goes through the current directory and it's subfolders. I have this, and the code is as listed below (the module). Then, I have a userform that comes up with a list of the files that the string was found in (works). What I want to do is have the ability to click on the name of the file and open the workbook.

Currently I can open the workbook, but only if I manually put in the search term (rather than MyValue) in the UserForm sub procedure. Is there a way around this? Any help is much appreciated.

The module in the workbook:

Sub FindFiles()

MyValue = InputBox("This macro will only search the Excel files" & vbCrLf _
& "in this directory and the folders within it. " & vbCrLf _
& "" & vbCrLf _
& "Enter your search criteria below:", "Search Criteria", "Search")

With Application.FileSearch
    .NewSearch
    .LookIn = ThisWorkbook.Path
    .SearchSubFolders = True
    .TextOrProperty = MyValue
    .MatchTextExactly = False
    .FileType = msoFileTypeExcelWorkbooks
    .Execute
        For i = 1 To .FoundFiles.Count
            UserForm1.ListBox1.AddItem .FoundFiles(i)
        Next i
    End With
    UserForm1.Show

End Sub

The code in the Userform:

Private Sub ListBox1_Click()

Workbooks.Open Filename:=ListBox1.Text
    Set c = .Find("alexsteimel", LookIn:=xlValues)
             'I don't want to use "alexsteimel", I want the variable "MyValue" 
             'from above to automatically be used. Also, is there a way to 
             'to automatically activate the cell that has MyValue in it?

End Sub

 

[dglnz]

Not done anything with your example BUT i have a need of something else and found what i think might be of help to you - see below from the help file.

regds,

dave.

LookIn Property
See AlsoApplies ToExampleSpecificsReturns or sets the folder to be searched during the specified file search. Read/write String.

Example
This example searches the My Documents folders for all files that begin with "Cmd" and displays the name and location of each file that’s found.

Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.FileName = "cmd*.*"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

 

[steimel386]

Thanks, but its already being used by the Application.FileSearch command above