Reference Range from Closed Workbooks

I have a workbook into which I want to aggregate data from many source workbooks (roughly 100 of them). Each month, these 100 workbooks will be updated, and I need my workbook to incorporate these changes without manually opening all 100 to update the data in my workbook. I have tried using regular linked formulas, but this results in #Refs when the source workbooks are changed or saved over.

I need a way to access the data from these workbooks without having to open each one when I want to update. Here is what I have for code so far, and I have attached an Excel file that illustrates what I am aiming to achieve.

Sub RefreshSourceData()
    Dim sourceFile As String, fileName As String, path As String
    path = "U:\Info\Meeting Materials\Strategy Files\"
    fileName = "[" & ActiveCell.EntireRow.Cells(1, 1).Value & ".xls]"
    sourceFile = "='" & path & fileName
    Range("B" & ActiveCell.Row).Formula = sourceFile & "Summary'!$F$2"
    Range("C" & ActiveCell.Row).Formula = sourceFile & "Summary'!$F$3"
    Range("D" & ActiveCell.Row).Formula = sourceFile & "Summary'!$B$10"
    Range("E" & ActiveCell.Row).Formula = sourceFile & "Summary'!$D$10"
    Range("F" & ActiveCell.Row).Formula = SomeWorkbookVariable(Don't know how to create this.).StdDev("Tab within workbook referenced first cell of row")
End Sub