Searching a Closed Workbook ?

How would I modify the code below to search a closed workbook, in the same directory as the open workbook (that has the search form) ?

In the closed workbook, always searching COLUMN A on worksheets #2 & #3, returning data from columns A-J.

Wanting to keep the closed workbook closed at all times if possible (or at the least, hidden).

Thank you for your assistance.

Sub FindAllMatches()
'Find all matches on activesheet
'Called by: TextBox_Find_KeyUp event


Dim SearchRange As Range
Dim FindWhat As Variant
Dim FoundCells As Range
Dim FoundCell As Range
Dim arrResults() As Variant
Dim lFound As Long
Dim lSearchCol As Long
Dim lLastRow As Long
   
    If Len(Me.TextBox_Find.Value) > 1 Then       'Do search if text in find box is longer than 1 character.
        
        Set SearchRange = ActiveSheet.UsedRange.Cells
        
        FindWhat = Me.TextBox_Find.Value
        'Calls the FindAll function
        Set FoundCells = FindAll(SearchRange:=SearchRange, _
                                FindWhat:=FindWhat, _
                                LookIn:=xlValues, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByColumns, _
                                MatchCase:=False, _
                                BeginsWith:=vbNullString, _
                                EndsWith:=vbNullString, _
                                BeginEndCompare:=vbTextCompare)
        If FoundCells Is Nothing Then
            ReDim arrResults(1 To 1, 1 To 2)
            arrResults(1, 1) = "No Results"
        Else
            'Add results of FindAll to an array
            ReDim arrResults(1 To FoundCells.Count, 1 To 2)
            lFound = 1
            For Each FoundCell In FoundCells
                arrResults(lFound, 1) = FoundCell.Value
                arrResults(lFound, 2) = FoundCell.Address
                lFound = lFound + 1
            Next FoundCell
        End If
        
        'Populate the listbox with the array
        Me.ListBox_Results.List = arrResults
        
    Else
        Me.ListBox_Results.Clear
    End If
        
End Sub

Private Sub ListBox_Results_Click()
'Go to selection on sheet when result is clicked

Dim strAddress As String
Dim l As Long

    For l = 0 To ListBox_Results.ListCount
        If ListBox_Results.Selected(l) = True Then
            strAddress = ListBox_Results.List(l, 1)
            ActiveSheet.Range(strAddress).Select
            'Populate textboxes with results
            With ActiveSheet
            
                UserForm1.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 4).Value
                UserForm1.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 6).Value
                UserForm1.TextBox_Results3.Value = .Cells(.Range(strAddress).Row, 7).Value
                UserForm1.TextBox_Results4.Value = .Cells(.Range(strAddress).Row, 8).Value
                UserForm1.TextBox_Results5.Value = .Cells(.Range(strAddress).Row, 9).Value
                UserForm1.TextBox_Results6.Value = .Cells(.Range(strAddress).Row, 10).Value
                UserForm1.TextBox_Results7.Value = .Cells(.Range(strAddress).Row, 11).Value
                UserForm1.TextBox_Results8.Value = .Cells(.Range(strAddress).Row, 12).Value
                UserForm1.TextBox_Results9.Value = .Cells(.Range(strAddress).Row, 13).Value
                UserForm1.TextBox_Results10.Value = .Cells(.Range(strAddress).Row, 1).Value
                
                
                UserForm1.txtDate = TextBox_Results10.Value
                UserForm1.txtCall = TextBox_Results1.Value
                UserForm1.txtMode = TextBox_Results2.Value
                UserForm1.txtPwr = TextBox_Results3.Value
                UserForm1.txtMyRST = TextBox_Results4.Value
                UserForm1.txtHisRST = TextBox_Results5.Value
                UserForm1.txtName = TextBox_Results6.Value
                UserForm1.txtCity = TextBox_Results7.Value
                UserForm1.txtStateDX = TextBox_Results8.Value
                UserForm1.txtComments = TextBox_Results9.Value
                
            End With
            GoTo EndLoop
        End If
    Next l

EndLoop:
    
End Sub

I'll have to make concessions then.

Can you open a closed workbook but have it VISIBLE = FALSE ?

In any case, how would I modify the existing code to accomplish my goal (second workbook open or closed) ?

I didn't want to reinvent what I already have.

I can't get the syntax working, changing "active workbook" to "workbooks" (referring to the closed workbook).

#1 Anyone have suggestions to modify my existing code ?

#2 I don't think VLOOKUP would be wise 'cuz there are just over 1 million entries to search.

#3 ADOB and SQL are way above my understanding at this time. However, if anyone is kind enough to help, I'd be mighty appreciative !
I'm willing to learn if you are willing to explain.


Thank you all for your assistance.

Your code is incomplete. Where is the FindAll() routine? Chip Pearson has a good one. http://www.cpearson.com/excel/findall.aspx

There are several ADO methods out there. Ron de Bruin has a routine that can be handy. You would want to modify the SQL I suspect. Since I don't know what your goal is, I can not suggest an SQL string. http://www.rondebruin.nl/win/s3/win024.htm