To retrieve a single cell's value for all Excel files in a folder

Try

Sub test()
    Dim myDir As String, fn As String, n As Long
    Const WSName As String = "Sheet1", ref As String = "I41"
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then myDir = .SelectedItems(1) & "\"
    End With
    If myDir = "False" Then Exit Sub
    fn = Dir(myDir & "*.xls")
    Do While fn <> ""
        n = n + 1
        Sheets(1).Cells(n, 1).Resize(, 2).Value = _
        Array(fn, "='" & myDir & "[" & fn & "]" & WSName & "'!" & ref)
        fn = Dir
    Loop
End Sub