Word Automation, Part II

Inserting Text into Word Bookmarks

 

By Bryan Carbonnelle

Demo File

 

In the last article we reviewed a number of automation tasks for working with Word from inside Access. Specifically, we discussed code for the following subjects:

 

·         Using an open Word instance, or creating one if there isn’t one already open.

·         Creating a new blank document.

·         Inserting text into the document at the insertion point.

·         Inserting paragraphs.

·         Saving the new document.

·         Closing and releasing the pointer to the document.

·         Closing Word, as needed, and releasing the pointer to the Word Application object.

 

In this article, we’ll learn how to insert text into a previously-created document at specially-marked positions called bookmarks (in Word). Let’s say you need to create an order confirmation letter. You could create a report in Access and print it, but what if your client states that all correspondence should be in electronic form? What can you do? You have a few options, which we’ve deliberated in Table A.

 

Table A

File Format

Pros

Cons

Access Snapshot (.snp)

1.    Can create from already existing report.

2. Accurate representation of printed report.

1.  Client needs special software that is not commonly installed, to view it.

2.  Not everyone wants to or is allowed to install software on their desktop PC.

Adobe Acrobat (.pdf)

1.    Common file format.

2.    Almost everyone has the Acrobat reader installed.

3.    You can secure the PDF to prevent modifying and copying text, printing, etc.

 

1.  Software to create PDFs can get expensive if there are multiple workstations that require the ability.

2.  Freeware/low cost alternatives are available, but not all may be able to produce the results required. For example, security may not be as thorough or font representation may not be as accurate.

Print and Scan

1.    You have a paper copy for your records.

1)  Waste of resources (paper/toner).

2)  Waste of time (staff time to scan and make sure the scan is legible).

3)  For high quality images, the file sizes are quite large.

Automate Word (.doc)

1.    Part of every Microsoft Office package.

2.    Commonly installed software.

3.    Integrates with Access quite well.

4.    No need to print and scan.

5.    Creating the document and emailing can be reduced to a single button click.

6.    Document sizes are reasonable unless a lot of images are included in the document.

1.  Learning the Word object model is difficult.

2.  Possibly more than one version of Word installed among your user base.

3.  Security in Word, to prevent changes to report is easily cracked.

 

It appears that the pros of using Word as a means to distributing electronic copies far outweigh the cons. However, if you’re concerned with the security of your e‑report, Word may not be suitable for you.

Procedure

Now that we have decided to use Word to allow us to distribute our confirmation letter electronically, where do we start? First you need to create a template for Access to use. Now this template won’t be a real Word template, although it could be. We will be using a regular Word document as our template. In the accompanying demo file, there is a sample confirmation letter (conf_let.doc) that will be the basis of the procedure described in this section.

 

When you open the document, you may see that it looks like a poorly written confirmation letter; that’s because it is missing a bunch of data that will come from your Access database. How do we know where to add the data? First, make sure you have the letter opened in Word, then select Tools | Options… | View Tab. Check the Bookmarks option in the Show section, and click OK to return to the letter. You should see a lot of I-beam characters. These are bookmarks. We will be using these bookmarks to tell our automation code where to put text.

 

To insert a bookmark into Word, place your insertion point where you want the bookmark to appear. Select Insert | Bookmark…, type in the name you want to call the bookmark (spaces are not allowed in the bookmark name), and then click add. You just added one bookmark. What if you put the bookmark in the wrong spot? To delete the bookmark just select Insert | Bookmark…again, select the bookmark you want to delete and then press the Delete key. You just deleted one bookmark.

 

Now that we have had a whirlwind introduction to bookmarks, let’s get onto writing some code. Where do we start? Well, first you need a new module and a reference to your version of Word’s Object Library.

 

Next, you need to create a reference to the Word object and make it visible, which you learned to do in Part 1. If you don’t want to type the code yourself, you can follow along in the sFillWordDoc procedure in the basWordBookmarks module in the stripped down version of the Northwind sample database that is included in the demo file.

 

Please note that there is no error handling in the code in this article. Normally you should have error handling, but to keep things clearer, and to keep the focus on Word automation, I have left it out.

 

Now you need to get the path to the Northwind.mdb sample database since the sample document we are using is located in the same directory. The following code creates the full path name:

 

‘Get Path of Current DB

strPath = CurrentDb().Name

'Strip FileName to Get Path to Doc

Do

    lngInStr = InStr(lngInStr + 1, strPath, "\")

Loop While (InStr(lngInStr + 1, strPath, "\") <> 0)

‘Get path up to the last ‘\’

strPath = Left(strPath, lngInStr)

'Append document name onto the end of the stripped path

strPath = strPath & "conf_let.doc"

 

Now that you have the full path to the Word document, the following statement opens it:

 

Set doc = objWord.Documents.Open(strPath)

 

Not only will this statement open the document, it will also create a pointer to it that is stored in the doc object.

 

The next step is to retrieve the data you want to insert into the confirmation letter. To do so, we’ll use a SQL statement and a Recordset object. You don’t have to use a SQL string as the basis for the query; you can use a saved query, a table, or any other recordset you wish to use.

 

One final check, before we do the transfer, is to make sure the recordset contains at least one record. Where there’s at least one record, the Else action in the following If..Then…Else statement inserts the data into Word:

 

'Check to see if there are any order details to transfer

If rstOrder.EOF And rstOrder.BOF Then

    MsgBox "No order details to transfer.", , "No Records"

Else

    doc.Bookmarks(“OrderDate”).Select

    objWord.Selection.TypeText _

    Format(rstOrder.Fields("OrderDate"), "mmmm d, yyyy")

End If

 

First, the Select method selects the appropriate bookmark. Then, the TypeText method enters the data:

 

·         objWord is the Word object reference.

·         Selection is the object that points to the currently selected item in Word. In our case, it happens to be the bookmark, OrderDate, which we selected in the previous line .

·         TypeText is a method of the Selection object to tell the code to type the text in the first parameter. One thing to watch for is that if you have “Typing Replaces Selection” in Tools | Options…| Edit set, the text will replace the selection. It does not replace bookmarks; the bookmark will remain.

·         Format(rstOrder.Fields("OrderDate"), "mmmm d, yyyy") tells our Automation code what text to insert. In this case it is a formatted version of the value from the OrderDate field.

 

If you want to try writing the rest of the code, here are the bookmark names and their corresponding fields:

 

BookMark Name  

Field

CompanyName 

Customers.CompanyName

Address  

Address

City  

City

Prov 

Region

Country

Country

PostCode

PostalCode

ContactName

ContactName

CourierName

Shippers.CompanyName

ShipDate

ShippedDate

ReqDate

RequiredDate

EmpName

FirstName and LastName

EmpPosition

Title

 

 

To verify your code, you can look at the sFillWordDoc module in the basWordBookmarks module in the Northwind database (the demo file).

Handling Null values

What happens when you run into a field with a Null value? The same thing that usually happens with Null fields—the application returns an Invalid Use of Null Error. There are a few ways to handle it Nulls:

 

·         Don’t allow Null values in your data.

·         Use an error handler to catch Null values.

·         Append an empty string to the end.

 

I typically use the first approach for new databases. For inherited data, I typically use the last method and just append “” to the end of any string that may contain Null. I only bring it up, because in the sample database, the Region Field, may contain Null values.

Unique Data

There are two fields we have not dealt with yet. The Quantity and ProductName fields. I need to back up just a bit here and explain the recordset we returned. In the form we selected an order for which to generate the confirmation letter. This can return 0 to n records, all depending on the number of items that are on the order. Of the 15 fields we return, only two of them are unique to each record. The Quantity and ProductName. All the other data is identical from one record to another. There may be better ways of returning a recordset to use, but since this article is about Word automation and not SQL, it works for our purposes. Now let’s move forward with this new information.

 

The Quantity and ProductName fields make up the individual Order Details. So not surprisingly, there is a bookmark called OrderDetails. This is where we want to put this information. The first thing to do is select the bookmark:

 

doc.BookMarks(“OrderDetails”).Select

 

Then, insert the Quantity. To set off this field from the rest of the document, indent it using the vbTab intrinsic constant as follows:

 

objWord.Selection.TypeText vbTab

objWord.Selection.TypeText rstOrders.Fields(“Quantity”)

 

Now that you have the quantity, insert the ProductName field in a columnar list using the following code:

 

objWord.Selection.TypeText vbTab

objWord.Selection.TypeText rstOrders.Fields(“ProductName”)

 

Last we need to end the paragraph with a hard return. Word gives us the ability to type a hard return with the TypeParagraph method of the Selection object as follows:

objWord.Selection.TypeParagraph

 

This gives us only the first record, so we need to wrap these lines in a Do … While Not(EOF) Loop. We don’t need to put the bookmark selection in the loop because the selection will be at the end of the last inserted paragraph. If we put the bookmark selection inside the loop, we would end up inserting the order details backwards (the last order detail would be first, and the first order detail would be last). The full loop follows:

 

doc.Bookmarks("OrderDetails").Select

Do

    With objWord.Selection

        .TypeText vbTab

        .TypeText rstOrder.Fields("Quantity")

        .TypeText vbTab

        .TypeText rstOrder.Fields("ProductName")

        .TypeParagraph

    End With

    rstOrder.MoveNext

Loop While Not (rstOrder.EOF)

 

This loop will cycle through the recordset and place the Quantity and ProductName fields in the Word document. You could also build a string and just use TypeText once to put the entire string into the document all at once.

 

The last thing you need to do is close and release all the pointers to the various objects used in the procedure:

 

rstOrder.Close

Set rstOrder = Nothing

db.Close

Set db = Nothing

'Close and release Word pointers

Set doc = Nothing

Set objWord = Nothing

 

The only thing you won’t close is the Word object so the user can save, print, fax, and e-mail the newly-created document. However, if you don’t close the newly-created letter or save it with a different file name, the next time you run the code you will insert new data into the currently open document (that already has data in it), and not a new document.

 

That is how you insert Access data into defined bookmarks in a Word document. Overall it is not really difficult once you grasp the Word Object Model and how to interact with it.

 

If you want to have a look at Word’s Object Model, you can look at these pages on Microsoft’s MSDN site.

Word 2000 - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deovrMicrosoftWord2000.asp

Word XP - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deovrMicrosoftWord2000.asp?frame=true

 

If you have any questions, please feel free to e-mail me at carbonnb@sympatico.ca and I will do my best to give you a coherent answer. J

 

Please contact the author at carbonnb@sympatico.ca  to ask questions or report errors.

Bryan Carbonnell ©2002
May be distributed as long as the copyright remains.
Bryan Carbonnell Bio