Create Macro to replace text in .txt file from Excel sheet

[fOfOf]

Dear Community,

I am a beginner in excel macro, and hope to find an answer to my question in this great forum. I looked at the previous threads but couldn't find an answer, so I am posting my own thread. I would like to create a simple excel macro which modifies text in an existing file, using values located in an excel sheet.
I want the macro to open the text file (which is located in a specific folder), to find the text located in excel's cell A1, and to replace it by excel's cell B1. Then, it should find the text located in excel's cell A2, and to replace it by excel's cell B2, and so on.
Can anyone please help me with this issue, I've been trying it out myself but can not find a solution.
Thanks a lot for your help!

 

[Firefly2012]

Hi

Do you want to replace all instances of the text in A1 or just the first?

 

[fOfOf]

Hi,

Thanks for your very quick response !
I might not have explained myself correctly.
I have two existing files: one excel file, and one .txt file.
In the column A of my excel file, the cells contain text (=oldtext).
This text is already written out in the .txt file. I created a Column B, with each row containing text (=newtext). I want for each row to find the "oldtext" (column A) in the .txt file, and to replace it by the "newtext" (column B).
I'm sorry I'm not very clear, I'm really unfamiliar with macros.

 

[Firefly2012]

Here's one possibility:

Sub Replace_Text()
Dim strFile As String
Dim i As Integer
Dim strText As String
Dim cell As Range
With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = ThisWorkbook.Path
    If .Show <> -1 Then Exit Sub
    strFile = .SelectedItems(1)
End With
i = FreeFile
strText = Space(FileLen(strFile))
With CreateObject("vbscript.regexp")
    .Global = True
    Open strFile For Binary Access Read Write As #i
        Get #i, , strText
        For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
            .Pattern = Replace(Replace(Replace(Replace(cell.Value, "?", "\?"), "*", "\*"), "+", "\+"), ".", "\.")
            strText = .Replace(strText, cell.Offset(, 1).Value)
        Next cell
        Put #i, 1, strText
    Close #i
End With
            
End Sub

Run the macro and select the file and away it goes.

Note there will be an issue if the text to be replaced in A2 matches any part of the text already replaced in B1.

 

[fOfOf]

Hey,

It seems to work smoothly. I indeed encounter the issue you raised, but it shouldn't be a problem. Quick question: how can I change this macro to make it more flexible. For example, if the columns are not A and B, and if the rows don't start at 1?
Thanks a lot for your help, I really appreciate!

 

[Firefly2012]

This bit:

For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)


Defines the column and start row (A1:A) - so change these as needed

 

[fOfOf]

Can I define it using ActiveCell ? In that can I just have to point in excel the correct cell to start with.

 

[Firefly2012]

Sure can:

For Each cell In Range(Activecell, Cells(Rows.Count, ActiveCell.Column).End(xlUp))

 

[fOfOf]

Works perfectly, you're great !
Thanks !!

 

[Gugan V]

Hi Firefly2012,

Thanks for your reply.

My apologies for the in-sufficient explanation

Here you go..

In my work sheet there are 2 columns A and B. A column contains “Refname” and B column contains respective “Designnote”. I have a text file, it contains only Refname. So my requirement is I want to find the Refname in text file and update with Designnote.

This is how my text file content looks,

<questionref id="c08c9e11-b53d-4203-b82c-66776a50aed3"><shortquestionref id="345ed9ff-48eb-4960-a51c-2498be976197"><month required="true" allowunknown="false"><day required="true" allowunknown="true"><hour required="true" allowunknown="true"><minute required="true" allowunknown="true">RefName="CMSTDTCIT" xmlns="http://phaseforward.com/CSML/2009-06-11" “Description/” “CustomProperty”“Name”Item Required”/Name”“Value”true”/Value”“/CustomProperty”“CustomProperty”“Name”SDV Required”/Name”“Value”true”/Value”“/CustomProperty”“Title”CMSTDTCIT”/Title”“QuestionRef Id="c08c9e11-b53d-4203-b82c-66776a50aed3" /”“ShortQuestionRef Id="345ed9ff-48eb-4960-a51c-2498be976197" /”“Year Required="true" AllowUnknown="false"““Range Start="2010" End="2015" /”“/Year”“Month Required="true" AllowUnknown="false"/”“Day Required="true" AllowUnknown="true"/”“Hour Required="true" AllowUnknown="true"/”“Minute Required="true" AllowUnknown="true"/”“/DateTimeVariable”

Step1 - Copy the Refname in 1^st cell of column A and find in text file. – This step only for placing the cursor

Step2 - Again system should find the keyword “Description /” here only I want to replace with Designnote in column B 1^st cell value.
<description>
<description><description><description><description>For more explanation,
Row1 “please assume something here” “Description/”
Row2 “please assume something here” “Description/”
Row3 “please assume something here” “Description/”
Row4 “please assume something here” “Description/”
Row5 please assume something here” “Description/”
Row6 “please assume something here” “Description/”

So if my column A first cell Refname value = Row4 then system should replace 4^th line “Description/” with column B 1st cell value and so on.

Thanks,</description></description></description></description></description></minute></hour></day></month></shortquestionref></questionref>