Access Word Objects through Excel VBA

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.

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

 

One thought on “Access Word Objects through Excel VBA”

  1. Elle says:

    Excellent tutorial. Thanks so much for sharing!

Leave a Reply

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

privacy policy