(Solved) – Why does the values in cells (Excel) aren’t replacing the variables in the Words files?

(solved)-–-why-does-the-values-in-cells-(excel)-aren’t-replacing-the-variables-in-the-words-files?

I made an excel file that the user should fill in one column details, after he finishes he is clicking on a button that generate three Word files.
At the Excel I named the cells where the details of the user were written. at the word files I placed the variables (the names of the cells) where I wanted.
every things is working well beside the replacment between the details that the user enters to the variables in the words files.

Sub createPDF()
Application.ScreenUpdating = False
Dim objWord As Object
Dim ws As Worksheet
Dim theString As String
Dim TemplatePath As String
Dim xWb As Workbook
Dim Pscope As String
'ws.Activate
Set ws = ThisWorkbook.ActiveSheet
Set objWord = CreateObject("Word.Application")
Set xWb = Application.ThisWorkbook
TemplatePath = xWb.Path
objWord.Visible = True

'Target File Extension (must include wildcard "*")
  myExtension = "*.doc*"

'Target Path with Ending Extention
  myfile = Dir(TemplatePath   "Template" & "" & myExtension)

'Loop through each word file in folder
Do While myfile <> ""
objWord.Documents.Open TemplatePath   "Template" & "" & myfile 'TemplatePath   "ProposalTemplate.dotm" ' change as required



With objWord.ActiveDocument.Content.Find

.Text = "company_ename"
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("company_ename").Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll


.Text = "owner_fname1"
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("owner_fname1").Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll

.Text = "owner_pname1"
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("owner_pname1").Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll

.Text = "owner_fullname1"
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("owner_fullname1").Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll

.Text = "owner_id1"
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("owner_id1").Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll

.Text = "owner_allotted1"
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("owner_allotted1").Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll


For i = 2 To 4
.Text = "owner_fname" & CStr(i)
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("owner_fname" & CStr(i)).Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll

.Text = "owner_pname" & CStr(i)
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("owner_pname" & CStr(i)).Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll

.Text = "owner_fullname" & CStr(i)
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("owner_fullname" & CStr(i)).Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll

.Text = "owner_id" & CStr(i)
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("owner_id" & CStr(i)).Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll

.Text = "owner_allotted" & CStr(i)
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("owner_allotted" & CStr(i)).Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll


Next i

.Text = "house"
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("house").Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll

.Text = "director_pname1"
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("director_pname1").Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll

.Text = "director_fname1"
.MatchCase = False
.MatchWholeWord = True
.replacement.Text = ws.Range("director_fname1").Value
.wrap = wdfindcontinue
.Execute Replace:=wdReplaceAll


End With

Dim TheFileName As String
        TheFileName = TemplatePath   "Output"   ws.Range("company_ename").Value   "_"   Replace(myfile, "docx", "")   ".docx"

        '(SaveAs is for Office 2003 and earlier - deprecated)
        objWord.ActiveDocument.SaveAs TheFileName
            'replaces existing .doc iff exists


        ' Close Documents and Quit Word
        objWord.ActiveDocument.Close savechanges:=False
       ' objWord.ActiveDocument.Close 'close .DOCx
 myfile = Dir
Loop
Set objWord = Nothing

MsgBox "Generation Complete!"
Application.ScreenUpdating = True
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *