<<

. 9
( 14)



>>

the .asp file a name in the File Name box, and click OK.

In this example, because we started with a blank page, FrontPage assigns an ASP filename
Note
extension by default. However, that doesn™t always happen if the page isn™t blank, so make a
point of assigning ASP file format to this page.

8. Create a new Web page (or open an existing one), and choose Insert _ Web
Component _ Spreadsheets and Charts. Click Office Spreadsheet in the Choose a
Control list of the Insert Web Component dialog box.
9. Click Finish to generate a spreadsheet. In the spreadsheet component, click the
Commands and Options icon to open the Commands and Options dialog box.
Chapter 14 ¦ Integrating FrontPage with Office Applications 341

10. In the Import tab of the Commands and Options dialog box, choose HTML from the
Data Type drop-down list, and enter the URL for your page (the one you saved in
Step 7), as shown in Figure 14-19.




Figure 14-19 This spreadsheet component has been associated with a Web page
with a database region.


Entering the absolute URL (that is, the entire URL, including http://www) seems to work
Note
best.

11. To continually update the spreadsheet, click the Refresh Data from URL at Run
Time checkbox.
12. Click Import Now. The spreadsheet will display the content of the associated
database region.

Creating Office PivotTables
Of the three interactive Office Web components that you can use in a Web page, PivotTables
are the most complex. PivotTables themselves are fairly complicated. A full discussion of
PivotTables is beyond the scope of this book, but in short, PivotTables summarize data from
a table. Therefore, for example, if you have a list of 500 orders for 12 products and the dates
the orders were placed, a PivotTable could summarize how many orders had been placed for
each of the 12 products. Or, the PivotTable could be used to total how many orders were
placed each day.
Assuming that you and your visitors are comfortable designing and manipulating fields in a
PivotTable, you can create an interactive PivotTable that summarizes data in an Excel file or
Access database table.
Part II ¦ Collaborating and Integrating with Office 2003
342


Connecting a PivotTable to an Excel data source
Steve Martin used to do a comedy routine around the theme of “how to make a million
dollars and not pay any taxes.” Part of the joke was that his starting point was “go get a
million dollars,” and then he would fill you in on the rest. The story™s relevance to
connecting a PivotTable to an Excel data source is that PivotTables are a rather complex art,
and an advanced spreadsheet skill. If you™re already comfortable with them, we can show
you how to plug them into a FrontPage Web site.
Even if you are comfortable with PivotTables, connecting a PivotTable to an Excel data
source is not a simple process. The basic process involves first connecting an existing Excel
file to your site as a recognized Web database, and then generating a PivotTable from a
named range in that file.
To generate an interactive online PivotTable from an Excel spreadsheet, follow these steps:
1. Create or open in Excel a worksheet that has the information you want to summarize
in your PivotTable.
2. Select the data and then choose Insert _ Name _ Define.
3. Assign a range name (for example, “Data”). You may want to jot down the range
name, because you™ll need it again in a later step.
4. Save the Excel file and note the filename and folder to which it is saved.
5. In Page view, open the FrontPage Web page in which you will insert the PivotTable.
6. Select Insert _ Web Component _ Spreadsheets and Charts. Choose Office
PivotTable, and click Finish. A blank PivotTable appears in Page view.
7. Click the Commands and Options button (the only active button in the PivotTable
toolbar) to open the Commands and Options dialog box, shown in Figure 14-20.




Figure 14-20: Buried in the PivotTable Commands and Options dialog box are the
elements needed to connect your PivotTable to a data source.
Chapter 14 ¦ Integrating FrontPage with Office Applications 343

8. Choose the Data Source tab in the Commands and Options dialog box. Click the
Connection radio button, and then click the Edit button. The Select Data Source
dialog box appears, as shown in Figure 14-21.




Figure 14-21 Choosing an Excel file to link to your Web PivotTable

9. Click the New Source button. You are about to connect your Excel file with your
Web site. The Data Connection Wizard opens to walk you through that process.
10. Choose ODBC DSN as your database source. Click Next.
11. Choose Excel files as your data source, and click Next.
12. Navigate to your Excel Workbook in the Select Workbook dialog box. Choose your
Excel file and click Next.
13. The Data Connection Wizard displays available named ranges (also referred to for
these purposes as “tables”) in your selected spreadsheet. Select one of these tables
and click Next.
14. In the final Wizard window, enter a description and keywords to help identify and
locate the PivotTable. These are optional. After you enter a description and
keywords, click Finish. You will be returned to the Select Data Source dialog box,
where your newly defined database connection (to your spreadsheet) is now one of
the connection options. Click Open to connect your selected spreadsheet to the
PivotTable.
15. Your PivotTable is now connected to your spreadsheet, and is ready to have fields
added. Click the Field List icon in the PivotTable toolbar to display field names, as
shown in Figure 14-22.

For a brief step-by-step explanation of how to put fields into a PivotTable, see the section
Note
“Adding fields to a PivotTable” later in this chapter.
Part II ¦ Collaborating and Integrating with Office 2003
344




Figure 14-22: Once your PivotTable is connected to a data source, you can
add fields.

16. Drag fields into place in your PivotTable.
17. Save your page. Your PivotTable is now ready to be both accessed by visitors to
your Web site and utilized to synthesize table data, as shown in Figure 14-23.




Figure 14-23 A completed PivotTable in FrontPage
Chapter 14 ¦ Integrating FrontPage with Office Applications 345


Defining a PivotTable
Here is a quick summary of how to put a PivotTable together:
After your PivotTable control is connected to an Excel data source, you can use the Field List button
in the PivotTable toolbar to add fields to the PivotTable displayed. Every PivotTable requires at least
one Row or Column field and at least one Total or Detail Field.
The basic concept is to summarize data by sorting it into categories. For example, if you wrote
books for a dozen publishers over the past four years, you could produce a PivotTable listing how
many books you wrote for each publisher each year by making Year the column field, Publisher the
Row field, and Books Written the Detail field.

Adding fields to a PivotTable
To add fields to a PivotTable, follow these steps:
1. Click the Field List button in the PivotTable toolbar to display a list of fields in your
database.
2. Drag one of the fields into the Drop Column Fields Here area of the PivotTable, and
drag one field into the Drop Row Fields Here area, as shown in Figure 14-24.




Figure 14-24: Adding fields to a PivotTable
Part II ¦ Collaborating and Integrating with Office 2003
346

3. You must have at least one field in the Detail area (in the middle of the PivotTable),
so drag a field from the Field list into the middle of the PivotTable.

The field in the Detail area normally displays values. These values can be summed, counted, or
Note
have other calculations performed on them.

4. You can drag a field into the Drop Filter Fields Here area at the top of the PivotTable
to create a filtering drop-down list that will control what is displayed in the entire
PivotTable. This field is optional and simply provides a higher level of filtering in
addition to the options you already have in the PivotTable.
5. After you define your PivotTable, close the PivotTable Field List dialog box. Note
that each field has a drop-down list associated with it. Use the checkboxes in these
drop-down lists to filter your PivotTable results, as shown in Figure 14-25.




Figure 14-25: Filtering a PivotTable enables you to fine-tune your analysis of data.
Chapter 14 ¦ Integrating FrontPage with Office Applications 347

6. You can remove fields by right-clicking them and selecting Remove Field from the
context menu.
7. To calculate (count, sum, find maximum or minimum value), right-click a field in
the Detail area and select AutoCalc. Then choose from calculation options such as
sum, count, or average.
8. You can turn subtotaling on or off for fields where it applies by right-clicking on a
field in the PivotTable and selecting or deselecting Subtotal from the context menu.

Formatting and calculating PivotTable data
After you define your PivotTable, save your Web page and preview it in Internet Explorer to
test it. Figure 14-26 shows an interactive PivotTable in Internet Explorer.




Figure 14-26: Visitors can do their own synthesis of your data with a PivotTable in
Internet Explorer ” as long as they have Office 2003 installed.
Part II ¦ Collaborating and Integrating with Office 2003
348

In FrontPage, you can format, calculate, and sort PivotTable data by using the PivotTable
toolbar, by right-clicking and choosing context menu options, or by using the Commands
and Options button in the PivotTable toolbar to open the Commands and Options dialog
box.
Some, but not all, of these filtering, sorting, formatting, and calculating features are
available for visitors when they work with the PivotTable in a Web site. You can control
table protection using the ActiveX dialog box associated with your PivotTable. This
process is the same as for the ActiveX dialog box associated with a spreadsheet, explored
earlier in this chapter.

Implementing an Office spreadsheet Web component
In this tutorial, you™ll add a spreadsheet to a Web page.
1. Open an existing FrontPage Web or create a new one. Open a Web page in Page
view.
2. Enter the title See How Much of Your Time You Spend Commuting on the page,
and then press Enter.
3. Select Insert _ Web Component _ Spreadsheets and Charts, and choose Office
Spreadsheet. Click Finish.
4. Click in cell A1 of the spreadsheet and type How many hours do you spend
commuting? Press Enter.
5. In cell A3 of the spreadsheet, enter =A2/24.
6. Click the Commands and Options button in the spreadsheet. In the Format tab, select
Percent from the Number Format drop-down list.
7. Click in cell A2. In the Commands and Options dialog box™s Format tab, deselect
the Lock Cells icon.
8. In the Protection tab of the Commands and Options dialog box, select Protect Active
Sheet.
9. Save the file, and preview it in Internet Explorer.
10. While testing the spreadsheet in Internet Explorer, attempt to enter text in cell A1.
Try to enter a number in cell A3. You should see a warning like the one shown on
the bottom of the screen in Figure 14-27.
Chapter 14 ¦ Integrating FrontPage with Office Applications 349




Figure 14-27: You can lock cells in a spreadsheet so that visitors can enter data
only in cells that you designate to accept input.



From FrontPage to Office 2003
The discussion thus far has focused on how to create Web page content in Office and
transfer it into FrontPage Web pages. You can also collect information in FrontPage and
send it to text or spreadsheet files that are stored at your Web server.

Collecting data from input forms requires some advanced FrontPage skills that are covered in
Note
Chapter 17 of FrontPage 2003 Bible. This section takes only a quick look at input forms from the
perspective of collecting data that can be used in a spreadsheet or text file.

Even before you examine how to create your own custom input forms, you can begin to
experiment with the Feedback Form page template, which contains a pre-made input form.
Part II ¦ Collaborating and Integrating with Office 2003
350


Sending data to Word mail-merge files
You can create an input form by using the page template with an input form (using the
Feedback Form page template, for example). With a Web open, select File _ New _ Page,
and double-click one of the templates with an input form. The input forms are filled with
different text and input fields and are surrounded by a dashed line.
To create an input form that sends data to a .doc file, follow these steps:
1. With a form on your page, right-click anywhere in the form (within the dashed lines)
and select Form Properties from the context menu. The Form Properties dialog box
appears.
2. Click the Send To option button and enter a filename with a .doc filename
extension (for example, maillist.doc).
3. After you name the target file (the .doc filename extension is important), click the
Options button in the dialog box and pull down the File Format list. Choose Text
Database Using Tab As a Separator, as shown in Figure 14-28.

Make sure you retain the .doc filename extension. FrontPage will try to change your filename
Note
extension to .txt when you choose the Text Database Using Tab As a Separator format.




Figure 14-28: Sending input to a Word file

4. Click OK.
5. Save your Web page.
You can test your input form by clicking the Preview in Browser button and entering
information in the input form. After you do, click the Submit button.
Chapter 14 ¦ Integrating FrontPage with Office Applications 351

You will see your .doc file in Folder view (you may have to press the F5 function key to
refresh the Folder view). As data is saved to your .doc file, you can open the file in Word
by double-clicking it. With fields separated by tabs, you can use this file as a mail-merge
data file in Word.

Sending data to Excel
You can save data to files that will open in Excel by using the same procedure previously
outlined for saving to a Word file. The only difference is that your filename should have an
.xls extension (for example, Feedback.xls). When you save tab-delimited text to an
Excel file, you can open that file in Excel by double-clicking it.

Sending reports to Excel
With FrontPage 2003, you can save reports as HTML files and then open them in Excel for
printing, graphing, sorting, or other analysis.
To save a report as an HTML page, view the report (select View _ Reports and choose any
report except for Summary). With the Report in view, choose File _ Save As, and save the
file as an HTML file to any folder on your Web site or your local computer.


Note
When you save your report, use an .htm filename extension.

Excel 2003 will open these HTML files for analysis, as shown in Figure 14-29.




Figure 14-29: Viewing a FrontPage site report in Excel
Part II ¦ Collaborating and Integrating with Office 2003
352


You can save any report as an HTML file except the Site Summary report. Unfortunately, you
Caution
can™t even copy and paste the cells from the Site Summary report into a spreadsheet.


Summary
With Office 2003, Microsoft has continued to smooth the integration between FrontPage and
other Office applications. You can easily integrate Word documents, Excel spreadsheets, and
PowerPoint presentations into your FrontPage Web.
You can also use FrontPage to create special components that allow you to put a little bit of
Excel into your Web pages. These components display active, working spreadsheets,
dynamically linked graphs, and even complex pivot tables in a Web browser.
Finally, you can export elements of a FrontPage Web into other Office applications. One
particularly useful example of this is sending FrontPage reports to Excel, where you can
sort, calculate, or even graph information about your Web site.
¦ ¦ ¦
15 CHAPTER



Exchanging
Access Data
with Office . . . .


Applications In This Chapter

Using Automation to
integrate with Office

Creating Automation
references

Creating an instance of
This chapter originally appears in the Access 2003 Bible, which
an Automation object
includes a CD with applicable sample databases and practice
Note
files. If you had that book and CD, you would use the database
Getting an existing
named CHAP33Start.mdb. A word template file named
object instance
Thanks.dot, is also included for use in this chapter. You would
need to copy those files to your machine now. There is no
Working with
CHAP33End.mdb. Because this chapter relies on the use of Vi-
Automation objects
sual Basic code, it and the forms that are driven by it have al-
ready been created for you.
Closing an instance of

A s companies standardize their computer practices and an Automation object
software selections, it is becoming more and more important
to develop total solutions: In other words, solutions that integrate Using Microsoft Word to
the many procedures of an organization. Usually, various create an Automation
procedures are accomplished by using different software packages, example
such as Word for letter writing, Exchange and Outlook for mailing
and faxing, Powerpoint for presentations, and Excel for financial Using Office™s Macro
functions. If the organization for which you are developing has Recorder
standardized on the Microsoft Office suite, you can leverage your
knowledge of Visual Basic for Applications to program for all of
. . . .
these products.
Part II ¦ Collaborating and Integrating with Office 2003
354


Automation, formerly called OLE Automation, is a means by which an application can expose
objects, each with its own methods and properties, that other applications can create instances
Note
of and control through code. Not all commercial applications support Automation, but more and
more applications are adopting Automation to replace the outdated DDE interface. Consult with
a specific application™s vendor to find out if it supports or plans to support Automation in the
program.


Using Automation to Integrate with Office
The Microsoft Office applications mentioned in the previous section all support Automation.
Using Automation, you can create objects in your code that represent other applications. By
manipulating these objects (setting properties and calling methods), you can control the
referenced applications as though you were programming directly in them, thus allowing you
to create seamless integrated applications by using Automation.

Creating Automation references
Applications that support Automation provide information about their objects in an object
library. The object library contains information about an application™s properties,
methods, and classes. An application™s class is its internal structure for objects; each class
creates a specific type of object”a form, a report, and so on. To reference an application™s
objects, Visual Basic must determine which specific type of object is being referenced by
an object™s variable in your code. The process of determining the type of an object
variable is called binding. You can use two methods for binding an object”early binding
and late binding.

Early binding an object
Using the References dialog box in the Visual Basic window of Access, you can explicitly
reference an object library. When you explicitly reference an object library, you are
performing early binding. Automation code executes more quickly when you use early
binding.

To access the References dialog box of VBA, you need to activate the Visual Basic window by
Note
either creating a new module or displaying the design of an existing module.

To create a reference, first create a new module or open any existing module in your
application database in the Visual Basic Design screen. After you have a module in Design
view, a new command, References, is available from the Tools menu. Figure 15-1 shows the
References selection on the Tools menu. Select Tools_References to access the References
dialog box. Figure 15-2 shows the References dialog box.
Chapter 15 ¦ Exchanging Access Data with Office Applications 355




Figure 15-1: The Tools_References menu item is available only after you have a
module in Design or New view in Access. This menu item activates the VBA window.




Figure 15-2: Early binding by setting references is the most efficient way to perform
Automation.

In the References dialog box, you specify all the references that your application needs for
using Automation or for using other Access databases as library databases. To select or
deselect a reference, click its check box.
Part II ¦ Collaborating and Integrating with Office 2003
356


For this chapter, you will need to make sure that several reference libraries are active. You may
Caution
not initially have the following four references available (checked):
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects Recordset 2.7 Library
Microsoft Word 11.0 Object Library
Microsoft Office 11.0 Object Library

If these libraries aren™t active (or, visible at the top of the list), find them in the selection list box
by scrolling to them, and then check them on.

After you reference an application for Automation, you can explicitly dimension any object
variable in that reference library. The New object coding help feature displays the available
objects as you type, as shown in Figure 15-3. In addition, after you have selected the primary
object and have entered a period (.), the help feature of Access enables you to select from the
available class objects (see Figure 15-4).

Late binding an object
If you don™t explicitly reference an object library by using the References dialog box, you can
set an object™s reference in code by first declaring a variable as an object and then using the
Set command to create the object reference. This process is known as late binding.
To create an object to reference Microsoft Word, for example, you can use the following
code:
Dim WordObj As Object
Set WordObj = New Word.Application

The Set command is discussed in the next section.

If you create an object for an application that is not referenced, no drop-down help box, such as
Tip
the ones shown in Figures 15-3 and 15-4, will display.
Chapter 15 ¦ Exchanging Access Data with Office Applications 357




Figure 15-3: When an Automation Server is referenced, its objects are immediately
known by Visual Basic.




Figure 15-4: The new drop-down syntax help of Visual Basic makes using referenced
Automation Servers easy.

Figure 15-3 shows the automatic drop-down box that appears immediately after you type the
word new in the Dim statement. At this point, you can select one of the application object
name types displayed (such as word) or enter a new application object name type that you
define. Figure 15-4 shows the new drop-down box that appears when you type a period (.)
after the object type word. This box helps you by displaying all known object types that can
be associated with the particular primary object name. In this case, clicking the Application
object type adds this to the word. portion of the object, thus word.application.
Part II ¦ Collaborating and Integrating with Office 2003
358


Creating an instance of an Automation object
To perform an Automation operation, the operating system needs to start the application”if it
isn™t already started”and obtain a reference, or handle, to it. This reference will be used to
access the application. Most applications that support Automation, called Automation
Servers, expose an Application object. The Application object exists at the top of the object
application™s hierarchy and often contains many objects, as well.

Using the New keyword to create a new instance
The simplest (and most efficient) method to create any Automation object is to early bind the
specific Automation Server reference library to the module by activating it, using the
Tools_References menu. After you bind it, you can then create a new instance of the object
by using the New keyword in Visual Basic. In the examples shown in Figure 15-3 and Figure
15-4, the variable MyWordObj is set to a new instance of Word™s Application object. If you
have not bound the Microsoft Word 11.0 Object Library, you will need to do so or you will
receive an error.

Caution If you don™t create a reference to the Automation Server by using the References dialog box,
Visual Basic doesn™t recognize the object type and generates an error on compile.

Every time you create an instance of an Automation Server by using the New keyword, a new
instance of the application is started. If you don™t want to start a new instance of the
application, use the GetObject function, which is discussed later in this chapter. Not all
Automation Servers support the New keyword. Consult the specific Automation Server™s
documentation to determine whether it supports the New keyword. If the New keyword is not
supported, you need to use the CreateObject function, which is discussed in the
following section, to create an instance of the Automation Server.

Using the CreateObject function to create a new instance
In addition to creating an instance of an object library by using the New keyword, you can
create an instance of an object library by using the CreateObject function. You use the
CreateObject function to create instances of object libraries that do not support the New
keyword. To use the CreateObject function, first declare a variable of the type equal to
the type of object that you want to create. Then use the Set statement in conjunction with the
CreateObject function to set the variable to a new instance of the object library.
For example, Microsoft Binder doesn™t support the New keyword, but it does provide an
object library, so you can reference it by using the References dialog box. To early bind the
object library of Binder, use the CreateObject function, as shown in the following code:
Dim BinderObj As OfficeBinder.Binder
Set BinderObj = CreateObject(“Office.Binder”)
Chapter 15 ¦ Exchanging Access Data with Office Applications 359


In the preceding example, the object library name for Binder is OfficeBinder.Binder,
Note
and the class instance is “Office.Binder.” You can view the names of object libraries
and their available classes by using the Object Browser.

You can create an object instance with the CreateObject function, which is late bound,
by not declaring the object variable as a specific type. For example, the following code
creates an instance of the Binder object by using late binding:
Dim BinderObj As Object
Set BinderObj = CreateObject(“Office.Binder”)


If you have different versions of the same Automation Server on your computer, you can specify
Note
the version to use by adding it to the end of the class information. For example, the following
code uses Office as the Automation Server:

Dim BinderObj As Object
Set BinderObj = CreateObject(“Word.Application.11”)


Word 97 was the first true Automation Server, and like its predecessor, Word 2003 doesn™t
Tip require you to specify a version when creating instances of Word object libraries; Word is al-
ways used, regardless of the other versions of Word on the computer. In fact, you get an error
if you try to specify a version number. Therefore, you can use the following syntax instead:

Set BinderObj = CreateObject(“Word.Application.11”)


Getting an existing object instance
As stated previously in this chapter, using the New keyword or the CreateObject function
creates a new instance of the Automation Server. If you don™t want a new instance of the
server created each time you create an object, use the GetObject function. The format of
the GetObject function is as follows:
Set objectvariable = GetObject([pathname][, class])

The pathname parameter is optional. To use this parameter, you specify a full path and file
name to an existing file for use with the Automation Server.

The specified document is then opened in the server application. Even if you omit the param-
Note
eter, you must still include the comma (,).

The class parameter is the same parameter that™s used with the CreateObject function.
See Table 15-1 for a list of some class arguments used in Microsoft Office.
Part II ¦ Collaborating and Integrating with Office 2003
360


Table 15-1
Class Arguments for Common Office Components
Component Class Argument Object Returned
Access Microsoft Access Application object
Access.Application

Excel Microsoft Excel Application object
Excel.Application

Microsoft Excel Workbook object
Excel.Sheet

Microsoft Excel Chart object
Excel.Chart

Word Microsoft Word Application object
Word.Application

Microsoft Word Document object
Word.Document


For example, to work with an existing instance of Microsoft Word, but not a specific Word
document, you can use the following code:
Dim WordObj as Word.Application
Set WordObj = GetObject(, “Word.Application”)

To get an instance of an existing Word document called MyDoc.Doc, on your C: drive, you
can use the following code:
Dim WordObj as Word.Application
Set WordObj = GetObject(“c:\MyDoc.Doc”, “Word.Application”)

Of course, this code is always placed in a new function or sub that you declare in your
module.

Working with Automation objects
After you have a valid instance of an Automation Server, you manipulate the object as though
you were writing code within the application itself, using the exposed objects and their
properties and methods.
For example, when developing directly in Word, you can use the following code to change
the directory that Word uses when opening an existing file:
ChangeFileOpenDirectory “C:\My Documents\”


Consult the development help for the Automation Server for specific information on the objects,
Note
properties, and methods available.
Chapter 15 ¦ Exchanging Access Data with Office Applications 361

Just as in Access, Word is implicitly using its Application object; the command
ChangeFileOpenDirectory is really a method of the Application object. Using
the following code, you create an instance of Word™s Application object and call the
method of the object:
Dim WordObj As New Word.Application
WordObj.ChangeFileOpenDirectory “C:\My Documents\”


When using Automation, you should avoid setting properties or calling methods that cause the
Tip Automation Server to ask for input from the user via a dialog box. When a dialog box is dis-
played, the Automation code stops executing until the dialog box is closed. If the server applica-
tion is minimized or behind other windows, the user may not even be aware that he or she
needs to provide input, and therefore may assume that the application is locked up.


Closing an instance of an Automation object
Automation objects are closed when the Automation object variable goes out of scope. Such a
closing, however, doesn™t necessarily free up all resources that are used by the object, so you
should explicitly close the instance of the Automation object. You can close an Automation
object by doing either of the following:
• Using the Close or Quit method of the object (consult the specific Automation
Server™s documentation for information on which method it supports)
• Setting the object variable to nothing, as follows:
Set WordObj = Nothing

The best way to close an instance of an Automation object is to combine the two techniques,
like this:
WordObj.Quit
Set WordObj = Nothing



An Automation Example Using Word
Perhaps the most common Office application that is used for Automation from a database
application like Access is Word. Using Automation with Word, you can create letters that
are tailored with information from databases. The following section demonstrates an
example of merging information from an Access database to a letter in Word by using
Automation and Word™s Bookmarks. Ordinarily, you create a merge document in Word and
bring field contents in from the records of an Access database. This method relies on using
Word™s MergeField, which is replaced by the contents of the Database field. It normally
requires that you perform this action in Word”thus limiting the scope and capability of the
function. For example, you will merge all records from the table that is being used rather
than a single record.
Part II ¦ Collaborating and Integrating with Office 2003
362

The following example uses the Orders form, which calls a module named WordIntegration.
The WordIntegration module contains a function named MergetoWord() that uses the Word
Thanks.dot template file.

When you attempt to run this example, you must make sure that the path for the template in the
Note
Visual Basic code is the actual path in which the Thanks.dot template file resides. This path
may vary from computer to computer.

The items that are discussed in this Word Automation example include the following:
¦ Creating an instance of a Word object
¦ Making the instance of Word visible
¦ Creating a new document based on an existing template
¦ Using bookmarks to insert data
¦ Activating the instance of Word
¦ Moving the cursor in Word
¦ Closing the instance of the Word object without closing Word
This example prints a thank-you letter for an order based on bookmarks in the thank you
letter template (Thanks.dot). Figure 15-5 shows the data for customers; Figure 15-6 shows the
data entry form for orders; Figure 15-7 shows the Thanks.dot template; and Figure 15-8
shows a completed merge letter.
The bookmarks in Figure 15-7 are shown as grayed large I-beams (text insert). The
bookmarks are normally not visible, but you can make them visible by selecting
Tools_Options, selecting the View tab and going to the top section titled Show and then
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
364


If you click the Print Thank You Letter button in Access while Word is open with an existing
Caution
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”, _
dbOpenTable)
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
WordObj.Documents.Add

˜ WARNING:
˜ 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

NewTemplate:=False
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 “”
Else
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”
Else
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]
DoEvents
WordObj.Activate
WordObj.Selection.MoveUp wdLine, 6
˜ Set the Word Object to nothing to free resources
Set WordObj = Nothing
DoCmd.Hourglass False
Exit Function
TemplateError:
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
366

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
CreateObject.

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-
Caution
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”, _
NewTemplate:=False

Note
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
Note
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:
WordObj.Activate


Depending on the processing that is occurring at the time, Access may take the focus back from
Tip
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
368

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:
WordObj.Quit


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:= _
“D:\GRAPHICS\PICTURE.BMP”, LinkToFile:=False,
SaveWithDocument _
:=True


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
370




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,
DocumentType:=0

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,
DocumentType:=0
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.
¦ ¦ ¦
16 CHAPTER



Collaborating
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.
workbooks
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,
databases
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
374




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
Caution
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
Protection task pane shown in Figure 16-2.




Figure 16-2: Protect Word documents using this task pane.
Part II ¦ Collaborating and Integrating with Office 2003
376

2. Under Formatting restrictions, check the checkbox if you want to limit formatting
to a selection of styles, and then click Settings to open the Formatting Restrictions
dialog box (see Figure 16-3).




Figure 16-3: Specify formatting restrictions on a shared document here.

3. Uncheck any styles you don™t want to allow in the document, or click the
Recommended Minimum button to have Office automatically select what it
considers to be a minimum number of styles. Click All to check all styles and None
to uncheck them all.
4. If you want to allow AutoFormat to override these formatting restrictions, check
that box at the bottom of the dialog box, and then click OK.
5. Back in the Document Protection task pane, if you want to allow only certain types of
editing in the document, check the Editing restrictions box. This activates a drop-
down list with four options: Tracked changes (all changes are permitted, but they™re
automatically tracked), Comments (no changes are permitted, but comments can be
inserted), Filling in forms (no changes are permitted, but data can be entered into
forms), and No changes (no changes are permitted ” the document is read-only).
6. Next, enter any exceptions to the editing rules. If you have established user groups,
they™re listed; otherwise, click More users and enter the user names for those to
whom you want to give greater editing access in the Add Users dialog box that
appears.
7. Finally, back in the Document Protection task pane, click the Yes, start enforcing
protection button if you™re ready to apply the protection settings to your document.
Chapter 16 ¦ Collaborating on a Network 377


Protecting documents in Excel
To protect an Excel worksheet or workbook:
1. Choose Tools_Protection.
2. From the submenu, choose which part of your Excel document you want to protect:
a particular worksheet or the workbook. You can also choose to protect and share
your workbook (more on sharing workbooks a little later in this chapter).
3. If you choose Protect Sheet, you™ll see the dialog box shown in Figure 16-4. Here
you can enter a password to unprotect the sheet, and then choose from the long
list provided which actions you™re willing to allow users of the worksheet to
perform.




Figure 16--4: Set protection for Excel worksheets here.

4. If you choose Protect Workbook, you™ll see the dialog box shown in Figure 16-5,
which contains three options:
• Structure prevents users from adding, deleting, moving, hiding, or unhiding
worksheets.
• Windows prevents users from moving, hiding, unhiding, resizing, or closing
workbook windows.
• Password allows you to enter a password that users must have before they can
unprotect the workbook.
Part II ¦ Collaborating and Integrating with Office 2003
378




Figure 16-5: Protect elements of your workbook here.

5. Protect and Share Workbook brings up a dialog box with only one box you can
check, to prevent those sharing the workbook from turning off change tracking.
You can enter a password that they™ll have to know before they can do so.
6. Allow Users to Edit Ranges opens the dialog box shown in Figure 16-6. Here you
can apply passwords to specific ranges within your worksheet. Even if the
worksheet as a whole is protected, users who have the password can edit the ranges
you specify. You can also click Permissions to specify which users are allowed to
edit the range without a password, and just so you don™t forget, you can even paste
permissions information into a new workbook so you can refer to it easily.




Figure 16-6: You can make ranges available for editing to those with the correct
password even if the rest of the sheet is protected.


Protecting files in Access and PowerPoint
You™ll learn about protecting Access files in detail later in this chapter. You need to use file
system features to protect PowerPoint files; talk to your system administrator.
Chapter 16 ¦ Collaborating on a Network 379


Using Information Rights Management tools
In previous versions of Office, the only way to protect sensitive information was to limit
access to it, as described in the preceding sections. That didn™t necessarily prevent the
people who were granted access from copying the information and/or sending it to someone
who wasn™t supposed to have access to it.
Information Rights Management (IRM) is a new feature in Office 2003 that gives you
greater control over files even when they™re no longer on your computer or network. No
matter where the file goes, the permissions you™ve assigned go with it, so that only those
users you™ve approved can read or change it; you can also restrict printing and forwarding.

In order to use IRM, you must have access to a computer running Windows Server 2003, with
Windows Rights Management activated. If you are working in a networked environment, con-
Note
sult your network administrator for details. As of this writing, Microsoft offers a trial Internet-
based service for individuals based on the .NET passport system; follow the prompts the first
time you attempt to use the feature to sign up for that service if it™s available (because it™s just a
trial service at this writing, it may not be by the time you read this book). Undoubtedly other
providers of public IRM servers will come forward as well.

Whenever you create a document in Word, Excel, or PowerPoint, you can set IRM policies
for it by choosing File_Permission to open the Permission dialog box (see Figure 16-7).




Figure 16-7: The Permission dialog box allows you to enter the e-mail addresses of
users you™d like to be able to read or change a document™s content.

Check the Restrict permission to this document box to activate the Read and Change
options. Enter the e-mail addresses of users you want to give Read permission to (they can
read the document but can™t change, print, or copy its content) and those you want to give
Change permission to (they can read, edit, and save changes to the document but can™t print
it). Click the Read and/or Change buttons to access e-mail addresses in your Address book.
Part II ¦ Collaborating and Integrating with Office 2003
380

To fine-tune permission, click More Options. This opens the dialog box shown in Figure 16-8.




Figure 16-8: Fine-tune the permissions you grant with these controls.

At the top is a list of all the users you™ve given permission to access the document and the
access level they currently have (your name shows up at the top of the list with Full
Control). Highlight the user whose permissions you™d like to fine-tune, and then choose
from the options in the Additional permissions for users area. You can:
¦ Set an expiration date for the user™s permission.
¦ Allow users to print content.
¦ Allow a user with read access to also copy content.
¦ Give specific users permission to read a document, print a document, copy a
document, or edit a document, or any combination of those; you can also set an
expiration date.
¦ Allow users to access the content programmatically ” that is, to open the file in the
same program that created it and edit its content.
Under Additional settings, you can enter a link to an e-mail address (or other hyperlink) that
will pop up whenever a document with restricted permission is forwarded to an unauthorized
individual, so that that individual can request permission to view it. If you leave this blank,
unauthorized individuals simply see an error message.
Chapter 16 ¦ Collaborating on a Network 381

You can also choose to allow users to view the content in a browser; a Rights-Management
Add-on for Internet Explorer makes this possible. Otherwise, IRM-protected files can be
opened in Office 2003 only.

Tip If you generally provide the same permissions to many different users, click Set Defaults to
make those permissions the default set.


Network administrators can create permission policies that define who can access documents,
workbooks, and presentations and what editing capabilities (if any) they have. For example, a
Note
company might define a policy called “Confidential” that allows documents to be opened only by
users whose e-mail addresses use the company™s domain name. Once these policies have
been defined, they appear in alphabetical order on a submenu under File_Permission; au-
thors simply choose the one they want to use.


Sharing Excel Workbooks
One of the most common types of Office documents shared on a network is an Excel
workbook because workbooks frequently contain budgetary or sales information that is
constantly being updated by a variety of users. Excel lets multiple users share a workbook so
they can all work on it at the same time; it also lets you combine several workbooks into a
single workbook.

Creating a shared workbook
To create a shared workbook:
1. Choose Tools_Share Workbook. This opens the dialog box shown in Figure 16-9.




Figure 16-9: The Editing tab of the Share Workbook dialog box shows you who
currently has the workbook open.
Part II ¦ Collaborating and Integrating with Office 2003
382

2. If you want more than one person to be able to edit the workbook at the same time,
or to combine several workbooks into one shared workbook, check the box at the
top of the dialog box.
3. To fine-tune the way the workbook is shared, click the Advanced tab (see Figure
16-10). In the Track changes section, choose the number of days you want to track
changes ” if at all.




Figure 16-10: The Advanced tab lets you choose your method of tracking, updat-
ing, and dealing with conflicting changes.

4. In the Update changes section, choose when you want changes made to the
workbook to be updated: whenever the file is saved, or automatically how ever
often you specify. If you choose to automatically update changes, you can choose
to save your changes and see everyone else™s changes at the specified interval, or
just see everyone else™s changes at the specified interval without saving yours.
5. Sometimes two or more users will make conflicting changes to the workbook ”
changes that are mutually exclusive. You can decide here how to deal with those
changes either by having Excel ask you which change should take effect or by
replacing any conflicting changes with your own changes every time you save.
6. Click OK.
Here™s one example of a shared workbook being useful: A sales group could share a
common workbook, with each salesperson in the group recording his or her sales as they
occur; that would give the sales manager the ability to monitor their sales, and the progress
of the group as a whole, in “real time.”
Chapter 16 ¦ Collaborating on a Network 383


Reviewing changes
Once a workbook is being shared, you can review changes in it by choosing Tools_Track
Changes_Accept or Reject Changes. Choose the changes you want to review in the Select
Changes to Accept or Reject dialog box shown in Figure 16-11. You can filter the changes
you want to look at by using the three fields. The When field lets you look for changes made
on a specific date; the Who field lets you look at changes made by everyone, everyone but
you, only you, or only any other user who has made changes; and the Where field lets you
specify a range of cells in which to look for changes.
Any changes found are brought to your attention in the Accept or Reject Changes dialog box
(see Figure 16-12). You can choose to accept or reject any or all of the changes brought to
your attention.

Tip
Choose Tools_Highlight Changes to highlight any changes made throughout the workbook.




Figure 16-11: Use this dialog box to select the changes you want to review.




Figure 16-12: Changes made to the workbook are brought to your attention here.

You can merge different versions of the same shared workbook into a single workbook by
choosing Tools_Compare and Merge Workbooks. Track Changes must be turned on (and
the workbook must be shared) for this to work.
Part II ¦ Collaborating and Integrating with Office 2003
384


Collaborating in PowerPoint
You can send your PowerPoint presentation to others for comment and revision, and then
combine all the reviewed presentations into one for easy review.

Note The easiest way to send a presentation for review is to choose File_Mail Recipient (for Re-
view). This feature, common to most Office applications, is discussed later in this chapter.

To do so, open the presentation you want to combine reviewed presentation with and choose
Tools_Compare and Merge Presentations. Browse for the presentations you want to merge,
and then click Merge.
PowerPoint opens the Revisions Pane and the Reviewing toolbar to allow you to sort
through all the suggested revisions and decide whether you want to apply them (see
Figure 16-13).




Figure 16-13: PowerPoint™s Revisions Pane shows you all the changes reviewers have
made to your presentation.

PowerPoint points out the suggested revisions in several ways. In the Revisions Pane, you
can see graphical representations of the altered slides, or you can view them as a list. You
can choose whether to look at the changes suggested by all reviewers, or just those made by
specific reviewers. The names of reviewers who made changes to a particular slide appear
above the thumbnail of the slide, color-coded. Click the name of any reviewer whose
changes you want to accept.
Chapter 16 ¦ Collaborating on a Network 385

You can also call up a shortcut menu by pointing at the thumbnail and then clicking the
downward-pointing arrow that appears beside it. The shortcut menu also lets you apply
changes by the current reviewer, show only that reviewer™s changes, preview animation (in
case there was a change to an animation) and, finally, finish off your review of that
reviewer™s changes by clicking Done With This Reviewer.
The list version of the changes in the Revisions Pane is a little different; it shows a list of
changes to the slide (text edits, new graphics, etc.), and a separate list of Presentation
changes (slide transitions, for instance). The Previous and Next buttons at the bottom of the
task pane take you from slide to slide.
The Reviewing toolbar, also visible in Figure 16-13, is very similar to Word™s Reviewing
toolbar. You can step from item to item, choose to apply or unapply, edit and delete
comments, choose which reviewers™ changes you want to see, end the review, and toggle the
Revisions pane off and on.

Caution
Clicking End Review discards all unreviewed changes in the merged presentation, so don™t
click it until you™re certain you™re done.

You can also toggle markup on and off. The Markup feature shows callouts detailing
changes made to the presentation without obscuring the presentation or affecting its layout
(see Figure 16-14). Accepting a change is as simple as checking it off in the markup callout.




Figure 16-14: PowerPoint™s Markup feature provides a way to see changes in the
context of the slide they™re on.
Part II ¦ Collaborating and Integrating with Office 2003
386


Sharing Access Databases
The information in the typical Access database is valuable not only to people working in
Access but also to people working in all other Office applications. Typically, the Access
database changes constantly as changes are made to the data in it; by drawing on it, network
users can ensure that their own Office projects always contain the most up-to-date
information.
If you don™t need any extra security on your Access database, you can share it just as you
can any other file in Office (see the first part of this chapter). If you do need extra security,
however, Access can provide it in several ways: passwords, permissions, user groups and
accounts, and encryption.

Using passwords
A password is the easiest way to protect a database. Every time a user tries to access a
password-protected database, he or she is asked to provide a password. Without it, the
database can™t be opened.
To set a password for a particular Access database:
1. Choose File_Open.
2. In the Open dialog box, find the database you want to assign the password to and
select it.
3. Click the down arrow next to the Open button and choose Open Exclusive. This
ensures that no one else can open the database while you are assigning a password
to it.
4. The database opens. Now choose Tools_Security_Set Database Password.
5. In the Set Database Password dialog box, enter the password once in the Password
field and then enter it again in the Verify field (all you™ll see are asterisks).

Note Remember, passwords are case-sensitive, are limited to 15 characters, and can contain letters,
numbers, and/or symbols.

6. Click OK.
Once the password is set, it doesn™t matter if you™re the user who created the database and
assigned the password to it: If you forget or lose the password, you can™t open the database
(at least, not without the use of a third-party password-cracking tool like the ones available
at www.lostpassword.com ” the existence of which is why a password provides only
low-level security).
To remove the password, open the file exclusively again, and then choose
Tools_Security_Unset Database Password. Enter the password and click OK.
Chapter 16 ¦ Collaborating on a Network 387


Creating user and group accounts
If a password doesn™t provide enough security, you might want to set up user accounts and
groups, which will require users to supply both an account name and a password before they
can access a database. This is called user-level security.
To set up user and group accounts:
1. Open a database.
2. Choose Tools_Security_User and Group Accounts. This opens the dialog box
shown in Figure 16-15.




Figure 16-15: Add new users and new user group accounts here.

3. By default, Access creates two groups: Admin and Users. Admin users can perform
administrative functions such as adding users and groups; users can access only the
database itself.
4. By default, Access creates an Admin user called, unimaginatively, Admin. Choose
it from the Name drop-down list, and then click the Change Logon Password tab.
Type the password you want to use in the New Password and Verify fields. (Once
you™ve closed the database and Access, the next time you open it you™ll have to log
on using this account name and password.)
5. Click the Users tab.
6. To create a new account, enter the name of the user in the Name box, and then
select the group you want to add him or her to; click the New button.
7. Enter the name of the user and the personal ID ” a string of four to 20 characters
of your choice that Access combines with the user™s name to identify that user in
the group.

<<

. 9
( 14)



>>