Private Sub Worksheet_Activate()
Dim chemin As String, derlig As Long, tablo, i As Long, fich As String
chemin = ThisWorkbook.Path & "\" 'à adapter
derlig = Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & derlig) = "" Then derlig = derlig - 1
tablo = Range("A3:J" & derlig)
For i = 1 To UBound(tablo)
fich = Dir(chemin & tablo(i, 1) & ".xls*")
tablo(i, 9) = "": tablo(i, 10) = ""
If fich <> "" Then
tablo(i, 9) = ExecuteExcel4Macro("VLOOKUP(""*TTC*"",'" & chemin & "[" & fich & "]" & tablo(i, 1) & "'!R1C1:R200C10,10,0)")
tablo(i, 10) = ExecuteExcel4Macro("VLOOKUP(""*HT*"",'" & chemin & "[" & fich & "]" & tablo(i, 1) & "'!R1C1:R200C10,10,0)")
End If
Next
Application.EnableEvents = False
[I3].Resize(i - 1).Formula = Application.Index(tablo, , 9)
[J3].Resize(i - 1).Formula = Application.Index(tablo, , 10)
Application.EnableEvents = True
End Sub