Hands-On 10: Office Automation
Database Design 3Hands-on Excercises
Hands-on 8: Automation using Word
To work with Office documents (such as Word or Excel) using VBA code you need to use Automation. This allows you to work with the object models of the other applications.
In this exercise you will explore automation with Word.
Set a reference to Word’s Object Library
The first stage in writing Automation code is to set a reference to the object model of the application you want to work with.
To set the Word object library reference:
Open the VBA Editor and click Tools > References... to display the References dialog.
Make sure a reference is set to the Word 11 object library. You might need to scroll the list to find this. If it is not checked already, click the check box.
Fig.12.1: Setting reference to Word’s Object Library
View the Word object model using the Object Browser
Open the VBA Editor and press F2 to open the Object Browser. Select Word from the libraries list.
The Classes pane lists the objects, collections and enumerations that are available. Selecting an item from this list displays the associated properties, methods and events in the Members pane. When you select one of these, the syntax will be displayed in the status bar at the bottom of the screen.
Create a new standard module
You will be writing several functions to automate Word from Access. It would be useful if these were available to any forms in your application. You will store all of these together in a standard module.
To make the same Word application available to all the functions in the module we will declare it as a Private variable in the General Declarations section at the start of the module. The Word application is an Object variable.
Option Compare Database
Option Explicit
Private objWord As Object
Note: The two Option lines should be inserted when you open a new module. To ensure that the Option Explicit statement (that forces you to declare variables before using them) is always inserted click Tools > Options and check the require variable declarations check box in the Editor tab.
Create a Word instance
You will first write a function that will open word. The application needs to be opened before you can work with it. Any function that needs to use Word can call this function.
Public Function OpenWord()
On Error GoTo Err_OpenWord
Set objWord = GetObject(, "Word.Application")
objWord.Application.Visible = True
Exit_OpenWord:
Exit Function
Err_OpenWord:
If Err = 429 Then 'word is not running
Set objWord = CreateObject("Word.Application")
Resume Next
Else
MsgBox "Error no. " & Err.Number
Resume Exit_OpenWord
End If
End Function
This code initially uses the GetObject function to open Word. GetObject opens an instance of Word if it is already running. It is best to use this first as otherwise you will have two versions of Word open. This is not necessarily a problem with the large amounts of memory available today but it is better practice to use the existing open version if possible.
If GetObject is called when Word is not running it generates error 429. The error handling code traps this error and calls the CreateObject function to open a new version of Word. If there is an error other than this, an error message is displayed to the user and the function exits.
Use F8 to step through the code both with a running version of Word and with Word closed. You will see that there is a different path through the function in each case.
Enter this code into your module and test it (press F5 with the cursor somewhere inside the function to run the code directly from the editor. Word should open with no document displayed.
Create a new blank document in Word
Public Function OpenNewWordDoc()
' Uses OpenWord function to open Word
' Opens new blank document
On Error GoTo Err_OpenNewWordDoc
Dim docWord As Word.Document
OpenWord
Set docWord = objWord.Documents.Add
Exit_OpenNewWordDoc:
Exit Function
Err_OpenNewWordDoc:
MsgBox "Error no. " & Err.Number
Resume Exit_OpenNewWordDoc
End Function
Close the document and Word
Of course, you will actually want to use the document you have opened in some way but before we look at how you might do that we will write the code to close the document and exit Word.
Adding the two lines below to your code will close the document and release the object reference.
docWord.Close
Set docWord = Nothing
To close the Word application you need to use the Quit method:
objWord.Quit
The complete function up to this point with the new code highlighted is shown below:
Public Function OpenNewWordDoc()
' Uses OpenWord function to open Word
' Opens new blank document
On Error GoTo Err_OpenNewWordDoc
Dim docWord As Word.Document
OpenWord
Set docWord = objWord.Documents.Add
docWord.Close
Set docWord = Nothing
objWord.Quit
Set objWord = Nothing
Exit_OpenNewWordDoc:
Exit Function
Err_OpenNewWordDoc:
MsgBox "Error no. " & Err.Number
Resume Exit_OpenNewWordDoc
End Function
Again, use F8 to step through the function to see how it works. You should see that the code starts Word, opens a new blank document and then closes it and exits Word.
Although Word is successfully closed there is one problem with the approach we have taken here. If the user already had a version of Word running our code used that instance rather than opening a new one. If the user had unsaved documents open in Word they have now been closed which might have resulted in lost work. To avoid this happening we need to add code so that the function will only close Word if it also opened it. To do this we will use a flag – a Boolean variable that will be set to true if the function has opened Word and False otherwise.
As we are using the OpenWord function to open Word, we need to set the flag in this function and then test its value in any other functions that are using Word. As the flag needs to be available to all the functions in the module we will declare it as a private variable in the declarations section at the start of the module in the same way as we declared the Word variable earlier.
Option Compare Database
Option Explicit
Private objWord As Object
Private blOpenedWord As Boolean
Then add the following code to the error handling section of the OpenWord function:
Public Function OpenWord()
...
Err_OpenWord:
If Err = 429 Then 'word is not running
Set objWord = CreateObject("Word.Application")
blOpenedWord = True
Resume Next
...
Now when the function opens a new copy of Word, the flag will be set to true. The code that closes Word can now check the value of this flag and only close Word if it was opened by our function. Add this code to the OpenNewWordDoc function to test the value of the flag before closing Word.
Public Function OpenNewWordDoc()
...
OpenWord
Set docWord = objWord.Documents.Add
docWord.Close
Set docWord = Nothing
If blOpenedWord = True Then
objWord.Quit
End If
Set objWord = Nothing
...
Again test the function by stepping through it both with an open and closed version of Word. You should notice that if Word is already open when you run the function the Quit method is not executed.
Inserting text
Now add some code to the OpenNewWordDoc function to insert some text into the document. To do this we will use the TypeText method of the Selection object. The Selection object is the text selected (or the position of the insertion point) in the currently active Word document. Once we have inserted a line of text we will use the TypeParagraph method of the Selection object to insert a new line character.
Insert this code into your OpenNewWordDoc function:
Set docWord = objWord.Documents.Add
' insert text into the Word document
objWord.Selection.TypeText "This document was created automatically by Access"
objWord.Selection.TypeParagraph
objWord.Selection.TypeText "It will now be closed and saved"
objWord.Selection.TypeParagraph
objWord.Activate
This code will insert two lines of text into your Word document. The last statement uses the Activate method to display Word on your screen.
Save and Close the document
Not surprisingly, we will use the Save and Close methods of the document object to do this. The Save method will open the Save As box if the document hasn’t been saved before so that the user can select a folder and enter a file name for the document. insert the following lines into your function:
docWord.Save
docWord.Close
Set docWord = Nothing
The final line of this code releases the memory used by the docWord variable.
Test the function. It should open a new Word document, insert the specified text, and then open the Save As dialog to allow you to save the file. Provided you do save the file everything is ok. However, if you click the Cancel button you generate error 4198 – Command Cancelled. You need to add appropriate error handling code to the function to deal with this. It is sufficient to add general purpose error handling unless you want to deal with error 4198 specifically.
The completed code for this function is given below:
Public Function OpenNewWordDoc()
' Uses OpenWord function to open Word
' Opens new blank document
On Error GoTo Err_OpenNewWordDoc
Dim docWord As Word.Document
OpenWord
Set docWord = objWord.Documents.Add
' objWord.Activate
' insert text into the Word document
objWord.Selection.TypeText "This document was created automatically by Access"
objWord.Selection.TypeParagraph
objWord.Selection.TypeText "It will now be closed and saved"
objWord.Selection.TypeParagraph
objWord.Activate
docWord.Save
docWord.Close
Set docWord = Nothing
If blOpenedWord = True Then
objWord.Quit
End If
Set objWord = Nothing
Exit_OpenNewWordDoc:
Exit Function
Err_OpenNewWordDoc:
MsgBox "Error no. " & Err.Number
Resume Exit_OpenNewWordDoc
End Function
Open an existing document
To open an exisitng document you use the Open method of the Documents object. The first argument to the method is the name of the file you wish to open. You need to use the full pathname for the file. The following code will open a file named empMemo.doc, stored in the D3 folder of the I: drive.
appWord.Documents.Open("I:\D3\empMemo.doc")
Note:
You can use the Path property of the current project to get the path for the folder where your database is located. Test this out by typing print CurrentProject.Path into the Immediate Window. Press Enter and you will see the drive and folder for your current database displayed. This method is used in the following code to open a file named empMemo.doc stored in the same folder as the database.
Add this function to your modWordUtilities module.
You might want to consider passing the file name as an argument to the function to make it more general purpose.
Public Function OpenWordDoc()
On Error GoTo Err_OpenWordDoc
Dim fname As String
Dim docWord As Word.Document
OpenWord
' file name needs to include path
' if file to open is in same folder as database can use Path attribute of current project
fname = CurrentProject.Path & "\empMemo.doc"
Set docWord = objWord.Documents.Open(fname)
Exit_OpenWordDoc:
Exit Function
Err_OpenWordDoc:
MsgBox "Error no. " & Err.Number
Resume Exit_OpenWordDoc
End Function
Create a Word Memo for all Yum employees
You will now use Word Automation to create a memo document to be sent to all Yum employees. Before you can do that you will need to create a query to list the employee names.
Create a query to list the employee names
To provide the list of employee names to be used by Word to create the memo we will use a query that will combine the employee first name and surname in a single field. Create this query as shown below and save it with a meaningful name.
Fig. 12.2: Employee name query to be used as data source for memo
The Word document
The Word document you will use for this exercise has already been created for you. Download it from WebCT and save it in the same folder as your database (this will simplify the code to open the file as you can then use the CurrentProject.Path as discussed above).
We are going to write text to specific locations in this document, identified by bookmarks. Two bookmarks have been set – one for the position of the date and the other for the position of the employee names. You can see the Bookmarks that are set in a Word document by clicking Bookmark from the Insert Menu.
If you click the Go To button the insertion point will be moved to the specified bookmark.
Insert the date and list of names
The following code will insert today’s date and the list of employee names at the appropriate points in the memo document. You need to make sure that you have created the employee names query and saved the memo document to the same folder as your database before running the code.
Public Function CreateWordMemo()
' Open memo in Word and insert text
On Error GoTo CreateWordMemo_Error
Dim dbs As Database
Dim rstEmployees As Recordset
Dim appWord As Word.Application
Dim docWord As Word.Document
Dim fname As String
Dim strDate As String
strDate = Date 'assign date to string variable so we can print it
OpenWord ' use function to open Word
'Open recordset based on employee name query
Set dbs = CurrentDb()
Set rstEmployees = dbs.OpenRecordset("qryEmpFullName")
' open the memo document
' assume memo document in same folder as database
fname = CurrentProject.Path & "\empMemo.doc"
Set docWord = objWord.Documents.Open(fname)
objWord.Visible = True
With objWord
.Selection.Goto wdGoToBookmark, Name:="DateToday" 'move to date bookmark
.Selection.TypeText " " & strDate 'insert date
.Selection.Goto wdGoToBookmark, Name:="MemoToLine" 'move to memo line bookmark
End With
'Loop through recordset returned by query, inserting name of each employee
Do Until rstEmployees.EOF
objWord.Selection.TypeText rstEmployees!Employee & ", "
rstEmployees.MoveNext
Loop
'reset variables to nothing and free up memory for other processes
Set rstEmployees = Nothing
Set dbs = Nothing
Set docWord = Nothing
Set objWord = Nothing
Exit_CreateWordMemo:
Exit Function
CreateWordMemo_Error: 'error trapping routine
MsgBox Err.Description
Resume Exit_CreateWordMemo
End Function
The comments embedded in the code should help you to understand how it works. You can test it from the VBA editor by pressing F8 with the insertion point somewhere inside the function.
Notice that although the code frees the variables, it does not close Word. This means that the document is open in Word for the user to save or edit as they choose. If you want to save the document it is best to use Save As or you will write duplicate text to the same document the next time you run the function.
Once you are happy that the function works as it should, create a utilities form and add a button to allow the user to create an employee memo.
Enhancements
At the moment only the date and list of employees are entered automatically by Access. The user then needs to enter the memo text directly into Word. One simple improvement that could be made is to add another Bookmark to the Word document at the position where the memo text needs to be entered. Then add a line of code to your function so that the document is ready for the user to enter text.
You could also consider passing the recordset as a parameter so that the user could select who the memo would be sent to. To do this you would pass a string containing the name of the query to be used.
A final modification would be to add a text box to your form to allow the user to enter the memo content. This would then be passed as a string to the function and printed to the appropriate bookmark by your code.
d3_ho8.docPage 1 of 9