9.1. Automating Microsoft Word

Next to automating Excel, automating Word is one of the most useful things you can learn to do with VBA. There are many times when you need to print information that may not lend itself to an Access report. Even if it can be done with an Access report, you might want to give the end user complete control over the look of the document so that he can make changes without having to call you, the developer. There are a lot of ways to use automation to create documents users can later modify, but for these examples I focus on using Form Fields.

There are several form controls available in Microsoft Word. Access them by going to View Toolbars Forms from the top menu in Word. You end up with the toolbar that you see in Figure 9-1. To insert a Form Field, click on the button on the far left side of the toolbar. You get a gray box at the current cursor location. After you have the gray box, click the fourth button from the left of the toolbar to get to the field properties, where you have several options. Determine the type of entry being done (Text, Number, Date, etc.), or set it up to run a macro when you enter or exit the field. Set the field name by changing the bookmark description. You can also choose to have it as a calculated field.

Figure 9-1. The forms toolbar in Microsoft Word, which can be used to insert several form controls

If you want to perform calculations, there is something to keep in mind that for me was not intuitive. Assume that you have three form fields on a Word document called Price, Quantity, and Total. You set up Price and Quantity to be number fields, and Total to be a calculated number field (by selecting Calculation from the Type drop down menu). There is a checkbox at the bottom of the Form Fields Properties dialog that says "Calculate on Exit." I am always tempted to check this box on the calculated field, but in reality, you need to have the "Calculate on Exit" boxes checked on the fields used to calculate the total. The reason for this is that you will never be in the calculated field to have anything run on exit. Having said this, I rarely perform calculations in Word, but you can do it, and it might make sense for things such as a performance appraisal or a survey where you need to get a score at the end.

Here is a quick example that we will also use to automate from Access. Go into a blank Word document and set it up so that you have Form Fields for Name, Address, City, State, Zip, Phone, Days_Past_Due, and Amount_Past_Due (set the bookmark to these names). While in the properties dialog for each form field, set the number of characters, the data type, the number format, etc. Below the form fields, type in Letter #1 and save the file as Letter1.doc; then update it to Letter #2 and save it as Letter2.doc. Now, close Microsoft Word and go into Access.

You will create two tables in Access; the first is called tbl_CustomerData. In this table, create fields for CustomerDataID (Autonumber), Name, Address, City, State, Zip, Phone, Sales, Days_Past_Due, and Past_Due_Amount. Next, create a second table called tbl_MailInformation. In this table, create fields for MailID (Autonumber), CustomerDataID (Long), Date_Mailed, and Letter_Mailed. Now, we are ready to automate Word. I suggest always naming the bookmarks in Word with the exact same names as your Access field names because it becomes much easier to perform the automation. You can use the procedure in Example 9-1 with slight modifications for many purposes. Keep in mind that you need to set a reference to the Microsoft Word Object Library by going to Tools References while in design view of the module. Also, there are several file locations noted in the example that will be different on your computer.

Example 9-1. Word automation example

 Public Sub SendLetters(  ) Dim wapp As Word.Application Dim wdoc As Word.Document Dim fld As Word.FormField Dim db As DAO.Database Dim rs As DAO.Recordset Dim rs2 As DAO.Recordset Dim filenm As String Set db = CurrentDb Set rs = db.OpenRecordset("tbl_CustomerData") Set rs2 = db.OpenRecordset("tbl_MailInformation") Set wapp = New Word.Application wapp.Visible = True rs.MoveFirst While Not rs.EOF   If rs.Fields("Days_Past_Due").Value <= 30 Then     filenm = "C:\BookInformation\Chapter9\Letter1.doc"   End If   If rs.Fields("Days_Past_Due").Value > 30 Then     filenm = "C:\BookInformation\Chapter9\Letter2.doc"   End If   Set wdoc = wapp.Documents.Open(filenm)   For Each fld In wdoc.FormFields     fld.Result = rs.Fields(fld.Name).Value   Next fld   wdoc.SaveAs "C:\BookInformation\Chapter9\" & _        rs.Fields("Name").Value & ".doc"   wdoc.Close   rs2.AddNew   rs2.Fields("CustomerDataID").Value = rs.Fields("CustomerDataID").Value   rs2.Fields("DateMailed").Value = Now(  )   rs2.Fields("LetterMailed").Value = filenm   rs2.Update   Set wdoc = Nothing   rs.MoveNext Wend Set fld = Nothing wapp.Quit Set wapp = Nothing rs2.Close rs.Close Set rs2 = Nothing Set rs = Nothing Set db = Nothing End Sub

The most critical thing to notice about using form fields is that the value of the form field is actually stored in a property called Result. What makes this code very easy to use is that the form fields are given the same names as the field names in the Access table. Since the table can have more fields than you might want in a document, the code cycles through the form fields in the Word document using a For Each...Next loop.

The second table stores the letter sent to each customer and the day it was sent. From a business perspective, this information can be very useful to know. This code saves the document as the customer name. If you have a large customer set, this probably isn't practical; save it as the CustomerDataID and then the customer name to avoid duplicate values. If you want to print the document, use a single line of code:

     wdoc.PrintOut

While this example can be very useful, there are some limitations. For example, you can't have the customer's name in the header or footer because it is not possible to put a form field in the header or footer. Let's look at an example where you want to have the customer's name in the header and embed an Excel worksheet in the document.

Create a new Word document and go into the header by going to View Header and Footer from the menu in Word. Next, go to Insert Bookmark, call the bookmark CustomerName, and press the Add button. Get out of the Header/Footer by going to View Header and Footer or by using the Close button on the Header and Footer toolbar. Type "Here is the Excel Document in the body of the Word document, and press Enter. Next, go to Insert Bookmark, call the bookmark ExcelDoc, and press the Add button. Save the document as WordExcelTest.doc.

Go into Excel and create an Excel workbook that looks like the Excel document shown in Figure 9-2. Select the cells that you want to show in Word, and go to File Print Area Set Print Area. This determines which cells are used when the Excel worksheet is embedded in the Word document. Save this workbook as WordExcelTest.xls. Next, find a way to access the bookmarks in Word and put in text or an Excel document. Look at Example 9-2. You need to reference both

Figure 9-2. The Excel workbook used in Example 9-2

Word and Excel. This example uses the same table and Access database used in Example 9-1. Also, note that the filenames and paths used in this example will be different on your system.

Example 9-2. Example of Access automating Excel and Word

 Public Sub WordExcelAutomation(  ) Dim xlapp As Excel.Application Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Dim wapp As Word.Application Dim wdoc As Word.Document Dim db As DAO.Database Dim rs As DAO.Recordset Set xlapp = New Excel.Application xlapp.Visible = True Set wapp = New Word.Application wapp.Visible = True Set db = CurrentDb Set rs = db.OpenRecordset("tbl_CustomerData") rs.MoveFirst While Not rs.EOF  Set xlwb = xlapp.Workbooks.Open _        ("C:\BookInformation\Chapter9\WordExcelTest.xls")  Set xlws = xlwb.ActiveSheet  xlws.Range("B1").Value = rs.Fields("Amount_Past_Due").Value  xlws.Range("B2").Value = rs.Fields("Days_Past_Due").Value  xlws.Range("C4").Value = Now(  )  xlws.Columns.AutoFit  xlwb.SaveAs "C:\BookInformation\Chapter9\temp.xls"  xlwb.Close  Set xlws = Nothing  Set xlwb = Nothing  Set wdoc = wapp.Documents.Open _    ("C:\BookInformation\Chapter9\WordExcelTest.Doc")  wdoc.Bookmarks.Item("CustomerName").Select  wapp.Selection.TypeText Text:=rs.Fields("Name").Value  wapp.Selection.TypeParagraph  wdoc.Bookmarks.Item("ExcelDoc").Select  wapp.Selection.InlineShapes.AddOLEObject _         ClassType:="Excel.Sheet.8", FileName:= _         "C:\BookInformation\Chapter9\temp.xls", LinkToFile:=False, _         DisplayAsIcon:=False  wdoc.SaveAs "C:\BookInformation\Chapter9\" & rs.Fields("Name").Value & ".doc"  Kill "C:\BookInformation\Chapter9\temp.xls"  wdoc.Close  Set wdoc = Nothing  rs.MoveNext Wend xlapp.Quit wapp.Quit Set xlapp = Nothing Set wapp = Nothing rs.Close Set rs = Nothing Set db = Nothing End Sub

There are several important items in this code. Because the CustomerName bookmark is in the header, you cannot use the GoTo method to access it. Instead, use the Select method of the Bookmarks collection. You could use the GoTo method for the bookmark in the body of the document, but I use the same method for both. To enter text at a bookmark location, use the TypeText method of the Word Selection class.

In this code, a temporary Excel workbook is created for each customer. For each customer record, the Excel document you created is opened, the current customer's information is filled in, and the workbook is saved as temp.xls. Then the Word document is opened, the CustomerName bookmark is selected, and the customer's name is typed beginning at the bookmark location. Next, the ExcelDoc bookmark is selected, and the temporary Excel file is embedded in the document. Because the Excel file is deleted after it is embedded, it is important that the LinktoFile property is set to False. Once the document is done, it is saved using the customer's name.

These Word examples should show you the possibilities available with Word automation. If you get stuck while using Word automation, record a macro and look at the code produced by Word automatically. The ability to automate two applications from Access and then integrate the documents is very useful for a number of business needs. For example, you want to embed a worksheet that shows sales results for each employee into a performance appraisal pre-filled in with each employee's information. Doing that manually would be very tedious, particularly if there are a large number of employees. This type of automation not only reduces the amount of time spent, but it also avoids errors.