How do I retrieve data from another Excel file without opening it ?

Let's consider a 'Source file' in the same folder as my Excel Macro.

Let's say that data we want to retrieve is a table made of 5 columns in the first sheet of 'Source file'.

First, we need to create a 'Data retrieval' sheet in 'My Macro.xlsm' file. Then, we can retrieve 'Source file.xlsx' data without opening it with the following VBA code :

Set appxl = CreateObject("Excel.application")
Dim myfile As Window
Dim currentSheet As Worksheet
Dim lastRow As Double
Dim sourceFileName As String

sourceFileName = "Source File.xlsx"

'Open Source File.xlsx
With appxl
    .Workbooks.Open ActiveWorkbook.Path & "\" & sourceFileName
    .Visible = False
End With
 
'Get first sheet data
Set myfile = appxl.Windows(sourceFileName)
myfile.Activate
Set currentSheet = appxl.Sheets(1)

'Past the table in my current Excel file
lastRow = currentSheet.Range("A1").End(xlDown).Row
Sheets("Data retrieval").Range("A1:E" & lastRow) = currentSheet.Range("A1:E" & lastRow).Value

'Close Source File.xlsx
appxl.Workbooks(sourceFileName).Close

As you can see, we actually open the source file with a false visible status and then we close it.