Perchè probabilmente Excel tenta di indovinare in automatico, ma in questo caso evidentemente 'sbaglia' (è capitato anche a me) forse perchè la formattazione del file non è esattamente come la vuole lui.

Ho fatto una prova utilizzando un mio vecchio file che ha stringhe delimitate dal carattere Pipe (|).
Dopo aver selezionto il file è partito il wizard, ho impostato il delimitatore su |, impostato tutti i campi di tipo carattere, e questo è il risultato:

codice:
Sub ApriCSV()
'
' ApriCSV Macro
' Prova CSV
'
    ChDir "D:\Documenti\_VB\_miei\VisualDB\BAAN_Migrazione"
    Workbooks.OpenText Filename:= _
        "D:\Documenti\_VB\_miei\VisualDB\BAAN_Migrazione\baan_agenti.txt", Origin:= _
        xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:= _
        False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array( _
        Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2)), TrailingMinusNumbers:=True
End Sub
Ovviamente dovrai sostituire usando i tuoi dati:
- il nome del file
- il carattere delimitatore
- le occorrenze di Array(x,x)

Fatto questo dovrebbe funzionare.

Eventualmente puoi fare una prova con un tuo file, bastano poche righe di prova, in cui sostituisci il delimitatore esistente con il Pipe (|).
Dovrebbe partirti il wizard ed a quel punto è banale. Basta ricordarsi di impostare tutti i campi su Carattere altrimenti c'è il rischio per Excel interpreti Numeri invece di Codici e ti toglie eventuali zeri iniziali (come avviene sempre con i numeri di Partita IVA!!!).