Sub ImportTextFile()
Dim L&, Ligne$, TabLigne, i%
Dim fso, fc, f, sf
L = ActiveSheet.Range("a65536").End(xlUp).Row + 1
chemin = "J:\npai\Invalides\test\"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fc = fso.GetFolder(chemin).Files
For Each f In fc
If UCase(fso.GetExtensionName(f.Path)) = "TXT" Then
Set sf = fso.OpenTextFile(f.Path, 1, False, -2)
Do Until sf.AtEndOfStream
Ligne = sf.readline
If InStr(Ligne, "@") > 0 Then
TabLigne = Split(Ligne, ";")
For i = LBound(TabLigne) To UBound(TabLigne)
ActiveSheet.Cells(L, i + 1).Value = TabLigne(i)
Next i
L = L + 1
End If
Loop
sf.Close
End If
Next
Set f = Nothing
Set fc = Nothing
Set fso = Nothing
End Sub