In this article I will explain how you can write data from an excel workbook to a word document. The first step in doing this is to determine where the code is going to be written. There are 3 options:

1. In the excel workbook
2. In the word document
3. In a 3rd file

I will only consider the first two cases in this article.

Example 1, Excel VBA:

Lets assume we have an excel workbook with the following data:

Lets assume we are going  to write the code in the excel workbook.

Step 1:

The first step would be to automate a word document.  In the article below I’ve explained there are 2 methods for this.

1. Early binding
2. Late binding

To prevent compatibility issues I will be using late binding in this example:

• VBA, Automating Word From Excel

The function below will automate a word document and make it visible:

Sub main()
Dim objWord As Object
Dim objDoc As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.documents.Add()
End Sub


Step 2:

The code below will write data from the excel sheet to the word document:

Dim i As Integer
Dim strValue As String
For i = 1 To 5
    'bring focus to the document created
    objDoc.Activate
    'read the value from the cell
    strValue = Cells(i + 1, 1)
    'write the value to the document
    objWord.Selection.TypeText Text:=strValue
    'move to the next line
    objWord.Selection.TypeParagraph
Next i


Complete Version:

Below you can see the complete code. It automates a word document and writes the values from the cells B2:B6 to it:

Option Explicit
Sub main()
Dim objWord As Object
Dim objDoc As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.documents.Add

Dim i As Integer
Dim strValue As String
For i = 1 To 5
    'bring focus to the document created
    objDoc.Activate
    'read the value from the cell
    strValue = Cells(i + 1, 1)
    'write the value to the document
    objWord.Selection.TypeText Text:=strValue
    'move to the next line
    objWord.Selection.TypeParagraph
Next i
End Sub


Result:

Example 2, Word VBA:

In this example the code will be written inside a word document. Therefore the excel workbook will be automated.

Step 1:

The first step would be to get the path of the excel workbook from the user. This can be done using an open file dialog. I have covered this topic in the article below:

• Excel VBA, Open File Dialog

Although the article was written for excel, the concept can be used in VBA for Word too. The code below will display an open file dialog and ask the user to select the path of the file to open. The path will be stored in the variable strPath:

Sub Example2()
Dim intChoice As Integer
Dim strPath As String

'only allow the user to select one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
    'get the file path selected by the user
    strPath = Application.FileDialog( _
        msoFileDialogOpen).SelectedItems(1)
End If
End Sub


Result:

Step 2:

The function below receives as input a file path and automates that excel workbook:

Private Sub AutomateExcel(ByVal strPath As String)
Dim objExcel As Object
Dim objWorkbook As Object

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.workbooks.Open(strPath)
End Sub


 Step 3: 

The function below receives as input an reference to the excel workbook. It clears all the data in the current word document and it reads the values from the workbook:

Private Sub ReadData(ByRef objWorkbook As Object)
Dim i As Integer

Selection.WholeStory
Selection.Delete Unit:=wdCharacter, Count:=1
For i = 1 To 5
    Selection.TypeText Text:= _
        objWorkbook.sheets(1).Cells(i + 1, 1)
    'move to the next line
    Selection.TypeParagraph
Next i
End Sub


Complete Version:

By putting it all together we end up with the code below:

Option Explicit

Sub Example2()
Dim intChoice As Integer
Dim strPath As String

'only allow the user to select one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
    'get the file path selected by the user
    strPath = Application.FileDialog( _
        msoFileDialogOpen).SelectedItems(1)
    Call AutomateExcel(strPath)
End If
End Sub

Private Sub AutomateExcel(ByVal strPath As String)
Dim objExcel As Object
Dim objWorkbook As Object

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.workbooks.Open(strPath)
Call ReadData(objWorkbook)
End Sub

Private Sub ReadData(ByRef objWorkbook As Object)
Dim i As Integer
Selection.WholeStory
Selection.Delete Unit:=wdCharacter, Count:=1
For i = 1 To 5
    Selection.TypeText Text:= _
        objWorkbook.sheets(1).Cells(i + 1, 1)
    'move to the next line
    Selection.TypeParagraph
Next i
End Sub


Result:

You can download the file and code related to this article from the links below:

• Excel to Word.docm
• Excel to Word.xlsm

See also:

• Word VBA, Open Document
• Word Automation VBA, Common Errors
• Word VBA, Apply Macro to Multiple Files
• Word VBA, Modify Header For Multiple Files
• Word Automation VBA, Common Errors
• VBA, Automating Word From Excel

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com