Replace text in a text file
1999-12-17 File access 0 160
The macros below can be used to replace text in a text file, e.g. when you want to change a column separator in a text file before you import it into an Excel worksheet or after you export a worksheet to a text file.
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 & " ..." End If 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