Microsoft Office
Microsoft Access
Microsoft Excel
Microsoft PowerPoint
Microsoft Word

How to Populate a Word Document with data from Excel

Automation assistant.

Populate Word document with data from Excel

There are different ways to achieve this functionality, 1) we can create form field and insert data into the form fields, 2) enter variables in Word docuemnt and replace them with variable values.

How to create form fields:

Under developer Ribbon -> Controls group -> Click on plain text form control and in the word document place cusor where you wanted to create form control and size.

 

wordformfields

 

In this learning let us use method 2. use variables and replace them with values from Excel, because this easy and simple.

Prepare word document to populate values

wordvariables

 

Prepare Excel file

exceltoword

Please note variable map between word document and Excel document above

<a>, <b> are variables in Word and column names in Excel file.

Lets build code

following code finds and replaces text in document, we have to pass find and replace string.

objWordmust point to the word document that we prepared, you can use code

set objWord = New Word.Application, and

Set objDoc = objWord.Documents.Open(sDestFileName)

 

Sub FindreplaceWord(strFind As String, strRep As String)
objWord.Selection.Find.ClearFormatting
With objWord.Selection.Find
.Text = strFind
.Replacement.Text = strRep
.Forward = True
.Wrap = 1
.Format = False
.MatchCase = False
.MatchWholeWord = True
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
objWord.Selection.Find.Execute Replace:=2
With objWord.Selection
If .Find.Forward = True Then
.Collapse Direction:=1
Else
.Collapse Direction:=0
End If
.Find.Execute Replace:=1
If .Find.Forward = True Then
.Collapse Direction:=0
Else
.Collapse Direction:=1
End If
.Find.Execute
End With
End Sub

 

--- Write to me for any further assistance, hope you find this useful ---