Private Sub Workbook_Activate()
Dim chemin As String, derlig As Long, tablo, i As Long, fich As String
Sheets("TB_DEVIS").Activate
chemin = "C:\Documents and Settings\R@chid\Bureau\DEVIS_FACTURES" & "\"
On Error Resume Next
ActiveSheet.ShowAllData 'affiche toutes les lignes du filtre
On Error GoTo 0
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) = Application.Index(tablo, , 9)
[J3].Resize(i - 1) = Application.Index(tablo, , 10)
Application.EnableEvents = True
End Sub