VBA, Write Excel Values to Word Document
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:
- In the excel workbook
- In the word document
- 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.
- Early binding
- Late binding
To prevent compatibility issues I will be using late binding in this example:
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:
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:
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:
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
2 thoughts on “VBA, Write Excel Values to Word Document”