VBA Excel MailMerge to Word

[milton92]

Dear guys

I am making an Excel file with VBA which is allowed me to Find Text in a Word Template file and Replace with correspondent data (table) in Excel like MailMerge then save to docx files.

I have a code below. When I run it, the docx files do come out with Names as I want but the content of them just keep <<Name>> of the Word Template file without replacing at all. Please see the attached file for details.

Could you please tell me where should I change in these codes to get Word files replaced with my Data on Excel Files??

Thank you.

Code:
Sub Test()


Dim DocRow As Long, DocCol As Long
Dim i As Long, j As Long
Dim Template As Object
Dim t As Object


DocCol = 8


DocRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row - 1


With CreateObject("word.application")
.Visible = False


For i = 1 To DocRow
Set Template = .documents.Open("C:\Test\MailMergeTest.docx")
Set t = Template.Content
For j = 1 To DocCol
t.Find.Execute _
FindText:=Sheet1.Cells(1, j).Value, _
ReplaceWith:=Sheet1.Cells(i + 1, j).Value, _
Replace:=wdReplaceAll
Next
Template.SaveAs FileName:=ThisWorkbook.Path & Application.PathSeparator & Cells(i + 1, 1).Value & ".docx"
Next
.Quit
End With
Set t = Nothing
Set Template = Nothing

End Sub

-----‐----------------
Moreover, I am trying to make Excel Replace Text in Word and preserve its Format. Ex: 30,000 or 5%. However, Word just displays as 30000 and 5 instead now.

[gmayor]

Why not simply use mail merge? From your description that is what mail merge does.

With reference to the formatting, Word reads the underlying data from Excel, but you can address that with formatting switches - https://www.gmayor.com/formatting_word_fields.htm

If you want separate documents see  https://www.gmayor.com/MergeAndSplit.htm (https://www.gmayor.com/MergeAndSplit.htm) or https://www.gmayor.com/email_merge_addin.html (https://www.gmayor.com/email_merge_addin.html)

[macropod]

You can, of course, run the mailmerge via VBA directly from Excel. See, for example, Run a Mailmerge from Excel, Sending the Output to Individual Files in the Mailmerge Tips and Tricks thread at: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html