Microsoft Word | Beginning Access 2002 VBA (Programmer to Programmer)

Using automation to drive Microsoft Word is more complex than Outlook, because it has such a large object model. There are literally hundreds of options that you can use, and it's often very confusing for the beginner. This is not really surprising considering the large number of things that you can do in Word - format text, insert tables, etc. In some respects, because Word is such a visual tool, you wonder whether it's really worth delving into the object model?

The answer to that conundrum is obviously going to be yes, otherwise there wouldn't be anything about it in this chapter, but there are two ways to combine the use of Word and Access. The first, using Mail Merge, has Word as the client requesting data from Access. You get the option to put Access fields in document templates, and then merge these into a document. However, this has always been rather restrictive . You can produce form letters , where the data from each row in the data source is inserted somewhere among the letter text. This is pretty much the way all that junk mail you receive works. You can also create a Catalog, where each row of data is shown after the previous one - exactly like a catalog. What's more difficult, however, is the combination of these two methods . Think about what we've just done with Outlook. We had a parent record (the supplier) and some child records (the order items). This isn't so easy to set up using a mail merge.

So a good way to achieve this is using the second way of combining Access and Word - using automation from Access to push the data into Word documents. This involves starting Word, in much the same way as we started Outlook, creating documents, and adding text to them. This is perfect to fill the gap in our re-ordering setup. At the moment orders are only placed for those suppliers who have e-mail addresses, so for those that don't have e-mail we need some form of paper order. What we'll do is create a Word document containing the order details, and print this out. This can then be faxed or sent to the supplier.

Let's give it a go. The following example uses the Word template Order.dot , which is on the accompanying CD. We use a template just to give us the basic layout of the report. This should be copied into the directory where you have placed the other samples. The example also saves documents in this directory as well as printing them out .

If you have installed your samples in another location then you should change C:\BegAccessVBA2002 to the appropriate directory.

Try It Out-Creating a Word Document

Modify the query qryReOrderSuppliers , and add the Address , State , City , PostCode , and Country fields.
Now create a new module.

Add the following global variables :

   Private Const m_strTEMPLATE As String = " Order.dot"     Private m_objWord As Word.Application     Private m_objDoc As Word.Document

Now the main procedure, to create the order letter:

   Public Sub CreateOrderLetter(recSupp As Recordset, recItems As Recordset)     Dim m_strDIR As String     m_strDIR = CurrentProject.Path & "\"     Set m_objWord = New Word.Application     Set m_objDoc = m_objWord.Documents.Add(m_strDIR & g_strTEMPLATE)     InsertTextAtBookMark "ContactName", recSupp("ContactName")     InsertTextAtBookMark "CompanyName", recSupp("CompanyName")     InsertTextAtBookMark "Address", recSupp("Address")     InsertTextAtBookMark "City", recSupp("City")     InsertTextAtBookMark "State", recSupp("State")     InsertTextAtBookMark "PostCode", recSupp("PostCode")     InsertTextAtBookMark "Country", recSupp("Country")     InsertItemsTable recItems     m_objWord.PrintOut Background:=False     m_objDoc.SaveAs FileName:= m_strDIR & recSupp("CompanyName") & _     " - " & FormatDateTime(Date, vbLongDate) & ".DOC"     m_objDoc.Close     m_objWord.Quit     Set m_objDoc = Nothing     Set m_objWord = Nothing     End Sub

Next comes a function to insert some text at a specific point:

   Private Sub InsertTextAtBookMark(strBkmk As String, varText As Variant)     m_objDoc.Bookmarks(strBkmk).Select     m_objWord.Selection.Text = varText & ""     End Sub

And now a procedure to insert a table:

   Private Sub InsertItemsTable(recR As Recordset)     Dim strTable As String     Dim objTable As Word.Table     strTable = "Item" & vbTab & "Quantity" & vbCr     recR.MoveFirst     While Not recR.EOF     strTable = strTable & recR("Name") & vbTab & _     recR("ReOrderPoint") & vbCr     recR.MoveNext     Wend     InsertTextAtBookMark "Items", strTable     Set objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTab)     objTable.AutoFormat Format:=wdTableFormatClassic3, AutoFit:=True, _     ApplyShading:=False     Set objTable = Nothing     End Sub

The code for Word is now finished, so we just need to call the main function when we run our ReOrder procedure. Don't forget to save this module - call it Word Functions .
Open the Email module you created earlier.

In the ReOrder procedure, add the following highlighted line. It should go after the string of items has been created, but before the recordset of items is closed:

 While Not recReOrder.EOF       strItems = strItems & vbCrLf & recReOrder("Name") & _          vbTab & recReOrder("ReOrderPoint")       recReOrder.MoveNext    Wend   CreateOrderLetter recSupps, recReOrder   recReOrder.Close

Save this module and run it. It should take a little longer this time, and you might see a printing window, indicating that the order letter is being printed out.
When the procedure is finished have a look in C:\BegAccessVBA2002 (or wherever you installed your samples) for two new documents, which you can view in Word:

You can see that we now have a pretty good Word document, with a table of the orders.

How It Works

The first thing to do is look at the Word template:

What you'll notice here is the large I symbols - these represent bookmarks.

If you don't see the bookmarks, then from the Tools menu select Options, and on the View tab select Bookmarks in the Show section:

If you select Bookmarks from the Insert menu you'll see a list of them:

Bookmarks in Word documents are just the same as bookmarks you place in books - that shopping receipt or envelope you tuck into a book to remind you where you are. This is the same - they allow you to jump to specified places in the document. Using automation we can jump to these bookmarks and insert text. Notice there's only one for the list of items. Although there are many items, what we'll be doing is inserting a table, so we only need one bookmark.

OK, let's take a look at the code, starting with the global variables. Firstly there are constants, defining the directory and the Word template to be used. Using a constant means that if we change the directory we don't have to crawl through the code looking for where we've used it:

 Private Const g_strTEMPLATE As String = "Order.dot"

Next come two Word object variables. The first is the main Word application, and the second will be the Word document. These correspond to the main Word window, and to an individual document within Word. We've used global variables here because they will be used in several procedures:

 Private m_objWord As Word.Application Private m_objDoc As Word.Document

Now let's look at the main procedure, which takes two arguments, both recordsets. The first is the recordset of the suppliers, and the second the items to be ordered for the supplier:

 Public Sub CreateOrderLetter(recSupp As Recordset, recItems As Recordset)

Once in the procedure, the first thing we do is get the path to the project, then create an instance of Word - this actually starts Word, but hides it from view so you can't see it. After starting Word we use the Documents collection, and Add a new document. The argument we pass into the Add method is the name of the template that we want our new document to be based on.

 Dim m_strDIR As String     m_strDIR = CurrentProject.Path & "\"    Set m_objWord = New Word.Application    Set m_objDoc = m_objWord.Documents.Add(m_strDIR & g_strTEMPLATE)

The Documents collection just contains a list of all documents currently open, just like the Access Forms collection, which contains a list of all open forms.

At this stage we now have a new document based upon our template. That means that it has the default text from the template, but has empty spaces where the bookmarks are, so that's what we do next. Insert some text into the bookmarks. We call another function to do this, and we'll look at that in a moment, but you can see we are inserting the details from the supplier recordset into specific bookmarks.

 InsertTextAtBookMark "ContactName", recSupp("ContactName")    InsertTextAtBookMark "CompanyName", recSupp("CompanyName")    InsertTextAtBookMark "Address", recSupp("Address")    InsertTextAtBookMark "State", recSupp("State")    InsertTextAtBookMark "PostCode", recSupp("PostCode")    InsertTextAtBookMark "Country", recSupp("Country")

Next is another function call, this time to insert the order items as a table. We'll look at that in a moment too:

 InsertItemsTable recItems

At this stage all of the text has been inserted, so we print out a copy of the order, setting the Background argument to False . This ensures that we wait for Word to finish printing before we continue. This is important, as the next thing we do is save the document and quit Word, and you can't quit if the document is still being printed. We've used Background as a named argument as it makes it much clearer what we are doing:

 m_objWord.PrintOut Background:=False

Once the document is printed it needs to be saved. The file name we use is the company name plus the current date. In real life you'd probably have an order number in there somewhere:

 m_objDoc.SaveAs FileName:= m_strDIR & recSupp("CompanyName") & _       " - " & FormatDateTime(Date, vbLongDate) & ".DOC"

Once saved, we can close the document, quit Word, and clear any object references:

 m_objDoc.Close    m_objWord.Quit    Set m_objDoc = Nothing    Set m_objWord = Nothing End Sub

So that's it. We open a new document based upon a template, add some text at specified positions , print the document, and then save it. Let's see how the text is actually inserted.

To use the bookmarks in the document we use the Bookmarks collection. This is just like the Controls collection on a form, and for a document, contains one entry for each bookmark in the document. We use the name of the bookmark to index into the collection, and use the Select method to make the bookmark the current selection. This would be the equivalent of just clicking on the bookmark in the Word document:

 Private Sub InsertTextAtBookMark(strBkmk As String, varText As Variant)    m_objDoc.Bookmarks(strBkmk).Select

Once this is done the Selection object is activated, containing the currently selected items. When typing documents the selection is visible as a highlight, for example when you select a word or sentence . The Selection object has a property called Text , which contains the text in the selection. We then set the text of the selection to the text we want to insert. We've appended an empty string onto the text because the text comes from the database, and might contain a null value. Adding this empty string just prevents any errors about null values:

 m_objWord.Selection.Text = varText & "" End Sub

So that's how to add text at a bookmark. Let's see how to insert a table. This procedure takes a recordset of the order items, which we will use to create a table. The method we are going to use to create the table is similar to the way you might do it in Word, by just typing in the items, separated by tab characters , and then selecting the Convert Text to Table option from the Table menu. We use this method because it's actually simpler than creating a table, and then moving around the cells inserting the data:

 Private Sub InsertItemsTable(recR As Recordset)

We have two variables. The first is a string, into which we will build up the table, separating the table columns by tab characters, and the table rows by carriage return characters. We've actually already done this procedure once, when we added these details to the mail message, but we're doing it again here to emphasize the method used to create tables:

 Dim strTable As String

The second variable is another word object - a Table , which represents, unsurprisingly, a Word table:

 Dim objTable As Word.Table

Now we create the string containing the table details, by looping through the recordset adding the ingredient name and the number of items to reorder:

   strTable = "Item" & vbTab & "Quantity" & vbCr   recR.MoveFirst    While Not recR.EOF       strTable = strTable & recR("Name") & vbTab & _          recR("ReOrderPoint") & vbCr       recR.MoveNext    Wend

Then we insert this string into the position marked by the Items bookmark:

 InsertTextAtBookMark "Items", strTable

At this stage the document looks like this:

The inserted text is highlighted, so we now need to convert it into a table. To do this we use another method of the Selection object, ConvertToTable , using the Separator argument to tell the method what character is to be used as the column separator:

 Set objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTab)

Now the document looks like this:

The text has been converted into a table, but it's not a particularly nice table, so it needs a little formatting. We use the AutoFormat method of the table, supplying three arguments. The first, Format , identifies the auto format style to use, in this case the Classic 3 style. The second argument, AutoFit , is a Boolean, and indicates whether the columns of the table should be automatically sized to fit their contents. The third argument, ApplyShading , is another Boolean, identifying whether or not shading is to be used on the table:

 objTable.AutoFormat Format:=wdTableFormatClassic3, AutoFit:=True, _       ApplyShading:=False

The table is nicely formatted, so we've really finished our work. All that's left to do is tidy up the object reference:

 Set objTable = Nothing End Sub

That's all there is to it. The final piece is to call all of this code from within our e-mail procedure:

 CreateOrderLetter recSupps, recReOrder

Here we simply call the procedure passing in the recordsets of the suppliers and order items.

Word Summary

As mentioned at the beginning of the section, using automation with Word can be complex, but we've only used a few features here, and managed to get quite a degree of flexibility. Using bookmarks as place markers for text insertion allows the Word document to change without affecting your code. As long as the bookmarks are in the document, then your code will still work.

This is actually quite a powerful concept, because you could use it to create a very flexible report writer. Often, one of the complaints of users is that the reports you've written don't quite do what they want. You could easily create a set of queries and some code that allows users to create their reports using Word. Just give them a list of queries and fields in each query, and they can add bookmarks to Word that match those fields. Your report writer code could search through the bookmarks in the document, and if it finds one that matches a field, could insert the text from the field.