Apply a list of replacements in an Excel spreadsheet to a text file

[nandagopal]

This macro opens the text file with name in Cells(1, 2), makes replacements listed in Cells(5, 1) through Cells(8, 2), and saves the results in the text file with name in Cells(2, 2). You can make several enhancements such as rearranging the cells, automatically searching for the end of the replacement list, asking the user to enter the file names, and so forth.

Code Select Expand

Sub ReplaceStringsInFile()
Dim fnum As Integer
Dim txt As String
Dim r As Integer

    ' Get the input file's contents.
    fnum = FreeFile
    Open Path & "\" & Cells(1, 2) For Input As fnum
    txt = Input$(LOF(fnum), fnum)
    Close fnum

    ' Make the replacements.
    For r = 5 To 8
        txt = Replace(txt, Cells(r, 1), Cells(r, 2))
    Next r

    ' Save the results.
    fnum = FreeFile
    Open Path & "\" & Cells(2, 2) For Output As fnum
    Print #fnum, txt;
    Close fnum

    MsgBox "Ok"
End Sub