VBA Mass find and replace from 2 columns into .txt file

I know there are many posts like this one, but i spent 2 hours last night trying to get the macros i found to work, without luck.

Im doing a study at my university, where i have just above 14000 lines of chat text. Now i need to change everybodys name so will be anonymous. Now i have my excel sheet with 895 rows of original names in column A, and 895 rows of new names in column B, thats need to be replaced in my .txt document.


After some time on Google i discovered VBA macros would be the best soulution for me. Im very noobish at programming, so if someone could help me with a VBA macro here, i shall name my first born after you! (Or maybe something less dramatic, i'll eat a cake in your honor).

Can just ignore the '<' & '>' but it was very useful to be able to delimit the search terms with the angle brackets.

I'd be wary of just doing a blanket replace of a name. You might have someone using 'Star' as a name. Blindly replace 'star' with something else will also affect words like 'stare' ... so there's a compromise to be made:

Can replace all instances with angle brackets -that'll cause no problems.
Where a name is mentioned in text then make an assumption that it will be bounded with spaces either side and replace ' name ' with ' newname ' but that will not catch instances where the name is the last word in the message text - but that can also be coded for. And so it goes on.

Admittedly the chances of these exceptions get less and less and I really can't think of others, but that's the choice to be made.

See how far this gets you.
Assuming textfile and XL-file are in the same dir. Otherwise you have to amend path and filename.

Sub ReplaceStringInFile()

    Dim sBuf As String, sTemp As String
    Dim iFileNum As Integer, sFileName As String

' Edit as needed
    sFileName = ThisWorkbook.Path & "\" & "chat.txt"

    iFileNum = FreeFile
    Open sFileName For Input As iFileNum

    Do Until EOF(iFileNum)
        Line Input #iFileNum, sBuf
        sTemp = sTemp & sBuf & vbCrLf
    Loop
    Close iFileNum
    
    sn = Cells(1).CurrentRegion.Value
    For i = 1 To UBound(sn)
        sTemp = Replace(sTemp, sn(i, 1), sn(i, 2))
    Next

    iFileNum = FreeFile
    Open sFileName For Output As iFileNum
    Print #iFileNum, sTemp
    Close iFileNum

End Sub

Following should do it then - Add to a module

Option Explicit
Option Compare Text

Public Sub EditChat()

   '// For simplicity - can easily be extended to browse
   '// for the file but in the meantime edit to point
   '// to the actual file with the full directory path
   Const ChatFile As String = "C:\Users\Fred\Downloads\chat.txt"
   
   Dim iFile As Integer
   Dim ChatText As String
   Dim r As Excel.Range
   Dim ws As Excel.Worksheet
   
   '// Get a free file handle
   iFile = FreeFile
   '// Open the chat file for input
   Open ChatFile For Input As #iFile
   '// and read the entire contents to a variable
   ChatText = Input(LOF(iFile), #iFile)
   '// Done with - close the file.
   Close #iFile
   
   '// Loop each used cell in Column A
   Set ws = ActiveSheet
   For Each r In ws.Range("A1:A" & ws.Cells(Rows.Count, 1).End(xlUp).Row)
      
      '// If both Col A & Col B contain text...
      If r.Value <> vbNullString And r.Offset(, 1).Value <> vbNullString Then
         '// Search for nameincolA, replace with nameincolB
         '// All occurrances will be replaced.
         ChatText = Replace(ChatText, r.Value, r.Offset(, 1).Value)
      End If
   Next
      
   '// Write the edited string out to a new file.
   '// Also change the name of the edited file. this adds "_redact" before the extension
   '// making "chat_redact.txt"
   '// Note: If a file with that name already exists, IT WILL BE OVERWRITTEN.
   Open Replace(ChatFile, ".txt", "_redact.txt") For Output As #iFile
   Write #iFile, ChatText
   '// And close.
   Close #iFile
   
   MsgBox "Finished...", vbInformation
      
End Sub

It assumes the active sheet contains the list of names to replace.

(Essentially the same as the previous post except the file is read in one pass rather than appending line by line and it refers to the worksheet directly rather than reading it into an array first).

@ cytop

For speed better to read names in Array then reading line by line of worksheet.
So combining your reading of textfile in one go and my loop to change names should give best results.

Save a slice for me, yes.

You're welcome and thanks for adding rep points.