<< . .

. 32
( : 51)

. . >>

turning on the Bookmarks option by checking the option (third choice in the first column).
The names won™t be visible”only the bookmark holders (locations) will be visible, as shown
in Figure 15-7. The names and arrows in Figure 15-7 were placed using text boxes to show
where the bookmark names are assigned.

Figure 15-5: Customer data used in the following Automation example is entered on the
Customers form.
Chapter 15 ¦ Exchanging Access Data with Office Applications 363

Figure 15-6: Each customer can have an unlimited number of orders. Thank-you letters
are printed from the Orders form.

Figure 15-7: The Thanks.dot template contains bookmarks where the merged data is to
be inserted.

Figure 15-8: After a successful merge, all the bookmarks have been replaced with their
respective data.
Part II ¦ Collaborating and Integrating with Office 2003

If you click the Print Thank You Letter button in Access while Word is open with an existing
document”which lacks the bookmark names specified in the code”the fields will simply be
added to the text inside Word at the point where the cursor is currently sitting.

When the user clicks the Print Thank You Letter button on the Orders form, Word generates a
thank-you letter with all the pertinent information. The following code shows the
MergetoWord function in its entirety so you can see in-depth how it works.
Public Function MergetoWord()
˜ This method creates a new document in MS Word
˜ using Automation.
On Error Resume Next
Dim rsCust As Recordset, iTemp As Integer
Dim WordObj As Word.Application
Set rsCust = DBEngine(0).Databases(0).OpenRecordset(“Customers”, _
rsCust.Index = “PrimaryKey”
rsCust.Seek “=”, Forms!Orders![CustomerNumber]
If rsCust.NoMatch Then
MsgBox “Invalid customer”, vbOKOnly
Exit Function
End If
DoCmd.Hourglass True
Set WordObj = GetObject(, “Word.Application”)
If Err.Number <> 0 Then
Set WordObj = CreateObject(“Word.Application”)
End If
WordObj.Visible = True

˜ Specify the correct drive and path to the
˜ file named thanks.dot in the line below.

Template:=”G:\Access 11 Book\thanks.dot”,

˜ The above path and drive must be fixed

WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”FullName”
WordObj.Selection.TypeText rsCust![ContactName]
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”CompanyName”
WordObj.Selection.TypeText rsCust![CompanyName]
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”Address1"
WordObj.Selection.TypeText rsCust![Address1]
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”Address2"
Chapter 15 ¦ Exchanging Access Data with Office Applications 365

If IsNull(rsCust![Address2]) Then
WordObj.Selection.TypeText “”
WordObj.Selection.TypeText rsCust![Address2]
End If
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”City”
WordObj.Selection.TypeText rsCust![City]
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”State”
WordObj.Selection.TypeText rsCust![State]
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”Zipcode”
WordObj.Selection.TypeText rsCust![Zipcode]
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”PhoneNumber”
WordObj.Selection.TypeText rsCust![PhoneNumber]
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”NumOrdered”
WordObj.Selection.TypeText Forms!Orders![Quantity]
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”ProductOrdered”
If Forms!Orders![Quantity] > 1 Then
WordObj.Selection.TypeText Forms!Orders![Item] & “s”
WordObj.Selection.TypeText Forms!Orders![Item]
End If
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”FName”
iTemp = InStr(rsCust![ContactName], “ “)
If iTemp > 0 Then
WordObj.Selection.TypeText Left$(rsCust![ContactName],
iTemp _ - 1)
End If
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”LetterName”
WordObj.Selection.TypeText rsCust![ContactName]
WordObj.Selection.MoveUp wdLine, 6
˜ Set the Word Object to nothing to free resources
Set WordObj = Nothing
DoCmd.Hourglass False
Exit Function
Set WordObj = Nothing
Exit Function
End Function

Creating an instance of a Word object
The first step in using Automation is to create an instance of an object. The sample creates an
object instance with the following code:
Part II ¦ Collaborating and Integrating with Office 2003

On Error Resume Next
Set WordObj = GetObject(, “Word.Application”)
If Err.Number <> 0 Then
Set WordObj = CreateObject(“Word.Application”)
End If

Obviously, you don™t want a new instance of Word created every time a thank-you letter is
generated, so some special coding is required. This code snippet first attempts to create an
instance by using an active instance (a running copy) of Word. If Word is not a running
application, an error is generated. Because this function has On Error Resume Next for
error trapping, the code doesn™t fail, but instead proceeds to the next statement. If an error is
detected (the Err.Number is not equal to 0), an instance is created by using

Making the instance of Word visible
When you first create a new instance of Word, it runs invisibly. This approach enables your
application to exploit features of Word without the user even realizing that Word is running.
In this case, however, it is desirable to let the user edit the merged letter, so Word needs to be
made visible by setting the object™s Visible property to True by using this line of code:
WordObj.Visible = True

If you don™t set the object instance™s Visible property to True, you may create hidden cop-
ies of Word that use system resources and never shut down. A hidden copy of Word doesn™t
show up in the Task tray or in the Task Switcher.

Creating a new document based on an existing template
After Word is running, a blank document needs to be created. The following code creates a
new document by using the Thanks.dot template:
WordObj.Documents.Add Template:=”G:\Access 11 Book\thanks.dot”, _

The path must be corrected in order to point to the Thanks.dot template on your computer.

The Thanks.dot template contains bookmarks (as shown in Figure 15-7) that tell this function
where to insert data. You create bookmarks in Word by highlighting the text that you want to
make a bookmark, selecting Insert_Bookmark, and then entering the bookmark name and
clicking Add.
Chapter 15 ¦ Exchanging Access Data with Office Applications 367

Using Bookmarks to insert data
Using Automation, you can locate bookmarks in a Word document and replace them with the
text of your choosing. To locate a bookmark, use the Goto method of the Selection
object. After you have located the bookmark, the text comprising the bookmark is selected.
By inserting text (which you can do by using Automation or simply by typing directly into
the document), you replace the bookmark text. To insert text, use the TypeText method of
the Selection object, as shown here:
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”FullName”
WordObj.Selection.TypeText rsCust![ContactName]

You can™t pass a null to the TypeText method. If the value may possibly be Null, you need
to check ahead and make allowances. The preceding sample code checks the Address2 field
for a Null value and acts accordingly. If you don™t pass text to replace the bookmark”even
just a zero length string (“ ”)”the bookmark text remains in the document.

Activating the instance of Word
To enable the user to enter data in the new document, you must make Word the active
application. If you don™t make Word the active application, the user has to switch to Word
from Access. You make Word the active application by using the Activate method of the
Word object, as follows:

Depending on the processing that is occurring at the time, Access may take the focus back from
Word. You can help to eliminate this annoyance by preceding the Activate method with a
DoEvents statement. Note, however, that this doesn™t always work.

Moving the cursor in Word
You can move the cursor in Word by using the MoveUp method of the Selection object.
The following example moves the cursor up six lines in the document. The cursor is at the
location of the last bookmark when this code is executed:
WordObj.Selection.MoveUp wdLine, 6

Closing the instance of the Word object
To free up resources that are taken by an instance of an Automation object, you should always
close the instance. In this example, the following code is used to close the object instance:
Set WordObj = Nothing
Part II ¦ Collaborating and Integrating with Office 2003

This code closes the object instance, but not the instance of Word as a running application. In
this example, the user needs access to the new document, so closing Word would defeat the
purpose of this function. You can, however, automatically print the document and then close
Word. If you do this, you may even choose to not make Word visible during this process. To
close Word, use the Quit method of the Application object, as follows:

Inserting pictures by using Bookmarks
It is possible to perform other unique operations by using Bookmarks. Basically, anything
that you can do within Word, you can do by using Automation. The following code locates a
bookmark that marks where a picture is to be placed and then inserts a .BMP file from disk.
You can use the following code to insert scanned signatures into letters:
WordObj.Selection.Goto what:=wdGoToBookmark, Name:=”Picture”
WordObj.ChangeFileOpenDirectory “D:\GRAPHICS\”
WordObj. ActiveDocument.Shapes.AddPicture Anchor:=Selection.Range,
_ FileName:= _
SaveWithDocument _

Using Office™s Macro Recorder
Using Automation is not a difficult process when you understand the fundamentals. Often,
the toughest part of using Automation is knowing the proper objects, properties, and methods
to use. Although the development help system of the Automation Server is a requirement for
fully understanding the language, the easiest way to quickly create Automation for Office
applications like Word is the Macro Recorder.
Most versions of Office applications have a Macro Recorder located on the Tools menu (see
Figure 15-9). When activated, the Macro Recorder records all events, such as menu selections
and button clicks, and creates Visual Basic code from them.
Chapter 15 ¦ Exchanging Access Data with Office Applications 369

Figure 15-9: The Macro Recorder in Word is a powerful tool to help you create Automa-
tion code.

After selecting Tools_Macro_Record New Macro, you must give your new macro a name
(see Figure 15-10). In addition to a name, you can assign the macro to a toolbar or keyboard
combination and select the template in which to store the macro. If you are creating the macro
simply to create the Visual Basic code, the only thing that you need to be concerned with is
the macro name.

Figure 15-10: Enter a macro name and click OK to begin recording the macro. In this
example, the macro is named “MyMacro.”

After you enter a macro name and click OK, the Macro Recorder begins recording events
and displays a Stop Recording window, and the arrow changes to an open pointer attached to
a cassette, as shown in Figure 15-11. You can stop recording events by clicking the Stop
button (the button with a square on it). To pause recording events, click the other button,
which is the Pause button.
Part II ¦ Collaborating and Integrating with Office 2003

Figure 15-11: The Macro Recorder records all events until you click the Stop button.

After you have finished recording a macro, you can view the Visual Basic code created from
your events. To view the code of a macro, select Tools_Macro_Macros to display a list of
all saved macros. Then select the macro that you recorded and click the Edit button to display
the Visual Basic editor with the macro™s code. Figure 15-12 shows the Visual Basic editor
with a macro that recorded the creation of a new document using the Normal template and the
insertion of a picture using the Insert_Picture_From File menu item.
In the application for which a macro is created, the Application object is used explicitly.
When you use the code for Automation, you must create an Application object accordingly.
For example, the preceding macro uses the following code to create a new document:
Documents.Add Template:=” Normal.dot”, NewTemplate:= False,

This code implicitly uses the Application object. To use this code for Automation, copy the
code from the Visual Basic editor, paste it into your procedure, and create an object that you
use explicitly, as follows:
Dim WordObj as New Word.Application
WordObj.Documents.Add Template:=” Normal.dot”, NewTemplate:= False,
Chapter 15 ¦ Exchanging Access Data with Office Applications 371

Figure 15-12: The Macro Recorder records all events until you click the Stop button.

The Macro Recorder enables you to effortlessly create long and complete Automation code
without ever needing to read the Automation Server™s documentation.
¦ ¦ ¦

on a Network
. . . .

In This Chapter

I n most business environments, very few things are done solely
Resource sharing
by individuals. Projects are planned, discussed, dissected, and
and security
carried out by teams of people working together. If one of the
final products of a project is to be an Office document, it™s helpful
Collaborating in Word
if all members of the team can share information, files, and ideas
online ” either via the company™s internal computer network or
Sharing Excel
(if team members are more far-flung) via the Internet.
Office makes it possible!
Collaborating in
Resource Sharing and Security PowerPoint

Sharing Access
If your computer is hooked up to a local network of some type,
chances are good you have a choice of saving your files either to
your own computer or to a location somewhere on the network.
Distributing Office
Access to various folders on the network is overseen by whoever documents
looks after the network; it™s quite likely that many people not in
your workgroup have access to a particular folder. However, in
. . . .
most Office applications you can control who has access to files
you place in network folders. You can also allow or deny access
by network users to your own computer™s hard drive.

Setting file-sharing options
when saving
Whenever you save a Word or Excel document, you have the
option of restricting access to it.
In Word™s standard Save or Save As dialog box, choose
Tools_Security Options. This opens the Security dialog box
shown in Figure 16-1.
Part II ¦ Collaborating and Integrating with Office 2003

Figure 16-1: The Security dialog box in Word lets you restrict access to any file.

Three levels of file-sharing security are provided here:
¦ Password to open. If you enter a password here, only someone who knows the
password can open the file. (Passwords can be up to 15 characters long and can
contain letters, numbers, and symbols. They are case-sensitive. As you type them
in, only asterisks are displayed.)

Password protection isn™t as secure as you might think; there are utilities available on the Internet
that claim to be able to crack open protected documents (in fact, a common question in Office-
related newsgroups is “I™ve forgotten my password; how do I get in?”).

¦ Password to modify. If you enter a password here, anyone can open the file, but
only someone who knows the password can modify it. Users who don™t know the
password can open the file only as read-only ” and that includes you if you forget
your password, so don™t!
¦ Read-only recommended. If you check this, users opening this file will get a
message suggesting they open it as a read-only file. If they do, they can™t change
the original document; instead, any changes they make must be saved as a new
document, under a different name.
In Excel, you have the same options, but you get to them by choosing Tools_General
Options in the Save or Save As dialog box.
In PowerPoint, you have only the password-protection options; you don™t have the Read-
only recommended option. You get to the password-protection options by choosing
Tools_Security Options in the Save or Save As dialog box.
Chapter 16 ¦ Collaborating on a Network 375

Word offers additional Privacy options: You can choose to remove personal information
(e.g., the document author™s name and the names of people who have added comments)
from the file before it is saved; have Word warn you before printing, saving, or sending a
file that contains tracked changes or comments; and stop Word™s usual practice of generating
random numbers during merge activities to indicate to itself that two documents are related.
Even though those numbers are hidden in the files, they could conceivably be used to show
that two documents were related. Be aware, however, that removing this option will reduce
the accuracy of merging operations.

Protecting documents
In addition, you can fine-tune the level of access you want to allow people to have to a
particular file by applying protection to it.

Protecting documents in Word
To protect a document in Word:
1. Choose Tools_Protect Document (or click the Protect Document button in the
Security Options dialog box from the previous section). This opens the Document

<< . .

. 32
( : 51)

. . >>