Convertir avec VBA

Option Explicit

'Variables globales utiles pour le traitement
Private Wk As Workbook
Private ws As Worksheet
Private cheminFichier As String

'-- Macro Principale ---
Public Sub MaMacro()

    InitVar   'Lancement de la procédure d'initialisation des variables
    OuvertureFichierTxt    'Lancement de l'ouverture et la lecture du fichier txt
    CloseVar     'Lancement de la procédure de fermeture des objets et libération de la mémoire
    
End Sub

'--- Initialisation des variables ---
Private Sub InitVar()

    Set Wk = ThisWorkbook   'Créer un objet Workbook
    Set ws = Wk.Worksheets(1)    'Créer un objet Worksheet de l'instance Wk
    cheminFichier = "D:\CCM\monFichierTxt.txt"      'Chemin complet et nom du fichier à traiter
    
End Sub

'Fermeture des objets
Private Sub CloseVar()
     Set ws = Nothing
     Set Wk = Nothing
End Sub

'--- Ouverture du fichier txt ---
Private Sub OuvertureFichierTxt()
Dim fp As Integer
Dim chaine As String
    
    '--- Ouverture du fichier txt
    fp = FreeFile
    Open cheminFichier For Input As fp
    While Not EOF(fp)   'Parcours toutes les lignes du fichier
        Input #fp, chaine
        Call TransposeExcel(chaine)     'Appel de la procédure de traitement de la ligne
    Wend
    
    Close fp
    
End Sub

'--- Traitement de la ligne et insertion dans Excel ---
Private Sub TransposeExcel(ByVal strChaine As String)
Dim mTab
Dim i As Integer
Dim DerLig As Long

    'Récupération des éléments dans un tableau
    mTab = Split(strChaine, "|")
    'Récupère la dernière ligne de la colonne A pour insertion à la suite
    DerLig = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
    'Parcours le tableau
    For i = LBound(mTab) To UBound(mTab)
        'Insertion des valeur dans Excel
        ws.Cells(DerLig, i + 1).Value = mTab(i)
    Next i
    
End Sub

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, OtherChar:="|"