Les macros ci-dessous peuvent être utilisées pour remplacer du texte dans un fichier texte, par exemple lorsque vous souhaitez modifier un séparateur de colonne dans un fichier texte avant de l’importer dans une feuille de calcul Excel ou après avoir exporté une feuille de calcul vers un fichier texte.

Sub ReplaceTextInFile(SourceFile As String, _

sText As String, rText As String)

Dim TargetFile As String, tLine As String, tString As String

Dim p As Integer, i As Long, F1 As Integer, F2 As Integer

TargetFile = "RESULT.TMP"

If Dir(SourceFile) = "" Then Exit Sub

If Dir(TargetFile) <> "" Then

On Error Resume Next

Kill TargetFile

On Error GoTo 0

If Dir(TargetFile) <> "" Then

MsgBox TargetFile & _

" already open, close and delete / rename the file and try again.", _

vbCritical

Exit Sub

End If

End If

F1 = FreeFile

Open SourceFile For Input As F1

F2 = FreeFile

Open TargetFile For Output As F2

i = 1 ' line counter

Application.StatusBar = "Reading data from " & _

TargetFile & " ..."

While Not EOF(F1)

If i Mod 100 = 0 Then Application.StatusBar = _

"Reading line #" & i & " in " & _

TargetFile & " ..."

Line Input #F1, tLine

If sText <> "" Then

ReplaceTextInString tLine, sText, rText

End If

Print #F2, tLine

i = i + 1

Wend

Application.StatusBar = "Closing files ..."

Close F1

Close F2

Kill SourceFile ' delete original file

Name TargetFile As SourceFile ' rename temporary file

Application.StatusBar = False

End Sub

Private Sub ReplaceTextInString(SourceString As String, _

SearchString As String, ReplaceString As String)

Dim p As Integer, NewString As String

Do

p = InStr(p + 1, UCase(SourceString), UCase(SearchString))

If p > 0 Then ' replace SearchString with ReplaceString

NewString = ""

If p > 1 Then NewString = Mid(SourceString, 1, p - 1)

NewString = NewString + ReplaceString

NewString = NewString + Mid(SourceString, _

p + Len(SearchString), Len(SourceString))

p = p + Len(ReplaceString) - 1

SourceString = NewString

End If

If p >= Len(NewString) Then p = 0

Loop Until p = 0

End Sub

Sub TestReplaceTextInFile()

ReplaceTextInFile ThisWorkbook.Path & _

"\ReplaceInTextFile.txt", "|", ";"

' replaces all pipe-characters (|) with semicolons (;)

End Sub