Excel VBAで他のブックの特定のセルの値をファイルを開かずに取得する方法

Application.ExecuteExcel4Macroメソッドを使う

このメソッドは、Microsoft Excel 4.0 マクロ関数を実行するメソッドですが、
引数に取得したいブックの絶対パスとシート名とセルへの参照を指定することで、
対象のブックをオープンせずに、その値を取得することができます。
※メモリ上に展開されるだけで、ウィンドウは開かないという事です。

Dim a As Variant
a = ExecuteExcel4Macro("'C:\tmp\[sample.xls]Sheet1'!R1C2")

上記の場合は、 C:\tmp\sample.xls の Sheet1 の B2セル の値をブックを開かずに取得しています。
この関数では、セルへの参照は R1C1 形式の文字列として指定する必要があります。

相対パスで他のブックへ参照を行いたい場合

自ブックから特定の相対パスにあるブックへの参照を行いたい場合は、
Scripting.FileSystemObjectオブジェクトを利用して対象ファイルの絶対パスを取得してExecuteExcel4Macroメソッドに渡します。

例）自ブックと同じフォルダに存在するすべての「.xls」ファイルのSheet1シートのB2セルの値を取得する場合

' FileSystemObjectを使用して現在のフォルダのFolderオブジェクトを取得
Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
Dim currentFolder As Folder
Set currentFolder = fso.GetFolder("./")

' 自ファイルと同じフォルダにあるブックのSheet1シートのB2セルの値を取得
Dim i As Integer: i = 1
For Each f In currentFolder.Files
    If f.Name Like "*.xls" Then
        Cells(i, 1) = ExecuteExcel4Macro("'" & f.Path "[" & f.Name & "]Sheet1'!R1C2")
        i = i + 1
    End If
Next f

公式ドキュメント

• Application.ExecuteExcel4Macroメソッド
  https://docs.microsoft.com/ja-jp/office/vba/api/excel.application.executeexcel4macro
• Scripting.FileSystemObjectオブジェクト
  https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/filesystemobject-object