With the procedures below you can use ADO to retrieve a recordset from a closed workbook and read/write data. In the example below, replace SheetName with the worksheet name you want to retrieve data from.

' call the procedure like this:
' GetWorksheetData "C:\Foldername\Filename.xls", "SELECT * FROM [SheetName$];", ThisWorkbook.Worksheets(1).Range("A3")

Sub GetWorksheetData(strSourceFile As String, strSQL As String, TargetCell As Range)
Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer, r As Long
    If TargetCell Is Nothing Then Exit Sub
    Set cn = New ADODB.Connection
    On Error Resume Next
    cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;DBQ=" & strSourceFile & ";"
    ' DriverId=790: Excel 97/2000
    ' DriverId=22:  Excel 5/95
    ' DriverId=278: Excel 4
    ' DriverId=534: Excel 3
    On Error GoTo 0
    If cn Is Nothing Then
        MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
        Exit Sub
    End If
        
    ' open a recordset
    Set rs = New ADODB.Recordset
    On Error Resume Next
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
'    rs.Open "SELECT * FROM [SheetName$]", cn, adOpenForwardOnly, adLockReadOnly, adCmdText
'    rs.Open "SELECT * FROM [SheetName$]", cn, adOpenStatic, adLockOptimistic, adCmdText
'    rs.Open "SELECT * FROM [SheetName$] WHERE [Field Name] LIKE 'A%'", cn, adOpenStatic, adLockOptimistic, adCmdText
'    rs.Open "SELECT * FROM [SheetName$] WHERE [Field Name] LIKE 'A%' ORDER BY [Field Name]", cn, adOpenStatic, adLockOptimistic, adCmdText

'    optional ways of retrieving a recordset
'    Set rs = cn.Execute("[A1:Z1000]") ' first worksheet
'    Set rs = cn.Execute("[DefinedRangeName]") ' any worksheet
    
    On Error GoTo 0
    If rs Is Nothing Then
        MsgBox "Can't open the file!", vbExclamation, ThisWorkbook.Name
        cn.Close
        Set cn = Nothing
        Exit Sub
    End If
    
    RS2WS rs, TargetCell ' optional 
    ' TargetCell.CopyFromRecordset rs ' available in Excel 2000 or later
    
    If rs.State = adStateOpen Then
        rs.Close
    End If
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub