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 = TrueNow 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 locationRange(“Table1”).Copy objRange.PasteExcelTable False, False, FalseThe 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 NextHere 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 ccFor multiple textboxes, you can match the title of the text box to the corresponding range in Excel and easily loop through.
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 WithHere 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”