In this article, we will see how to access the various word objects using VBA in Excel and insert data from Excel to Word. This has many practical applications such as when you have to fill out a form multiple times with data from Excel or when you have to create a Word document with the same structure but different data each time and so on.

Before we go through individual controls, first let us have a look at how to access a Word document in Excel.

Step 1: Get the name and path of the Word Document that you need to modify. We will use the GetOpenFilename command for that.

fileName = Application.GetOpenFilename(, , "Select the word Document")

Step 2: Check to see if Word is already running on the system.
Set oApp = GetObject(, "Word.Application")

If Word is not running already, then start it
If Err.Number <> 0 Then
    Set oApp = CreateObject("Word.Application")
End If



Step 3: Assign the selected Word file to a Word object so that we can access it.
Set oDoc = oApp.Documents.Open(fileName)
oApp.Visible = True

Now that we know how to access to the Word document, let us see how to access the various controls in Word. In each of the below examples, we will need to add the above code.
Contents

Example 1: Bookmarks
In this example, we will see how to insert data from Excel after a bookmark in Word. Let us assume you have a bookmark named “Table1” in Word, where you be inserting a table from Excel. So, for simplicity, we will name that range in Excel as “Table1”.

Step 1: Access the bookmark
Set oBkMrk = oApp.ActiveDocument.Bookmarks("Table1")

Step 2: Get the location where you want to insert the data
Set objRange = oBkMrk.Range.Characters.last ‘Position of the last character of the bookmark
objRange.Start = objRange.Start + 1 ‘We need to start pasting from the next character



Step 3: Copy the table and paste it at that location
Range(“Table1”).Copy
objRange.PasteExcelTable False, False, False

The PasteExcelTable method takes 3 arguments: LinkedToExcel, WordFormatting, RTF. We have set them all to False. Once you run the code, the Word Document will look like this 
Example 2: Text boxes
In Word, the only way to assign a name to a shape is by using VBA (in contrast to Excel where this can be done using the formula bar). Secondly, Word does not force shapes to have unique names. So, accessing a text box by its name is not a very good option. The approach we will follow is referring to shapes by their index position.
Note: We are referring to text box created using drawing controls (Insert tab)

In this example we will loop through all the text boxes in Word and modify their text.
Step 1: So, first loop through all the shapes in the Word document using the .Shapes collection

Step 2: Check if the shape is a textbox

Step 3: Manipulate the .TextFrame.TextRange.Text property, to copy text from Excel.
Dim str As String
Dim i As Integer
i = 1
For Each shp In oDoc.Shapes
    If shp.Type = msoTextBox Then
        str = ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value
        shp.TextFrame.TextRange.Text = str
        i = i + 1
    End If
Next

Here the excel values we are using are in column A, and i is used as the counter to directly access column A rows from the Excel. This is how the output will look:

Example 3: Content control Text box from Developer tab
For this we will be using the Title property of a textbox to access it. The title can be set using the properties option of a textbox from the Developer Tab. The code is very similar to that in example 2
    For Each cc In oDoc.ContentControls
        If cc.Title = "Text1" Then
            cc.Range.Text = "Hello World!"
            Exit For
        End If
    Next cc

For multiple textboxes, you can match the title of the text box to the corresponding range in Excel and easily loop through.

Example 4: Headers and Footers
This code will add Headers and Footers on all the pages of Section 1 of the Word Document. The text can easily be taken from an Excel file.
With oDoc.Sections(1)
    .Headers.Item(1).Range.Text = "Header text"
    .Footers.Item(1).Range.Text = "Footer text"
End With

Here is how the header and footer will look like.


And here’s all the above code put together for easy reference:
Sub copyToWord()

Dim oApp        As Object 'Word.Application
Dim oDoc        As Object 'Word.Document
Dim sDocName    As String
Dim path        As String
Dim fileName    As String
Dim noOfFields  As Integer
Dim varName     As String
Dim wb

fileName = Application.GetOpenFilename(, , "Select the word Document")
Set wb = ThisWorkbook

On Error Resume Next
    Set oApp = GetObject(, "Word.Application") 'See if word is already running
    If Err.Number <> 0 Then     'Word isn't running so start it
        Set oApp = CreateObject("Word.Application")
    End If

On Error GoTo Error_Handler_Exit
    Set oDoc = oApp.Documents.Open(fileName)
    oApp.Visible = True

'Bookmarks
Set oBkMrk = oApp.ActiveDocument.Bookmarks("Table1")

Set objRange = oBkMrk.Range.Characters.last
objRange.Start = objRange.Start + 1

Range("Table1").Copy
objRange.PasteExcelTable False, False, False

'Textbox
Dim str As String
Dim i
i = 1

For Each shp In oDoc.Shapes
    If shp.Type = msoTextBox Then
        str = ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value
        shp.TextFrame.TextRange.Text = str
        i = i + 1
    End If
Next

'Textbox Content Control
    For Each cc In oDoc.ContentControls
        If cc.Title = "Text1" Then
            cc.Range.Text = "Hello World!"
            Exit For
        End If
    Next cc

'Headers and Footers
With oDoc.Sections(1)
    .Headers.Item(1).Range.Text = "Header goes here"
    .Footers.Item(1).Range.Text = "Footer goes here"
End With

oDoc.Save

Error_Handler_Exit:
    On Error Resume Next
    Set oDoc = Nothing
    Set oApp = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: UpdateDoc" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit

End Sub