<< . .

. 31
( : 51)

. . >>

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

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

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

Access Data
with Office . . . .

Applications In This Chapter

Using Automation to
integrate with Office

Creating Automation

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

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

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

To access the References dialog box of VBA, you need to activate the Visual Basic window by
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

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

For this chapter, you will need to make sure that several reference libraries are active. You may
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
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
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

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,
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
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-
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

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

Excel Microsoft Excel Application object

Microsoft Excel Workbook object

Microsoft Excel Chart object

Word Microsoft Word Application object

Microsoft Word Document object

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

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,
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:
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

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

<< . .

. 31
( : 51)

. . >>