Private Sub Workbook_Activate()
Dim chemin$, f$, r As Range, t, rest(), i&, x$
chemin = ThisWorkbook.Path & "\"
f = "Feuil1" 'à adapter
With Feuil1 'CodeName de la feuille de destination
Set r = Intersect(.Range("A2:A" & .Rows.Count), .UsedRange)
End With
If r Is Nothing Then Exit Sub
t = r.Resize(, 2) 'au moins 2 éléments
ReDim rest(1 To UBound(t), 1 To 10)
For i = 1 To UBound(t)
If Dir(chemin & t(i, 1) & ".xls") <> "" Then
x = "'" & chemin & "[" & t(i, 1) & ".xls]" & f & "'!"
rest(i, 2) = ExecuteExcel4Macro(x & "R2C3") + ExecuteExcel4Macro(x & "R5C3") + ExecuteExcel4Macro(x & "R8C3")
rest(i, 3) = ExecuteExcel4Macro(x & "R3C3") + ExecuteExcel4Macro(x & "R6C3") + ExecuteExcel4Macro(x & "R9C3")
rest(i, 4) = ExecuteExcel4Macro(x & "R4C3") + ExecuteExcel4Macro(x & "R7C3") + ExecuteExcel4Macro(x & "R10C3")
rest(i, 5) = ExecuteExcel4Macro(x & "R2C4") + ExecuteExcel4Macro(x & "R5C4") + ExecuteExcel4Macro(x & "R8C4")
rest(i, 6) = ExecuteExcel4Macro(x & "R3C4") + ExecuteExcel4Macro(x & "R6C4") + ExecuteExcel4Macro(x & "R9C4")
rest(i, 7) = ExecuteExcel4Macro(x & "R4C4") + ExecuteExcel4Macro(x & "R7C4") + ExecuteExcel4Macro(x & "R10C4")
rest(i, 8) = ExecuteExcel4Macro(x & "R2C5") + ExecuteExcel4Macro(x & "R5C5") + ExecuteExcel4Macro(x & "R8C5")
rest(i, 9) = ExecuteExcel4Macro(x & "R3C5") + ExecuteExcel4Macro(x & "R6C5") + ExecuteExcel4Macro(x & "R9C5")
rest(i, 10) = ExecuteExcel4Macro(x & "R4C5") + ExecuteExcel4Macro(x & "R7C5") + ExecuteExcel4Macro(x & "R10C5")
rest(i, 1) = rest(i, 2) + rest(i, 5) + rest(i, 8)
End If
Next
i = Me.Saved 'mémorisation, pour éviter l'invite à la fermeture
r.Columns(2).Resize(, 10) = rest
Me.Saved = i
End Sub