VBA - Retrieve Data from a Closed Workbook

Hello everyone,

I'm new to VBA and I have the following problem:

I have a file A and a source file. I want to extract data from the source file to display in file A. So I set up a link between the two files. However, this required opening the source file for file A to update.

I wrote the following code, which I managed to get with the help of a user:

Sub ImportDataWithoutOpening()

'Declare variable path and name as string
Dim SourcePath As String
Dim SourceFile As String

'Define variable content
SourcePath = "I:\G_AMEC\ETAT AMEC2\Etat AMEC 2\" '
SourceFile = "Etat AMEC 2 (version 2).xlsm"

'Create a data retrieval range from the closed source file, from the calculations sheet and the desired cell range
ThisWorkbook.Names.Add "range", _
RefersTo:="='" & SourcePath & "[" & SourceFile & "]calculs'!H9"

With Worksheets("RECAP") ' sheet from file A

Worksheets("RECAP").Range("F49").Value = "=range"
End With

End Sub

But this code displays 0 instead of the desired value... Do you have any ideas?

Thanks in advance