The macro recorder is limited in what it will allow you to recordThe mouse cannot be used to move the cursor or select objects but there are no limits on doing so with the keyboard

The following example is what the Word macro recorder produces when adding a new, blank document.

Documents.Add Template:="Normal", NewTemplate:=False, DocumentType:=0

Making this more efficient (still in Word) produces this:


Template, NewTemplate, and DocumentType are all optional properties that the recorder includes but are not required unless you need to change a default property or ensure that a property is as you require.

To use the same line of code in Excel, a link to the Word object library is required, as we learned earlier. After that link is established, an understanding of Word's objects is all you need. Following is a review of some of Word's objects—enough to get you off the ground. For a more detailed listing, refer to the object model in Word's VBE.

Document Object

Word's Document object is equivalent to Excel's Workbook object. It consists of characters, words, sentences, paragraphs, sections, and headers/footers. It is through the Document object that methods and properties affecting the entire document, such as printing, closing, searching, and reviewing, are accomplished.


To create a blank document in an existing instance of Word, use the Add method (we already learned how to create a new document when Word is closed—refer to GetObject and CreateObject):

Sub NewDocument()

Dim wdApp As Word.Application

Set wdApp = GetObject(, "Word.Application")


Set wdApp = Nothing End Sub

This example opens a new, blank document that uses the default template. To create a new document that uses a specific template: wdApp.Documents.Add Template:="Contemporary"

This creates a new document that uses the Contemporary Memo template. Template can be either just the name of a template from the default template location or the file path and name.


To open an existing document, use the Open method. Several parameters are available, including Read Only and AddtoRecentFiles. The following example opens an existing document as Read Only, but prevents the file from being added to the Recent File List under the File menu:

wdApp.Documents.Open _

Filename:="C:\Excel VBA 2003 by Jelen & Syrstad\Chapter 17 - Arrays.Doc", _ ReadOnly:=True, AddtoRecentFiles:=False

After changes have been made to a document, you most likely will want to save it. To save a document with its existing name:


If the Save command is used with a new document without a name, the Save As dialog box appears. To save a document with a new name, you can use the SaveAs method instead.

wdApp.ActiveDocument.SaveAs "C:\Excel VBA 2003 by Jelen & Syrstad\MemoTest.doc"

0 0

Post a comment