I don't think it's that easy - I have managed to workaround this one
in the past. Either post a small sample of the text file  and the whole
macro here along with how you want it to finish up, or send a copy of
the spreadsheet, with the macro in it, and the data as it is about to
be saved, directly to me (pascaldaulton at tesco dot net).

Sub Importer_fichier_correction()
'
' Importer_fichier_correction Macro
' Macro recorded 07/10/2005 by CacciapuBr01

Dim NbLigne, Nbcara As Integer
Dim rep, nom_fichier, nom_colonne, type_vente, commentaire As
String

' Demande a l'utilisateur le fichier a traiter
rep = Application.GetOpenFilename
Nbcara = Len(rep)
nom_fichier = Mid(rep, 57, Nbcara)

' Demande a l'utilisateur qq infos
nom_colonne = InputBox("Nom de la première colonne: ")
type_vente = InputBox("Volumetric ou Causal?")
commentaire = InputBox("Commentaire : ")

' Ouvre un fichier txt du repertoire Before
Workbooks.OpenText Filename:= _
rep, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:= _
False, Space:=False, Other:=True, OtherChar:=";",
FieldInfo:=Array( _
Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5,
2)), TrailingMinusNumbers _
:=True

' Insere une colonne a gauche
Columns("A:A").Select
Selection.Insert Shift:=xlToRight

'nombre de lignes importees
NbLigne = Cells.SpecialCells(xlLastCell).Row

'insere la formule qui permet de formater la ligne
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=""UC,DS_BE_UC_" & nom_colonne & ",BE,""& RC[1] &"",,""& RC
[2] &"",,"" & RC[3] & "",," & type_vente & ",,,"" & RC[4] & "","" &
RC[5] & "",,,,,,,,,,,'" & commentaire & "'"""

Range([A1], Cells(NbLigne, 1)).Select
Selection.FillDown

'efface la derniere ligne
Range(Cells(NbLigne, 1), Cells(NbLigne, 2)).Select
Selection.ClearContents

Range([A1], Cells(NbLigne, 1)).Select
Selection.Copy
Range("A1").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1,
DisplayAsIcon:=False, IconFileName:=False

Columns("B:F").Select
Selection.ClearContents

ChDir "W:\C19\Sirval\Corrections_BE\After_Format_Input_SIRVAL"
ActiveWorkbook.SaveAs Filename:= _
nom_fichier, FileFormat:=xlText, CreateBackup:=False
End Sub

Try
FileFormat:=xlTextPrinter
instead of
FileFormat:=xlText

It's only a guess as I don't know what's being imported into the
headers and contents of columns B,C,etc. therefore I don't know what
ends up in Column A. Getting the macro helped, but as mentioned
before, a copy of "the data as it is about to be saved" would have
allowed me to be sure, and saved me a lot of my time (but you're not
paying for it so of course, it matters not), and you'd have got a
definitive answer sooner.

In fact, the data written on each line contain one or more commas.
That's why all the lines of the text file  saved, contain quotes  at
the beginning and the end. Commas are not a delimiter, these are a
just text. I hope that's helped you to understand.

In fact, the data written on each line contain one or more commas.
That's why all the lines of the text file  saved, contain quotes  at
the beginning and the end. Commas are not a delimiter, these are a
just text. I hope that's helped you to understand.