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.