Text File Mail Merge With VBA

[Nine Zero]

Alright so let me see if i can ask this as best as possible

I have a spreadsheet with 24 values in column A (A1:A24)

I also have a text file that we use as a mail merge template to run anothet macro inside a separate software

What i would like is to for each selected value out of the 24, find and replace "XXXXXXXXXX" with the value selected, in the textfile, then repeat it for each selected one but i need it to copy the entire file again and replace the next "XXXXXXXXXX" with the following one. The finally save the .txt file with the date as the file name. This makes no sense lol visual representation is better....


Excel File:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Doggy[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Kitty[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Pig[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mouse[/TD]
[/TR]
</tbody>[/TABLE]


Text File:

TypeTo field 'Statement ID' , 'Stuff'
MoveTo field Description
MoveTo field '(L) Range' item 0 # 'All'
ClickHit field '(L) Range' item 1 # 'From:'
MoveTo field '(L) From Customer ID'
TypeTo field '(L) From Customer ID' , 'XXXXXXXXXX'
MoveTo field '(L) To Customer ID'
TypeTo field '(L) To Customer ID' , 'XXXXXXXXXX'
MoveTo field '(L) Ranges Selected' item 0

End Result(Assuming i only selected "Doggy", "Kitty", and "Mouse" from the spreadsheet:

TypeTo field 'Statement ID' , 'Stuff'
MoveTo field Description
MoveTo field '(L) Range' item 0 # 'All'
ClickHit field '(L) Range' item 1 # 'From:'
MoveTo field '(L) From Customer ID'
TypeTo field '(L) From Customer ID' , 'Doggy'
MoveTo field '(L) To Customer ID'
TypeTo field '(L) To Customer ID' , 'Doggy'
MoveTo field '(L) Ranges Selected' item 0
TypeTo field 'Statement ID' , 'Stuff'
MoveTo field Description
MoveTo field '(L) Range' item 0 # 'All'
ClickHit field '(L) Range' item 1 # 'From:'
MoveTo field '(L) From Customer ID'
TypeTo field '(L) From Customer ID' , 'Kitty'
MoveTo field '(L) To Customer ID'
TypeTo field '(L) To Customer ID' , 'Kitty'
MoveTo field '(L) Ranges Selected' item 0
TypeTo field 'Statement ID' , 'Stuff'
MoveTo field Description
MoveTo field '(L) Range' item 0 # 'All'
ClickHit field '(L) Range' item 1 # 'From:'
MoveTo field '(L) From Customer ID'
TypeTo field '(L) From Customer ID' , 'Mouse'
MoveTo field '(L) To Customer ID'
TypeTo field '(L) To Customer ID' , 'Mouse'
MoveTo field '(L) Ranges Selected' item 0

 

[John_w]

Try this. Edit the code where indicated to specify the full name of the template file.

Public Sub Merge_Selected_Cells_In_Text_File()

    Dim textFileTemplate As String, outputFile As String
    Dim fileNum As Integer
    Dim fileData As String
    Dim cell As Range

    If Intersect(Selection, ActiveSheet.Range("A1:A24")) Is Nothing Then
        MsgBox "No cells selected in A1:A24 "
        Exit Sub
    End If

    textFileTemplate = "C:\path\to\Template.txt"    'CHANGE THIS
    outputFile = Left(textFileTemplate, InStrRev(textFileTemplate, "\")) & Format(Date, "yyyy mm dd") & ".txt"
    
    fileNum = FreeFile
    Open textFileTemplate For Binary As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] 
    fileData = Space(LOF(fileNum))
    Get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] , , fileData
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] 
    
    fileNum = FreeFile
    Open outputFile For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] 
    For Each cell In Selection
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] , Replace(fileData, "XXXXXXXXXX", cell.Value)
    Next
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] 
        
End Sub

 

[Nine Zero]

Try this. Edit the code where indicated to specify the full name of the template file.

Public Sub Merge_Selected_Cells_In_Text_File()

    Dim textFileTemplate As String, outputFile As String
    Dim fileNum As Integer
    Dim fileData As String
    Dim cell As Range

    If Intersect(Selection, ActiveSheet.Range("A1:A24")) Is Nothing Then
        MsgBox "No cells selected in A1:A24 "
        Exit Sub
    End If

    textFileTemplate = "C:\path\to\Template.txt"    'CHANGE THIS
    outputFile = Left(textFileTemplate, InStrRev(textFileTemplate, "\")) & Format(Date, "yyyy mm dd") & ".txt"
    
    fileNum = FreeFile
    Open textFileTemplate For Binary As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum"]#fileNum[/URL] 
    fileData = Space(LOF(fileNum))
    Get [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum"]#fileNum[/URL] , , fileData
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum"]#fileNum[/URL] 
    
    fileNum = FreeFile
    Open outputFile For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum"]#fileNum[/URL] 
    For Each cell In Selection
        Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum"]#fileNum[/URL] , Replace(fileData, "XXXXXXXXXX", cell.Value)
    Next
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum"]#fileNum[/URL] 
        
End Sub

Sorry for the late reply

You are amazing! Works perfectly! I can definitely learn a lot from this code

Thank you very much

 

[Nine Zero]

Quick question,

What if i have 2 or n-amount of replacements to make?

like not only do i wanna do

Replace(fileData, "XXXXXXXXXX", cell.Value)

but I also want to do

Replace(fileData, "YYYYYYYYYY", cell.Offset(,1).Value)

how would i add that to the For Each Loop

 

[John_w]

Try this macro, which works like the previous code if only cells A1:A24 are selected and also makes multiple replacements when multiple columns (A1:B24) are selected.

Public Sub Merge_Selected_Cells_Multiple_Columns_In_Text_File()

    Dim textFileTemplate As String, outputFile As String
    Dim fileNum As Integer
    Dim fileData As String, newFileData As String
    Dim cell As Range

    If Intersect(Selection, ActiveSheet.Range("A1:A24")) Is Nothing Then
        MsgBox "No cells selected in A1:A24 or adjacent columns"
        Exit Sub
    End If
    
    textFileTemplate = "C:\path\to\Template.txt"  'CHANGE THIS
    outputFile = Left(textFileTemplate, InStrRev(textFileTemplate, "\")) & Format(Date, "yyyy mm dd") & ".txt"
    
    fileNum = FreeFile
    Open textFileTemplate For Binary As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] 
    fileData = Space(LOF(fileNum))
    Get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] , , fileData
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] 
    
    fileNum = FreeFile
    Open outputFile For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] 
    For Each cell In Selection
        If cell.Column = 1 Then newFileData = fileData
        If cell.Column = 1 Then newFileData = Replace(newFileData, "XXXXXXXXXX", cell.Value)
        If cell.Column = 2 Then newFileData = Replace(newFileData, "YYYYYYYYYY", cell.Value)
        If cell.Column = Selection.Columns.Count Then Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] , newFileData
    Next
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] 
        
End Sub

To make replacements for more columns, simply repeat the If cell.Column = n Then .... line for each column .

 

[Nine Zero]

Sorry for the late reply was out of the office.

This was PERFECT!

Thank you very much!