Extraire données fichier .text vers excel

Private Sub essai()
DerLig = Sheets(1).Range("A65536").End(xlUp).Row
For i = 1 To DerLig
Cells(i, 2).Value = Left(Cells(i, 1), 2)
Cells(i, 3).Value = Right(Left(Cells(i, 1), 5), 2)
Cells(i, 4).Value = Right(Left(Cells(i, 1), 8), 2)
Cells(i, 5).Value = Right(Left(Cells(i, 1), 13), 3)
Cells(i, 6).Value = Right(Left(Cells(i, 1), 15), 1)
Cells(i, 7).Value = Right(Left(Cells(i, 1), 16), 1)
Cells(i, 8).Value = Right(Left(Cells(i, 1), 17), 1)
Cells(i, 9).Value = Right(Left(Cells(i, 1), 18), 1)
Cells(i, 10).Value = Right(Left(Cells(i, 1), 19), 1)
Cells(i, 11).Value = Right(Left(Cells(i, 1), 20), 1)
Cells(i, 12).Value = Right(Left(Cells(i, 1), 21), 1)
Cells(i, 13).Value = Right(Left(Cells(i, 1), 22), 1)
Cells(i, 14).Value = Right(Left(Cells(i, 1), 24), 1)
Cells(i, 15).Value = Right(Left(Cells(i, 1), 25), 1)
Cells(i, 16).Value = Right(Left(Cells(i, 1), 26), 1)
Cells(i, 17).Value = Right(Left(Cells(i, 1), 27), 1)
Cells(i, 18).Value = Right(Left(Cells(i, 1), 28), 1)
Cells(i, 19).Value = Right(Left(Cells(i, 1), 29), 1)
Cells(i, 20).Value = Right(Left(Cells(i, 1), 30), 1)
Cells(i, 21).Value = Right(Left(Cells(i, 1), 31), 1)
Next i
End Sub

.......
oConn.Close
[COLOR=blue]With ActiveSheet
    .Range("A1:A" & .Range("A65536").End(xlUp).Row).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
    .Columns("B:C").NumberFormat = "00000000"
 End With
[/COLOR] 
Application.ScreenUpdating = True
End Sub