VBA, Automating Word From Excel
There are 2 methods for initiating a word application from excel using VBA:
- Early Binding
- Late Binding
Basically in early binding we define what object we are creating reference to before program execution. In late binding, the program won’t know what we are creating reference to until execution has started. Each method has pros and cons:
- Early binding has better performance.
- When you use early binding intellisense will work in the editor.
- When using early binding you have to specify what version of word you will be using. If the application is taken to another computer with a different word version the code will not run.
Contents
Early Binding:
In the first example I will use early binding to automate a word application from excel.
Step 1: Click on the reference button from the tools drop down menu:
Step 2: Search for the microsoft word object library. At the time of writing this post, word 2010 was installed on my computer, therefore the Microsoft Word 14.0 Object Library was installed on my system. If you have a different version of word installed, there will be another object library in this list:
Step 3: Place the following code in the Excel VBA editor:
Sub main()
Dim objWord As Word.Application
Dim objDoc As Word.Document
Set objWord = New Word.Application
objWord.Visible = True
Set objDoc = objWord.Documents.Add
End Sub
After a few seconds a word application with an empty document will appear. As mentioned before this method will not run on a system with a different version of word installed.
Late Binding:
In this method there is no need for adding references. All you need to do is copy the following code in the Excel VBA editor:
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
As mentioned before, although late binding is less efficient than early binding, it will run on any system that has word installed, regardless of the version.
Common Errors:
When automating a word document from another application keep in mind that you will not be able to use many of the variables previously used in word directly. For example the variables below:
ActiveDocument
Selection
Must be replaced with:
objWord.ActiveDocument
objWord.Selection
Where objWord, is a reference to the Word. Application object. For more information about this topic please see the link below:
You can download the code and file related to this article from the link 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, Write Excel Values to Word Document
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