Sometimes, as an Access developer you need a more powerful reporting tool than Access’s internal reporting engine. Microsoft Word can give you the power that you need. Cindy Meister’s article covers the topics that every Access developer needs to know about Microsoft Word.
Your client wants you to use small caps or strike-through or subscripts or superscripts in your report. He would also like italics and bold formatting in the report body text, which is, by the way, blocks of variable-length text and needs to wrap and break across pages. The report is long, requires odd-even pages with mirror margins, the page orientation switches between portrait and landscape, and the headers and footers dynamically change with each section. This is the report from hell.
I don’t know if you could do all that in Access, but even if you could, you’re looking at many hours (and dollars) of development. There’s a better alternative: You could use Word, which will handle all of these requirements without writing a line of code.
Sound good to you? But you’re an Access person, not a “Wordie”: Where do you start? I realize that you don’t have the time (or inclination) to learn about Microsoft Word in depth. In this article, I’ll show you the things that you need to know so that you don’t waste valuable time going down a dead-end street or programming features already built into the application.
There are four key features in Word that significantly reduce the amount of coding you need to do when generating a formatted document: templates, styles, fields, and bookmarks. I’ve included examples of all of these in the accompanying Download file. Templates let you set up your document format ahead of time. Styles let you create a name for a set of formatting characteristics that you can then apply to any part of your report. Fields and bookmarks give you places to insert text or define a body of text that you can manipulate. Fields can also contain pre-defined functionality that will be automatically processed for you.
Templates are those .DOT files that you see in your Word directories. In Figure 1, the document on the left-hand side is a template. All the formatting that you see in the document on the right could have been handled by the template without any code, leaving you free to do what you do best: handling Access data. The data displayed in the form comes from a slightly modified Northwind database that I’ve included in this month’s Subscriber Download file. Also included in the download is a sample Word template file, Sample.DOT, whose magic you see in Figure 1.
You’re probably already familiar with the concept of a template. Templates provide boilerplate text and basic formatting for new documents. In addition, a Word template is a container for the styles that you want to use in your report (as well as macros, toolbars, keyboard shortcuts, and AutoText entries). A document created from a specific template is still linked (attached) to the template, and has access to the items in the template through the link. The styles in the template are also copied into the document and saved with it (which allows you to distribute the document without the template and still have it display correctly).
Access also has templates, but they’re considerably more limited than Word’s. One important shortcoming of Access templates is that you may have only one designated template each for forms and reports in a database. The number of templates Word can use to create new documents is virtually unlimited.
To create a new document from a template is simple. First, you must add a reference to the Microsoft Word type library to your references list. Then you need to start up Word, access its Documents collection, and add a new document based on your template. The code would look like this:
Dim wrd As Word.Application Set wrd = New Word.Application wrd.Documents.Add Template:="C:\MyApp\MyTemp.Dot"
So how do you set up a template? It’s very much like creating a Word document. Start Word, select File | New, and then, in the lower right-hand corner of the New dialog box that appears, check Template. You can now add and modify items in your document as you would with a Word document.
As an example, the template in Figure 1 includes a logo, address, and phone information to be printed only on the first page of the report. To make this happen, I placed that material in the document’s First Page Header (you can turn on the First page Header from the File | Page Setup | Layout menu choice by checking the Different First Page option). This feature lets me have one header on the first page and a completely different one for all of the following pages in my report–all without code.
To add the header for subsequent pages, I pressed Ctrl-Enter to insert a manual page break and then modified the header of the resulting page. When you’re finished creating the second page header, simply delete the page break; Word won’t lose the information about the following pages, and your headers will appear automatically as soon as the document has more than one page.
The recipient’s address and the date are positioned in frames (available from the Forms toolbar). I used these frames for three reasons:
- The frame can be positioned so that the text slightly overlaps the header.
- The frame’s position is fixed on the page so that the address will appear in an envelope window.
- The body of the document won’t be affected by the number of lines required by the address, since the frame’s height is formatted to an “exact” value.
Word (and Excel, for that matter) gives you the ability to create complex styles in the user interface and then apply those styles to your report from your code. A style is a group of attributes you can apply in one step. Styles let you control your document’s format from a central point of control because changing a style’s definition will automatically change all document elements formatted with it to match the new formatting. Styles further enhance this control by cascading. As an example, an ItemList style (used in the body of a document) can underlie the ItemListHeader style (used in your headers), which itself is the basis for the ItemListTotal (used at the end of a report). If you wish to change the font for all three parts of the item list, simply change it in the ItemList style and it will propagate to the other two. The concept of how styles work is a lot like using constantsin programming.
Creating the styles in your templates that you want to use in your report is easy, because Word allows you to create and change paragraph styles “by example.” Simply select or create a paragraph with the desired formatting, then click in the Style box on the Formatting toolbar. Type in (or select) the style’s name, and press Enter. For even finer control, or to create character styles and cascading styles, you can use the Format Style dialog box.
Once you’ve set up a style, you can apply it from your code to any object that has a Style property. For instance, the code to set the Style property for the first paragraph in the document to a style called “MyFormat” is:
Paragraphs(1).Style = "MyFormat"
Word also comes with a number of predefined styles. You can refer to these styles using a set of predefined constants (wdStyleBodyText, and wdStyleDate, for instance) or by name (“Body Text”, “Date”). If you’re working in an international environment and using Word’s built-in styles, be sure to use the constants, as the Style name changes according to the local language.
When you insert information into your Word document, you’ll need a target in order to position it. For individual pieces of data that stand alone, you can add Custom Document Properties and DocProperty fields to your template, as described in Helen Feddema’s article “Four Ways to Merge to Word” in the November 1999 issue of Smart Access. However, a document property can accept a maximum of only 127 characters. For working with larger chunks of data, bookmarks are a better solution.
In addition to marking a location in your template, a bookmark can also be used to define a set of text that you can manipulate. You can use the second kind of bookmark in your template to define an area that you can manipulate from code. In order to create a bookmark that encloses text, select at least one character in your template and then use the Insert | Bookmark menu command to create the bookmark. The sample template file has a [ItemList] bookmark that contains information. The marker that looks like an I-beam just above the last line in the template is a bookmark that has no content.
In your code, when you want to insert text into your bookmark, you have a couple of ways of going about it. I recommend this code (ActiveDocument refers to the document that Word is currently processing):
If ActiveDocument.Bookmarks.Exists( _ "BookmarkName") Then ActiveDocument.Bookmarks( _ "BookmarkName").Range.Text = "My Data" End If
The other techniques are less efficient because they don’t manipulate the Word objects. The concept of selection is also important in Word, because it controls which part of the document you’re working with. This technique doesn’t move the selection in the document.
VBA doesn’t provide many intrinsic methods of directly checking for the existence of things, so I often have to use elaborate error-trapping code when accessing something that might or might not be present. But in Word, for bookmarks (and Tasks and Headers/Footers), Microsoft has provided the Exists method that I used in my previous example.
Commonly included information like dates or page count can be kept accurate without having to write code. Word provides over 80 fields that you can insert into your template to take over the most common tasks for you. I’ve provided an overview of the most useful fields in Table 1.
Table 1. The 10 most useful fields when creating reports with Word.
|Ref||Display the contents of a bookmark.|
|DocProperty||Display the contents of a document property.|
|Date / CreateDate / Save/Date / PrintDate||Display the current date / date the document was created / date the document was last saved / date the document was last printed.|
|Page / NumPages||Display the current page / total number of pages.|
|FileName||Display the file name of the current document. With the \p switch, also the complete path.|
|UserName / UserInitials||Display the user name / user initials as defined in the Word environment (Tools/Options/UserInfo).|
|Database||Link to and display information from a database table or query as a Word table (formatting limited).|
|IncludeText / IncludePicture||Link to and display the contents of a word-processing / graphics file.|
|Link||Link to and display an object from an OLE-server (Excel spreadsheet, chart, etc).|
|Seq||Generate a numbering sequence. Handy alternative to Word’s built-in numbering.|
When setting up a template, the easiest way to add fields is to use the Insert | Field menu choice. Table 2 lists some of the VBA code that you can use to manipulate fields from Access (I’ve also included the keystrokes to use for editing fields when creating a template). For more about what each field does as well as its code syntax, check Word’s Help files.
Figure 2 shows my sample template with the field codes displayed so that you can see how I’ve used them. The date field under the logo automatically generates the current date. The format of the date is set by the string following the picture switch \@. In addition, all of the items you see in <brackets> are DocProperty fields that will display the information that I pass in from Access.
Table 2. VBA syntax and keystrokes for working with Word fields.
|Update fields in current selection.||<Object>.Fields.Update||F9|
|Insert field brackets||<Object>.Fields.Add Range:= <Range object>||Ctrl-F9|
|Toggle field code display||View.ShowFieldCodes = True/False||Alt-F9|
|Turn field code into plain text||<Object>.Fields.Unlink||Ctrl-Shift-F9|
|Lock field to prevent updating||<Object>.Fields.Locked = True||Ctrl-F11|
|Unlock field||<Object>.Fields.Locked = False||Ctrl-Shift-F11|
The IF field in the figure switches a label between “order” or “inquiry” based on the order type. I also have an IncludePicture field that inserts the signature of the Northwind representative who processed the customer request. All of this functionality was generated in a few minutes in Word’s user interface–I have yet to write a line of code.
Word fields do have their limitations, though. It’s not possible to create user-defined fields, for instance, nor can you use VBA functions in Word fields. You’re limited to what Word offers, but that can still be very powerful. Word will let you nest fields up to 19 levels. By nesting IF fields, you can perform difficult comparisons, and nesting Expression fields can let you do complex calculations.
In its own way, Word is just as powerful and complex as Access. As a long-time Word power-user and developer, the features and the object model are obvious to me. I’m continually nonplussed when I see how many database developers approach the task of automating Word.
Most programmers just use the Macro Recorder when they start programming with a component like Word. This is fine for storing and replaying sets of repeated actions for the average user, but totally inadequate as a programming code generator. Why? Because it emulates what the insertion point and menu commands do, rather than manipulating Word’s objects directly.
Compare the two sets of code in Listing 1 and Listing 2. As a person coming from outside Word, which do you find easier to understand? Almost certainly, your answer is Listing 1, where you have a single line of code. Reading the code, it’s obvious at a glance that the second word in the current document will be formatted as bold. For the same task, the Macro Recorder generates four lines. To understand this code you mentally need to walk through each line, visualizing what’s happening on the screen in order to get an idea of what the code is accomplishing. In addition, the Recorder code is slower, the screen will flicker, and you can’t be certain in the end whether that second word will be bold or not!
Listing 1. Word object model code.
ActiveDocument.Words(2).Bold = True
Listing 2. Word Macro Recorder code.
Selection.HomeKey Unit:=wdStory Selection.MoveRight Unit:=wdWord, Count:=1 Selection.MoveRight Unit:=wdWord, Count:=1, _ Extend:=wdExtend Selection.Font.Bold = wdToggle.
As a programmer, you should use Word’s Macro Recorder only as a tool to help you understand the information in the Object Browser and Word’s object model diagram. The Macro Recorder will help you discover the name of an object, property, or method so that you can get further information on how to use it from Help or another reference source.
And yet, I encounter numerous programmers who shrug their shoulders and say, “So what? Speed isn’t important; if what I record with the Macro Recorder works, why shouldn’t I just use that? It costs me too much time to figure out the object model.”
The first reason is that the resulting code might not work. When you start Word from Access, you normally keep Word’s Visible property set to False to hide it from your users. Unfortunately, the Macro Recorder code uses Word’s Selection object extensively, and there are problems with using the Selection object when Word isn’t visible. These problems can be avoided by working directly with the object model instead.
There’s another problem with Macro Recorder code: When you’re inserting and formatting lots of information in Word (which can easily happen if you’re generating a report), you might find yourself running into error messages such as, “The formatting in this document is too complex. Save your document.” That will stop execution of your code dead until someone manually takes action. You can’t choose to ignore this message, and you can’t trap it. But, by using Word’s object model, you encounter the message as rarely as possible.
One last problem with the Macro Recorder code is its dependence on the Selection object. Compare the two sets of code in Listing 3 and Listing 4. The goal is to put the formatted main story text of an existing document into a newly created document. The object model solution uses object variables to specify what’s being manipulated so that the content of the source document is duplicated directly in the target document without going through the Clipboard. After the process is complete, the source document window is closed.
The Macro Recorder code uses the Clipboard to transfer the data, an unnecessary step. The code also records the index number of the source document window, which happened to be the first window in the collection when I ran the Recorder. When the time comes to close the source document, this code closes window #1. If the source document window isn’t window #1 when you run this code, the wrong document will be closed.
Listing 3. Object model-based code.
Set docSource = ActiveDocument Set docTarget = Documents.Add docTarget.Content.FormattedText = _ docSource.Content.FormattedText docSource.Close
Listing 4. Macro Recorder code.
Selection.WholeStory Selection.Copy Documents.Add Selection.Paste Windows(1).Activate ActiveWindow.Close Manipulating your document
Earlier in this article, I showed you the code to create your document from a template. With the document created, you now need to know how to manipulate it from your Access application. As long as you’re in the immediate Word environment, you can use the ActiveDocument object with relative impunity to manipulate your document (see my bookmark code as an example).
The problem is that your code might make another document the ActiveDocument. It’s much safer, therefore, to set your document to an object variable directly and work with that. In the following code, an object variable is declared as a Word document, then set to the new document being created from the SAMPLE.DOT template. Now, whenever I use the object variable doc, I know that I’m manipulating the correct document:
Dim doc as Word.Document Set doc = _ Word.Documents.Add(Template:=strTmplPath _ & "\" & conLetterConfirmationTemplate)
It’s also now a simple matter to pass the object variable to another procedure that I’ve created:
or put information into document properties and update the fields to display the information:
doc.CustomDocumentProperties("NWRef") = _ "OrderNr: " & lOrderID doc.Fields.Update
The Range object
With your template designed, your document created, and a commitment to understanding the Word object model, you’re ready to start working with your document. One of the key–and most difficult–concepts in Word VBA is the Range object. In Excel, a range is a group of cells; in Word, it’s any contiguous area of text. A Word range can be a single character, a paragraph, rows (but not columns) in a table, all of the text in the body of the document, or any combination of the above. The trick is in knowing how to specify the range.
Here’s an example from the sample document in this month’s Subscriber Download. In the document, order details are inserted as a tab-delimited string at the [ItemList] bookmark. Just below that list, the total of all of the item lines should be inserted and formatted with the ItemListTotal style. I obtained the position for the total by setting a range variable to the ItemList bookmark and collapsing the range to its end point. I then inserted the order total information into the redefined range. The range variable now contains only the order total information, so it’s a simple matter to format it with the ItemListTotal style.
Dim rngBookmark As Word.Range Set rngBookmark = _ InsertBookmarkInfo(doc, "ItemList", _ strItemList) With rngBookmark .Collapse wdCollapseEnd .Text = vbTab & vbTab & _ "Order Total" & vbTab & _ Format(CStr(dblRunningTotal), "0.00") .Style = "ItemListTotal" End With
The InsertBookmarkInfo function is used to check for the presence of the bookmark in the document, get its range, and insert the order details. It’s important to realize that inserting information into a content-type of bookmark will delete the bookmark. If you wish to keep the bookmark in the document (either to refer to it or retrieve its content), it must be recreated, a simple matter when you use ranges. This code recreates a bookmark after inserting information into it:
Private Function InsertBookmarkInfo( _ ByVal doc As Word.Document, _ ByVal strBkName As String, _ ByVal strInfo As String) As Word.Range Dim rng As Word.Range If doc.Bookmarks.Exists(strBkName) Then 'Get current range Set rng = doc.Bookmarks(strBkName).Range 'Insert the information '(deletes the bookmark) rng.Text = strInfo 'Recreate bookmark at range 'Will include inserted text doc.Bookmarks.Add Name:=strBkName, _ Range:=rng Set InsertBookmarkInfo = rng Else 'Set range to beginning of doc Set rng = doc.Range rng.Collapse wdCollapseStart Set InsertBookmarkInfo = rng Err.Raise ERR_BookmarkMissing End If End Function
You now know the two essential rules of using Word to present your data in written form. One: Have the template do as much as possible and code only what’s necessary. Two: Use the object model and ranges to manipulate a document and its content, rather than emulating mouse and keyboard actions. You also have an introduction to Word’s fundamental object, Range. You’re well on your way to exploiting one of the most powerful components on your computer.