I can't give you a more explicit answer but try modifying this to suit your needs
Sub ReadFileInFolder()
    Dim i As Integer
    Dim strPath As String, strFilename As String
    Dim strSheetName As String, strReadCell As String
    Dim wbResult As Workbook, shtResult As Worksheet
    
    Set wbResult = Workbooks("Test")
    Set shtResult = wbResult.Sheets("Sheet1")
    
    strPath = "C:\data\2010\"
    strFilename = shtResult.Range("A1") & ".xls"
    strSheetName = "Sheet1"

    ' Check if file exists
    If Dir(strPath & strFilename) = "" Then
        MsgBox "File:- " & strPath & strFilename & "does not exist."
        Exit Sub
    End If
    
    For i = 1 To 5
        strReadCell = "'" & strPath & "[" & strFilename & "]" & strSheetName
        strReadCell = strReadCell & "'!R" & i & "C2"
        shtResult.Range("B" & i) = ExecuteExcel4Macro(strReadCell)
    Next
    
    Set wbResult = Nothing
    Set shtResult = Nothing
End Sub

ExecuteExcel4Macro is the function I think you are looking for

Hope this helps