Automate Microsoft Word from Excel, using VBA
———————————————————————
Contents:
Automating an Office Application
Practical Examples of Automating Word from Excel
———————————————————————
Automation is a process by which one application communicates with or controls another application. This can be done in two ways: Early Binding and Late Binding. Binding is a process where you assign an object to an object variable. In this chapter, we explain “the Process of Automating an Office Application“, followed by “Examples of Automating Word from Excel“.
Automating an Office Application
Automation Process
Automation is a process by which one application communicates with or controls another application. This can be done in two ways: Early Binding and Late Binding. Binding is a process where you assign an object to an object variable.
When you use vba in an Office Application, say Word, a reference to the Word Object Library is set by default. When you Automate to work with Word objects from another application, say Excel, you can add a reference to the Word object library in Excel (your host application) by clicking Tools-References in VBE, which will enable using Word’s predefined constants. This is a must when you automate using Early Binding (explained below). Once this reference is added, a new instance of Word application can be created by using the New keyword.
Exposing the object library of the other application (viz. Word) by which its object model can be accessed by the host application (viz. Excel), means that the Word’s predefined constants are recognized when you add a reference to the Word Object Library in Excel.
Early Binding and Late Binding in Automation
In Early Binding, the object variable is declared as a specific object type ie. specific to the application which is being automated, and this makes an object early bound. In Early Binding the object library of the other application is exposed early, during design-time, in the host application. This Binding is done manually by adding a reference to the Object Library of the other application (viz. Word), in the host application (viz. Excel) by clicking “Tools” from the menu bar in VBE.
In Late Binding, the object variable is declared as an Object Type which can be a reference to any object, and this makes an object late bound. In Late Binding, the object library is not exposed during design-time, but the binding happens during run-time using the CreateObject or the GetObject functions. CreateObject creates a new instance of Word and GetObject returns an already running instance of the Word object. In Late Binding, you need not add a reference to the Word object library in Excel (your host application), in this case you will not be able to use the Word’s predefined constants and will need to replace them by their numerical values in your code.
Adding a reference to the Object Library
In Early Binding the object library is exposed during design-time and this is done manually. To add a reference to the Word library in Excel (Office 2007), click “Tools” from the menu bar in VBE, click the “References” option, and in the dialog box which opens select “Microsoft Word 12.0 Object Library” in the “Available References” list. Note that the library name would be “Microsoft Word 9.0 Object Library” in Office version 2000 ie. the name will vary for different Office versions. This is one disadvantage of using early binding because you will need to adjust per the Office version being used in your desktop viz. running a code programmed in say Office 2007, in a machine which is using an earlier version of Office might give an error because the specific object library would not be installed in that machine. Therefore in cases where the programmer is not aware at design-time as to which Office version will be used by the client to run the procedure, Late Binding should be used. Or you can also early bind to the earliest version of the application which can be expected to be used by the client viz. set a reference to the library for Excel 95 for autmotaing Excel. Running a procedure in an Office version later than in which a reference to the library is set, should be compatible.
Built-in Constants and their Numerical Values
We have explained above that the Word’s predefined constants are recognized when you add a reference to the Word library in Excel. It may be noted that replacing the Word’s built-in constants viz. wdSaveChanges, by their numerical values viz. -1 (wdSaveChanges has a numerical value of -1) will run the code without adding a reference to the Word library and in this case the procedures will work across various Office versions. To determine the values of these built-in constants, while the reference to the relevant object library has been added: (i) search in the Object Browser by typing the constant in the Search Text drop down list (in the top pane) and click on the Search button (with binoculars); or (ii) type the constant preceded by a question mark (viz. ?wdStyleNormal) and then press Enter, in the Immediate Window in VBE.
Early Binding vs. Late Binding
In Early Binding you must manually add a reference to the Word library in your Excel project at design-time. It is generally preferable to use Early Binding because the code runs faster than in Late Binding where the binding is done while the code runs. Also, in Early Binding the programmer will have access to the Word’s object model and its built-in (pre-defined) constants, and this will facilitate checking syntax errors and the use of “intellisense” ie. after typing a keyword followed by a dot you get a pop-up list of the keyword’s supported properties and methods which can be inserted by selecting the required one. However, one significant advantage of Late Binding is that it will not be dependant on the Office version being used, as explained above.
The Getobject function and CreateObject function
The Getobject function. Syntax: GetObject([file_path_file_name] [, class]). The function has two optional arguments. The first argument specifies the file to open – the file name and full filepath should be given. In case the first argument is omitted, it is required to give the second argument which specifies the class or object type to work with. Omitting the first argument in the Getobject function will return an instance of the application. The function will give an error if the application is not already running.
CreateObject function. Syntax: CreateObject(ApplicationName_Class_of_Object,[servername]). This function starts the application and creates the object, for example, CreateObject(“Word.Application”) will create an instance of the Word Application. The second argument of servername is optional which refers to the network server where to create the object and omitting this will create the object in the local machine.
Example of using Early Binding:
Variable declared as a specific object type ie. specific to the application which is being automated:
Dim applWord As Word.Application
When you use vba in an Office Application, say Word, a reference to the Word Object Library is set by default. When you Automate to work with Word objects from another application, say Excel, you can add a reference to the Word library in Excel (your host application) by clicking Tools-References in VBE, which will enable using Word’s predefined constants. This is a must when you automate using Early Binding. Once this reference is added, a new instance of Word application can be created by using the New keyword.
Set applWord = New Word.Application
Using built-in constants of the other application, ex. wdStyleNormal
applWord.Styles(wdStyleNormal).Font.Name = “Arial”
Examples of using Late Binding:
Variable declared as Object Type, which can be a reference to any object:
Dim applWord As Object
If an instance of an existing Word object is not available, CreateObject creates a new instance of Word:
Set applWord = CreateObject(“Word.Application”)
Variable declared as Object Type, which can be a reference to any object:
Dim applWord As Object
If an instance of an existing Word object is available, GetObject returns an already running instance of the Word object:
Set applWord = GetObject(, “Word.Application”)
Practical Examples of Automating Word from Excel
Example 1: Create a new word document, set page setup properties, add paragraphs, reference paragraphs by index number, format the paragraphs, with options of saving the new document in the default or a specific folder, using Early Binding.
Refer Image 1:
———————————————
———————————————
Sub Automate_Word_from_Excel_1()
‘Automate Word from Excel, using Early Binding: You must add a reference to the Word Object Library in Excel (your host application) by clicking Tools-References in VBE, which will enable using Word’s predefined constants. Once this reference is added, a new instance of Word application can be created by using the New keyword.
‘This example shows how to create a new word document, set page setup properties, add paragraphs, reference paragraphs by index number, format the paragraphs, with options of saving the new document in the default or a specific folder, using Early Binding.
‘variables declared as a specific object type ie. specific to the application which is being automated:
Dim applWord As Word.Application
Dim docWord As Word.Document
Dim paraWord As Word.Paragraph
‘Set the Application object variable to create a new instance of Word:
Set applWord = New Word.Application
‘make the Word window visible:
applWord.Visible = True
‘maximize Word window:
applWord.WindowState = wdWindowStateMaximize
‘add a new word document:
Set docWord = applWord.Documents.Add
‘to save document in the default folder:
docWord.SaveAs fileName:=“newDoc1.docx”
‘to save document in a specific folder:
‘DocWord.SaveAs fileName:=“C:\Users\Amit Tandon\Documents\ExcelTips&Tricks\Excel VBA\newDoc1.docx”
‘set Page Setup properties – Page Orientation, Page Size & Margins:
With docWord.PageSetup
.LineNumbering.Active = True
.Orientation = wdOrientPortrait
.TopMargin = applWord.InchesToPoints(1)
.BottomMargin = applWord.InchesToPoints(0.75)
.LeftMargin = applWord.InchesToPoints(0.75)
.RightMargin = applWord.InchesToPoints(0.85)
.PageWidth = applWord.InchesToPoints(8)
.PageHeight = applWord.InchesToPoints(11)
.Gutter = applWord.InchesToPoints(0.25)
.GutterPos = wdGutterPosLeft
End With
‘The Selection Property returns the the selected area in the document or if nothing is selected it represents the insertion point.
With applWord.Selection
.Font.Name = “Arial”
.Font.Size = 12
.Font.Bold = True
.Font.Color = wdColorRed
.ParagraphFormat.Alignment = wdAlignParagraphCenter
‘set spacing after this paragraph, in points:
.ParagraphFormat.SpaceAfter = 10
End With
‘use the TypeText Method to insert text into the new word document.
applWord.Selection.TypeText Text:=“Title of New Document”
‘use Paragraphs.Add Method to add a new paragraph mark at the end of the document:
docWord.Paragraphs.Add
‘Set the paragraph object to the new para added. Paragraphs(IndexNumber) is used to return the Paragraph object. Use the Count property to determine the number of paragraphs (ie. determine the last para added) in the collection of Paragraph objects. You can also use the Paragraphs.Last Property to return the last para viz. Set paraWord = docWord.Paragraphs.Last.
Set paraWord = docWord.Paragraphs(docWord.Paragraphs.count)
paraWord.Range.Text = “I bring to your notice that the bill for the month of September, 2011 for my mobile number 1234567, seems to be incorrect. I have apparantly been overcharged because there are some calls which are reflected in the bill which have not actually been made by me.”
‘format the new paragraph:
With paraWord.Range
.ParagraphFormat.Alignment = wdAlignParagraphJustify
.ParagraphFormat.LineSpacingRule = wdLineSpaceSingle
.ParagraphFormat.LeftIndent = applWord.InchesToPoints(0.25)
.ParagraphFormat.FirstLineIndent = applWord.InchesToPoints(0.5)
.ParagraphFormat.SpaceAfter = 10
With .Font
.Name = “TimesNewRoman”
.Size = 10
.Bold = False
.Color = wdColorBlue
End With
End With
docWord.Paragraphs.Add
Set paraWord = docWord.Paragraphs(docWord.Paragraphs.count)
paraWord.Range.Text = “I hereby request you to kindly rectify as above and send me the correct bill so that I can pay at the earliest. Thanking you in anticipation of a quick response.”
With paraWord.Range
.ParagraphFormat.Alignment = wdAlignParagraphJustify
.ParagraphFormat.LineSpacingRule = wdLineSpaceDouble
.ParagraphFormat.LeftIndent = applWord.InchesToPoints(0.75)
.ParagraphFormat.FirstLineIndent = applWord.InchesToPoints(1)
.ParagraphFormat.SpaceAfter = 10
With .Font
.Name = “Verdana”
.Size = 10
.Bold = False
.Color = wdColorGreen
End With
End With
‘Save and close the active word document.
docWord.Close SaveChanges:=wdSaveChanges
‘quit the word application:
applWord.Quit
‘clear the object variables:
Set docWord = Nothing
Set applWord = Nothing
Set paraWord = Nothing
End Sub
Example 2: Automate using Early Binding – Create a new word document and insert a letter with multiple paras, using built-in word styles.
Refer Image 2:
———————————————
———————————————
Sub Automate_Word_from_Excel_2()
‘Automate Word from Excel, using Early Binding: You must add a reference to the Word Object Library in Excel (your host application) by clicking Tools-References in VBE, which will enable using Word’s predefined constants. Once this reference is added, a new instance of Word application can be created by using the New keyword.
‘This example (Automate using Early Binding) shows how to create a new word document and insert a letter with multiple paras, using built-in word styles.
‘variables declared as a specific object type ie. specific to the application which is being automated:
Dim applWord As Word.Application
Dim docWord As Word.Document
‘Set the Application object variable to create a new instance of Word:
Set applWord = New Word.Application
‘make the Word window visible:
applWord.Visible = True
‘maximize Word window:
applWord.WindowState = wdWindowStateMaximize
‘add a new word document:
Set docWord = applWord.Documents.Add
‘to save document in the default folder:
docWord.SaveAs fileName:=“newDoc1.docx”
With docWord
‘A built-in or user-defined style is returned by using Styles(index). Index can be mentioned as the user-defined style name, or a WdBuiltinStyle constant or index number. You can set style properties like Font, as follows.
‘Set styles for the new word document:
With .Styles(wdStyleHeading1)
.Font.Name = “Verdana”
.Font.Size = 13
.Font.Color = wdColorRed
.Font.Bold = True
.ParagraphFormat.Alignment = wdAlignParagraphCenter
End With
With .Styles(wdStyleHeading2)
.Font.Name = “TimesNewRoman”
.Font.Size = 12
.Font.Color = wdColorBlue
.Font.Bold = True
End With
With .Styles(wdStyleNormal)
.Font.Name = “Arial”
.Font.Size = 10
.Font.Color = wdColorBlue
.ParagraphFormat.LineSpacingRule = wdLineSpaceSingle
End With
With .Styles(wdStyleBodyText)
.Font.Name = “Arial”
.Font.Size = 10
.Font.Color = wdColorGreen
.ParagraphFormat.Alignment = wdAlignParagraphJustify
.ParagraphFormat.LineSpacingRule = wdLineSpaceSingle
.ParagraphFormat.FirstLineIndent = applWord.InchesToPoints(0.5)
End With
‘Use the Range.InsertParagraphAfter Method to insert a paragraph mark. The insertion is made after the specified range. The range expands to increase the new paragraph, after applying this method.
‘Use the Range.InsertAfter Method to insert text at the end of a range.
.Range(0).Style = .Styles(wdStyleHeading1)
.Content.InsertAfter “Request for Mobile Bill Correction”
‘Insert paragraph marks at the end of the document. A Range object is returned by the Content property.
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter
‘set style starting from range after the Heading 1:
.Range(.Characters.count – 2).Style = .Styles(wdStyleHeading2)
‘Insert text, use Chr(11) to add new line:
.Content.InsertAfter “The Billing Department” & Chr(11) & “M/s MobilePhone Company” & Chr(11) & “London Office” & Chr(11) & “UK.”
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter
‘use the Paragraphs.Last Property to return the last para in the document:
.Paragraphs.Last.Style = .Styles(wdStyleNormal)
.Content.InsertAfter vbTab & “Subject: Correction of Bill for my Mobile Number 1234567.”
‘Insert paragraph mark at the end of the document. A Range object is returned by the Content property.
.Content.InsertParagraphAfter
.Paragraphs.Last.Style = .Styles(wdStyleNormal)
.Content.InsertAfter “Dear Sir,”
.Content.InsertParagraphAfter
.Paragraphs.Last.Style = .Styles(wdStyleBodyText)
.Content.InsertAfter “I bring to your notice that the bill for the month of September, 2011 for my mobile number 1234567, seems to be incorrect. I have apparantly been overcharged because there are some calls which are reflected in the bill which have not actually been made by me.”
.Content.InsertParagraphAfter
.Paragraphs.Last.Style = .Styles(wdStyleBodyText)
.Content.InsertAfter “I hereby request you to kindly rectify as above and send me the correct bill so that I can pay at the earliest. Thanking you in anticipation of a quick response.”
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter
.Paragraphs.Last.Style = .Styles(wdStyleNormal)
.Content.InsertAfter “Yours Sincerely,”
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter
.Paragraphs.Last.Style = .Styles(wdStyleNormal)
.Content.InsertAfter “Alan Croft”
.Content.InsertParagraphAfter
End With
‘close and save the document:
docWord.Close SaveChanges:=wdSaveChanges
‘quit the word application:
applWord.Quit
‘clear the object variables:
Set docWord = Nothing
Set applWord = Nothing
End Sub
Example 3: Open an Existing word doc, insert text, format doc and save, and then close & quit application, using Late Binding.
Sub Automate_Word_from_Excel_3()
‘Automate Word from Excel, using Late Binding. You need not add a reference to the Word library in Excel (your host application), in this case you will not be able to use the Word’s predefined constants and will need to replace them by their numerical values in your code.
‘This example shows how to open an existing word doc, insert text, format doc and save, and then close & quit application, using Late Binding.
‘Variables declared as Object type, which can be a reference to any object. Variables cannot be declared as a specific object type (ie. specific to the application which is being automated) using Word.Application or Word.Document, because a reference to the Word library has not been added.
Dim applWord As Object
Dim docWord As Object
‘set the Application object variable to create a new instance of Word:
Set applWord = CreateObject(“Word.Application”)
‘make the Word window visible:
applWord.Visible = True
‘maximize Word window:
‘replaced the Word’s built-in constant wdWindowStateMaximize with its numerical value 1.
applWord.WindowState = 1
‘opens an existing word document from a defined folder:
Set docWord = applWord.Documents.Open(“C:\Users\Amit Tandon\Documents\Friends.docx”)
‘Insert paragraph mark at the end of the document. A Range object is returned by the Content property.
docWord.Content.InsertParagraphAfter
‘insert text at the end of the document:
docWord.Content.InsertAfter “Inserted text at the end of document on ” & Now
‘A built-in or user-defined style is returned by using Styles(index). Index can be mentioned as the user-defined style name, or a WdBuiltinStyle constant or index number. You can set style properties like Font, as follows.
‘Set style in the new word document: Note that the Word’s predefined constants wdStyleNormal and wdColorRed are not recognized because reference to the Word library has not been added. In this case the following code will not run and the style cannot be set with the following code:
‘docWord.Styles(wdStyleNormal).Font.Color = wdColorRed
‘Set style for the word document:
‘replaced the Word’s built-in constants wdStyleNormal and wdColorRed with their numerical values -1 and 255.
docWord.Styles(-1).Font.Name = “Arial”
docWord.Styles(-1).Font.Size = 10
docWord.Styles(-1).Font.Color = 255
‘close and save the document:
‘replaced the Word’s built-in constant wdSaveChanges with its numerical value -1.
docWord.Close SaveChanges:=-1
‘quit the word application:
applWord.Quit
‘clear the object variables:
Set docWord = Nothing
Set applWord = Nothing
End Sub
Example 4: Automate using Late Binding – Create a new word document, insert a letter by sourcing data from an excel worksheet, using built-in word styles.
For live code of this example, click to download excel file.
Sub Automate_Word_from_Excel_4()
‘Automate Word from Excel, using Late Binding. You need not add a reference to the Word library in Excel (your host application), in this case you will not be able to use the Word’s predefined constants and will need to replace them by their numerical values in your code.
‘This example (Automate using Late Binding) shows how to create a new word document, insert a letter by sourcing data from an excel worksheet, using built-in word styles. This method is particularly useful for making letters with similar content being addressed and emailed to multiple addresses.
‘Variables declared as Object type, which can be a reference to any object. Variables cannot be declared as a specific object type (ie. specific to the application which is being automated) using Word.Application or Word.Document, because a reference to the Word library has not been added.
Dim applWord As Object
Dim docWord As Object
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets(“Sheet3”)
‘Create a new instance of the Word application, if an existing Word object is not available.
‘Set the Application object as follows:
On Error Resume Next
Set applWord = GetObject(, “Word.Application”)
‘if an instance of an existing Word object is not available, an error will occur (Err.Number = 0 means no error):
If Err.Number <> 0 Then
Set applWord = CreateObject(“Word.Application”)
End If
‘disable error handling:
On Error GoTo 0
‘make the Word window visible:
applWord.Visible = True
‘maximize Word window:
‘replaced the Word’s built-in constant wdWindowStateMaximize with its numerical value 1.
applWord.WindowState = 1
‘add a new word document:
Set docWord = applWord.Documents.Add
‘to save document in the default folder:
docWord.SaveAs fileName:=“newDoc1.docx”
With docWord
‘A built-in or user-defined style is returned by using Styles(index). Index can be mentioned as the user-defined style name, or a WdBuiltinStyle constant or index number. You can set style properties like Font, as follows.
‘Set styles for the new word document:
‘replaced the Word’s built-in constant wdStyleHeading1 with its numerical value -2.
With .Styles(-2)
.Font.Name = “Verdana”
.Font.Size = 13
‘replaced the Word’s built-in constant wdColorRed with its numerical value 255.
.Font.Color = 255
.Font.Bold = True
‘replaced the Word’s built-in constant wdAlignParagraphCenter with its numerical value 1.
.ParagraphFormat.Alignment = 1
End With
‘replaced the Word’s built-in constant wdStyleHeading2 with its numerical value -3.
With .Styles(-3)
.Font.Name = “TimesNewRoman”
.Font.Size = 12
‘replaced the Word’s built-in constant wdColorBlue with its numerical value 16711680.
.Font.Color = 16711680
.Font.Bold = True
End With
‘replaced the Word’s built-in constant wdStyleNormal with its numerical value -1.
With .Styles(-1)
.Font.Name = “Arial”
.Font.Size = 10
‘replaced the Word’s built-in constant wdColorBlue with its numerical value 16711680.
.Font.Color = 16711680
‘replaced the Word’s built-in constant wdLineSpaceSingle with its numerical value 0.
.ParagraphFormat.LineSpacingRule = 0
End With
‘replaced the Word’s built-in constant wdStyleBodyText with its numerical value -67 .
With .Styles(-67)
.Font.Name = “Arial”
.Font.Size = 10
‘replaced the Word’s built-in constant wdColorGreen with its numerical value 32768.
.Font.Color = 32768
‘replaced the Word’s built-in constant wdAlignParagraphJustify with its numerical value 3.
.ParagraphFormat.Alignment = 3
‘replaced the Word’s built-in constant wdLineSpaceSingle with its numerical value 0.
.ParagraphFormat.LineSpacingRule = 0
.ParagraphFormat.FirstLineIndent = applWord.InchesToPoints(0.5)
End With
‘Use the Range.InsertParagraphAfter Method to insert a paragraph mark. The insertion is made after the specified range. The range expands to increase the new paragraph, after applying this method.
‘Use the Range.InsertAfter Method to insert text at the end of a range.
‘replaced the Word’s built-in constant wdStyleHeading1 with its numerical value -2.
.Range(0).Style = .Styles(-2)
.Content.InsertAfter “Request for Mobile Bill Correction”
‘Insert paragraph marks at the end of the document. A Range object is returned by the Content property.
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter
‘set style starting from range after the Heading 1:
‘replaced the Word’s built-in constant wdStyleHeading2 with its numerical value -3.
.Range(.Characters.count – 2).Style = .Styles(-3)
‘Insert text, use Chr(11) to add new line:
.Content.InsertAfter ws.Range(“A7”) & Chr(11) & ws.Range(“B7”) & Chr(11) & ws.Range(“C7”) & Chr(11) & ws.Range(“D7”) & Chr(11) & ws.Range(“E7”) & Chr(11) & ws.Range(“F7”)
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter
‘use the Paragraphs.Last Property to return the last para in the document:
‘replaced the Word’s built-in constant wdStyleNormal with its numerical value -1.
.Paragraphs.Last.Style = .Styles(-1)
.Content.InsertAfter vbTab & ws.Range(“A1”)
‘Insert paragraph mark at the end of the document. A Range object is returned by the Content property.
.Content.InsertParagraphAfter
‘replaced the Word’s built-in constant wdStyleNormal with its numerical value -1.
.Paragraphs.Last.Style = .Styles(-1)
.Content.InsertAfter “Dear Sir,”
.Content.InsertParagraphAfter
‘replaced the Word’s built-in constant wdStyleBodyText with its numerical value -67 .
.Paragraphs.Last.Style = .Styles(-67)
.Content.InsertAfter ws.Range(“A3”)
.Content.InsertParagraphAfter
‘replaced the Word’s built-in constant wdStyleBodyText with its numerical value -67 .
.Paragraphs.Last.Style = .Styles(-67)
.Content.InsertAfter ws.Range(“A5”)
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter
‘replaced the Word’s built-in constant wdStyleNormal with its numerical value -1.
.Paragraphs.Last.Style = .Styles(-1)
.Content.InsertAfter “Yours Sincerely,”
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter
‘replaced the Word’s built-in constant wdStyleNormal with its numerical value -1.
.Paragraphs.Last.Style = .Styles(-1)
.Content.InsertAfter “Alan Croft”
.Content.InsertParagraphAfter
End With
‘close and save the document:
‘replaced the Word’s built-in constant wdSaveChanges with its numerical value -1.
docWord.Close SaveChanges:=-1
‘quit the word application:
applWord.Quit
‘clear the object variables:
Set docWord = Nothing
Set applWord = Nothing
End Sub