How To Read Cell from a Closed Workbook

If we do it manually, we can do like image below

A12 is result from closed Workbook (BookToRead.xlsx)

If we do it with Macro:
A. Methode 1 ( with formula like do manually)

1 2 3 4 5 6 7 8 9 10

Sub Button1_Click() Dim Path As String Path = ThisWorkbook.Path & "\" For i = 1 To 10 Range("B11").Offset(i, 0) = "='" & Path & "[BookToRead.xlsx]Sheet1'!$A$" & i Next i End Sub

B. Methode2 (with ExecuteExcel4Macro)

1 2 3 4 5 6 7 8 9 10

Sub Button2_Click() Dim i As Integer Path = ThisWorkbook.Path & "\" For i = 1 To 10 Range("G11").Offset(i, 0) = ExecuteExcel4Macro("'" & Path & "[BookToRead.xlsx]Sheet1'!R" & i & "C1") Next i End Sub

Download File (Place both files in same directory)