Sub Import()
Dim chemin$, feuille$, ListeFich, ListeCel, tablo(), i&, form$, j%
chemin = [B1]
feuille = [B2]
ListeFich = [A8:A100] 'à adapter
ListeCel = [B6:N6] 'à adapter
With [B8:N100] 'tableau des résultats
ReDim tablo(1 To .Rows.Count, 1 To .Columns.Count)
For i = 1 To UBound(tablo)
If Dir(chemin & ListeFich(i, 1)) <> "" And Right(ListeFich(i, 1), 4) Like "xls?" Then
form = "='" & chemin & "[" & ListeFich(i, 1) & "]" & feuille & "'!"
For j = 1 To UBound(tablo, 2)
tablo(i, j) = form & ListeCel(1, j)
Next j
End If
Next i
.Formula = tablo
.Value = .Value
End With
End Sub