Find and replace text in a file from Excel

[PhilBreau]

Hi All, I posted this in the excel forum, but was advised to post here

I have a signature file I use in outlook as a template. I want to search for several words and replace them with cells of specific columns on the current row of the cursor, while I have the excel spreadsheet open. I use generic words in the template as variables. I want to search and replace these variables and create a new signature file. for simplicity, the variables are in CAPS and highlighted in red

Here is the signature file:

{\rtf1\ansi\ansicpg1252\deff0\deflang4105\deflangfe4105{\fonttbl{\f0\fswiss\fprq2\fcharset0 Calibri;}{\f1\froman\fprq2\fcharset0 Times New Roman;}}
{\colortbl ;\red31\green73\blue125;\red0\green0\blue255;}
{\*\generator Msftedit 5.41.21.2510;}\viewkind4\uc1\pard\cf1\f0\fs22 Hi all,\par
\par
The part is being shipped to FIRSTNAME LASTNAME as requested. UPS tracking number \cf2\ul{\field{\*\fldinst{HYPERLINK "

http://wwwapps.ups.com/etracking/tracking.cgi?Type...=

TRACKINGNUMBER}{\fldrslt{TRACKINGNUMBER}}}\f0\fs22. \cf1\ulnone They should arrive by TIME tomorrow. FIRSTNAME please advise once you receive them.\par
\par
\par
Thank you,\par
\par
MYNAME\par
\cf0\f1\par
}



There is formatting in the HTM file, but I want to keep the file exactly as it is, but find and replace the variables.


Thank you

 

[Andrzejek]

I created your signature file as "C:\TEMP\Signature.txt"

My Excel file loks like this;

[pre]
First Name Last Name TRACKINGNUMBER
Phil Breau 123ABC
Andy Brown 876XYZ
[/pre]

and the VBA code:

Option Explicit

Sub CreateSigs()
Dim i As Integer
Dim intFileNo As Integer
Dim strTextLine As String
Dim strOutput As String
i = 2

Do While Range("A" & i).Value <> ""
    intFileNo = intFileNo + 1
    Open "C:\TEMP\File" & intFileNo & ".txt" For Output As #2
    
    Open "C:\TEMP\Signature.txt" For Input As #1
    Do While Not EOF(1)             ' Loop until end of file.
        Line Input #1, strTextLine   ' Read line into variable.
       
        If InStr(strTextLine, "FIRSTNAME LASTNAME") Then
            strOutput = Replace(strTextLine, "FIRSTNAME LASTNAME", Range("A" & i).Value & " " & Range("B" & i).Value)
        ElseIf InStr(strTextLine, "=TRACKINGNUMBER") Then
            strOutput = Replace(strTextLine, "=TRACKINGNUMBER", Range("C" & i).Value)
        Else
            strOutput = strTextLine
        End If
       
        Print #2, strOutput
    Loop
    Close #1
    Close #2
    i = i + 1
Loop

End Sub

And since I have 2 rows in Excel, I end up with 2 txt files with some information replaced. You would have to add more columns and some code to do it all.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PhilBreau]

Hi Andy,

This works great. I tried it and I only get the first and last name, but not the tracking number. I changed the order and I can get the tracking number, but not the first or last name. I think it's because of the else logic. How do I find and replace all variables?



Line Input #1, strTextLine ' Read line into variable.

If InStr(strTextLine, "=TRACKINGNUMBER") Then
strOutput = Replace(strTextLine, "=TRACKINGNUMBER", Range("C" & i).Value)

ElseIf InStr(strTextLine, "FIRSTNAME LASTNAME") Then
strOutput = Replace(strTextLine, "FIRSTNAME LASTNAME", Range("A" & i).Value & " " & Range("B" & i).Value)
Else
strOutput = strTextLine
End If

Print #2, strOutput

Thanks again.

 

[Andrzejek]

OK, change of harts

Try this instead - same sample of Excel data:

Sub CreateMySigs()
Dim strInputData As String
Dim strOutputData As String
Dim i As Integer

Open "C:\TEMP\Signature.txt" For Input As #1
strInputData = Input$(LOF(1), 1)
Close #1

i = 2
Do While Range("A" & i).Value <> ""
    strOutputData = strInputData
    strOutputData = Replace(strOutputData, "FIRSTNAME", Range("A" & i).Value)
    strOutputData = Replace(strOutputData, "LASTNAME", Range("B" & i).Value)
    strOutputData = Replace(strOutputData, "TRACKINGNUMBER", Range("C" & i).Value)
    
    Open "C:\TEMP\File" & i - 1 & ".txt" For Output As #1
    Print #1, strOutputData
    Close #1

    i = i + 1
Loop

End Sub

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[strongm]

Um ... this looks rather like a mail merge to me.

 

[PhilBreau]

Hi Andy,

This is perfect. Thank you so much. Here's a little something for the tip jar.

$$$$$$$$$$


BTW, I have a work station

Phil Breau

 

[PhilBreau]

strongm,

Thanks for your reply. I do use mail merge. I wanted to create a signature file I can insert when replying to all. For what I need, I think Andy's solution works better for me than a mail merge. I couldn't quite figure out a mail merge for that.

Phil Breau