Multireplace words from a text file

Question

i am not familiar to VBA at all. I found a code online that allows me to replace several words at the same time. I found it a year ago, but i can't find it now so can't give credit to the person who wrote it (my point is, it was not i who wrote it).

It works great, but i instead of entering all of the words in StrFind and StrRepl inside VBscript i want to load the words from a text file in containing a list of words and the corresponding replaced word.

This is the code:

Sub MultiReplace()
Dim StrFind As String, StrRepl As String

Dim i As Long
StrFind = "word1, word2, word3"
StrRepl = "hello1, hello2, hello3"
Set RngTxt = Selection.Range
For i = 0 To UBound(Split(StrFind, ","))

Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = Split(StrFind, ",")(i)
.Replacement.Text = Split(StrRepl, ",")(i)
.Format = False
.MatchWholeWord = True
.MatchAllWordForms = False
.MatchWildcards = False
.Execute Replace:=wdReplaceAll
End With
Next i
'
End Sub

I want to adjust the lines StrFind = "word1, word2, word3" StrRepl = "hello1, hello2, hello3" so it gets data from the textfile instead, this because i got an error if i have to much word/letters.

This is how a textfile would look: list.txt:

word1 == hello1
word2 == hello2

It dosent need to be a txt file, an excel would work as well (but i want to remain the superfast time of running the script in word).

Thanks for help

Answer 1

You can import your list.txt to your excel file, and then read the content from your excel file cells.

A very useful tool to retrieve VBA code for determined action is the macro recorder, in the ribbon, Developer -> RecordMacro, perform you action and stop recording and then you can check the code generated for the actions you recorded. Its not the cleanest code but you can find there the lines of code for the specific actions you want. Once you step into a one concrete problem with the code you tried, you can then ask for help regarding something more concrete, more than expecting that someone will code that for you.

Find this .txt content import code from a random txt file of mine, just recording the steps when you import your .txt with user actions:

Sub Importtxt()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Usuario\Desktop\YourFileName.txt", Destination:=Range("$A$1") _
        )
        .CommandType = 0
        .Name = "YourFileName"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(117, 2, 3)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

User actions to follow can be found here. From here on you would need to read your StrFind and StrRepl from your excel file after imported, and apply those values accordingly in your working macro.

Hope that helps