Using Automation Servers with Access 97

To use Automation, you must first write the code to create an instance in your application of the object that you plan to program. Access VBA contains the following four reserved words that you use to create an instance of a programmable object with Access VBA code:

• The Object data type is assigned to the programmable object variables you declare with {Private|Dim|Public} objName As Object statements. The Object property of a programmable Automation object contained in a bound or unbound object frame control points to the instance of the object. You can assign to an Object variable a pointer to the Automation object with the general syntax

  								Set objName = [Forms!frmName!]uofName.Object
  

• The CreateObject function assigns a pointer to a new instance of an empty programmable object, such as a blank Excel 97 Worksheet object. The general syntax of the CreateObject function is

  								Set objName = CreateObject("ServerName.ObjectType")
  

• The GetObject function assigns a pointer to a new instance of a programmable object whose data is contained in an existing file, strPathFileName, in the following syntax example:

  								Set objName = GetObject(strPathFileName[, _
     "ServerName.ObjectType"])
  

  You can omit the ServerName.ObjectType argument if an entry in the Registry associates the file's extension with the object of the application you want to program. In the case of Excel 97, the default object type for .xls files is Excel.Workbook. If you substitute an empty string ("") for strPathFileName, the preceding statement assigns a pointer to an object of ServerName.ObjectType if such an object is open. If an object of the specified type is not open when the statement is executed, a trappable error occurs.

• The New keyword declares an Object variable and assigns in a single command a pointer to a new instance of an empty programmable object, such as an empty Word 97 document. The general syntax for creating an instance of a programmable object with New is

  {Private|Dim| Public|Static} objName
  								As New [ServerName.]ObjectType
  							

To use the New keyword to create an instance of an Object variable, you must add a reference to the Automation server in Access 97's References dialog. Using the New keyword to create an object instance is called early binding. The advantage of early binding is that the VBA interpreter can check the validity of object references in Design view and the new Auto List Members feature displays candidate objects in a pop-up list (see Figure 31.1). Auto List Members also displays the syntax for object properties and methods.

If the Automation server application is open when you use the CreateObject or GetObject functions or the New reserved word, the type of the Automation server determines whether a new instance of the server is launched or the open instance is used. If the server type is Creatable Single-Instance, as is the case for Word 97, the open instance of Word is used with an empty document (CreateObject) or with a document created from the specified file (GetObject).

The four reserved words of the preceding list also are used by VBA to program objects of other applications. To write Automation code, you need to know the server name of the OA server and the names of the object types created by the server. The best way to become familiar with the object hierarchy of an Automation server is to create a reference to the server and explore the object hierarchy with Access 97's Object Browser.

◂◂ See Using the Object Browser.

Manipulating an Excel 97 Workbook Object

The hierarchy of Excel 97 programmable objects is at least as complex as that of Access 97. Fortunately, the concepts of addressing objects in all Automation-compatible applications is nearly identical. Thus, the techniques you learned in Chapter 27, "Understanding the Data Access Object Class," stand you in good stead when you encounter Excel 97's Excel.Application object. The following sections describe how to create a test workbook, Customers.xls, with a single worksheet, Customers, and how to transfer data to and from the worksheet using Access VBA and Automation.

Creating Customers.xls The examples that follow use the Customers.xls workbook file with data from the Customers table of Northwind.mdb. The example of OLE Automation with Excel 97 in this chapter uses the GetObject() function to open the Customers.xls file and manipulate the data in the worksheet. To create Customers.xls, follow these steps:

The Hierarchy of VBA Excel 95 Objects The following list describes the hierarchy of the most commonly used programmable objects exposed by Excel 97:

• Application represents an invisible instance of Excel 97. Using the Application object, you can execute almost all of Excel's menu commands by applying methods to the Application object. The Application object has properties such as ActiveWorkbook and ActiveSheet that specify the current Workbook and Worksheet objects. You can specify Excel.Application as the value of the ServerName. ObjectType argument of the CreateObject() and GetObject() functions, plus the Dim objName As New Excel.Application statement.

• Workbook is the primary persistent object of Excel 97. Workbook objects are files that contain the other objects you create with Excel 97: Worksheet and Chart objects. Worksheet and Chart objects are contained in Worksheets and Charts collections, respectively.

• Worksheet objects are elements of the Workbook object that contain data. The primary interaction between Access 97 and Excel 97 takes place with Worksheet objects. You can transfer data contained in rows and columns of an Access Recordset object to cells in an Excel Worksheet object and vice versa. If you specify Excel. Sheet as the value of the ServerName.ObjectName argument of the GetObject function or use the Dim objName As New Excel.Sheet statement, the first member of the Worksheets collection—the ActiveSheet property of the Workbook object—is opened by default.

• Range objects are groups of cells specified by sets of cell coordinates. You can get or set the values of a single cell or group of cells by specifying the Range object. If the cells are contained in a named range, you can use the range name to refer to the group of cells. (A group of cells is not the same as a collection of cells; no Cells collection exists in Excel 97.)

  The Cells method specifies the coordinates of a Range object. Although no Cell object exists in Excel 97, you can use the Cells method to read or set the value of a single cell or group of cells, specified by coordinate sets.

Excel 97 exposes a variety of other objects. The preceding four objects, however, are those that you use most often in conjunction with Automation operations that are executed with Access VBA code.

Using Excel's Online Help File for VBA If you don't have a printed copy of the Microsoft Excel Visual Basic for Applications Reference, you can open the Vbaxl8.hlp online help file to act as a reference for the objects, methods, and properties exposed by Excel 97. To open Vbaxl8.hlp while running Access and to display objects and methods references, follow these steps:

Creating an Excel Reference and Using Access 97's Object Browser Creating a reference to the object or type library of Automation servers offers the following advantages:

• The Access VBA compiler checks the syntax of your Automation code for consistency with the properties and methods of the objects you declare. This test is not made if you don't declare a reference to the Automation server object.

• You can use the Object Browser to determine the proper syntax for object properties and methods.

• You can gain quick access to the help topic for the object, property, or method selected in the Object Browser's lists.

• You gain the advantage of Access 97's Auto List Members feature when writing your Automation code.

• You can use the shorthand Dim objName As New ServerName.ObjectType statement to instantiate (create an instance of) the server object with early binding.

• You can use the intrinsic constants predefined for the object as argument values. This is especially important for servers, such as Excel, that have many intrinsic constants.

To create a reference to the Microsoft Excel 8.0 object library and explore Excel objects with the Access 97 Object Browser, follow these steps:

Registry Entries for Automation Servers You can specify any object listed in the Registry that is identified by a ServerName.ObjectType[.Version] entry at the HKEY_CLASSES_ROOT level of the Registry, as shown in Figure 31.10. The optional .Version suffix, shown in the line below the entry for Excel.Sheet, indicates the version number of ServerName. If you have two versions of the same Automation server, you can use the .Version suffix to specify one of the two.

In addition to the primary Registry entries for Excel 97 objects that you can open, additional data for creating or opening programmable objects appear in the HKEY_ LOCAL_MACHINESoftwareCLSID section of the Registry. CLSID is the abbreviation for ClassID, a globally unique, 32-character (plus hyphens) identifier for each object exposed by any COM object. The additional Registry entries for the Excel.Sheet.5 object appears in Figure 31.11. These entries provide information required to launch Excel 97 in Automation mode when you create an Excel.Sheet object.

Opening and Manipulating an Existing Excel Worksheet Object Learning to write VBA Automation code is a much quicker process if you use the Debug window to try Automation instructions before you begin writing complex Automation code in modules.

To open the Worksheet object of the Customers.xls Workbook object, follow these steps:

									Private xlaCust As Object 'Application object
Private xlwCust As Object 'Workbook object
Private xlsCust As Object 'Sheet object

									Set xlwCust = GetObject(CurDir & "customers.xls")

									Set xlsCust = xlwCust.ActiveSheet
								

Using Named Ranges of Cells If you created the named range—TestRange—in Customers.xls, you can return the values of the cells in the range by referring to the Range object of the Worksheet object. Figure 31.13 shows typical expressions that operate on the Range object of a Worksheet object and the Names collection of a Workbook object.

To specify a cell within a named Range object, use the following general syntax:

wksName.Range(strRangeName).Cells(intRow, intCol)

Because named ranges are global for all worksheets in an Excel workbook, the Names collection is a member of the Workbook object.

You refer to the Ranges collection of the Workbook object with statements such as

strRangeName = xlwName.Names(intIndex).Name
strRangeValue = xlwName.Names(intIndex).Value

Unlike Access object collections, which begin with an index value of 0, the first member of a VBA collection has an index value of 1.

Explicitly Closing a Workbook and the Application Object Just as a Workbook object is the value of the Parent property of a Worksheet object, the Application object is the Parent property of a Workbook object. The value of Excel 97's Application object is Microsoft Excel. Figure 31.14 illustrates expressions that work their way upward in Excel's object hierarchy, from Worksheet to Application objects. You can shortcut the process by using xlsCust.Parent.Parent to point to the Application object from a Worksheet object.

You can use the Close method to close an Excel Workbook object explicitly (you can't close a Worksheet object). You can use the Quit method of the Application object to exit the application. It's a good practice to exit the Application server application to conserve scarce system resources if you have multiple references to the Application server object. (Access 2.0 required brackets around Close and Quit because of conflicts with Access Basic reserved words.) For example, the following statements close the Workbook object and then exit the OA server application, freeing the memory reserved by the server:

							Set xlaCust = xlsCust.Parent.Parent
xlsCust.Parent.Close(False)
xlaCust.Quit

The False argument of the Close method closes the Workbook without displaying the message box that asks if you want to save changes to the Workbook. When using the Quit method to exit the application, you receive that message regardless of whether you modified the worksheet. To avoid this message when reading Excel 95 worksheets, close the worksheet before applying the Quit method. You must create an Application object because you cannot execute the xlsCust.Parent.Parent.Quit statement after closing the xlsCust object. (You receive an Object has no value error message if you try.)

Creating a New Excel Worksheet with Access VBA Code

You can emulate the Analyze It with MS Excel feature of Access 95 with Access VBA OLE Automation code. The CreateCust function of Listing 31.1 creates a new Workbook object, Cust.xls, and copies the data from the Customers table to a Worksheet named Customers. One of the primary incentives for writing your own version of the Analyze It with MS Excel feature is the ability to format the Worksheet object the way that you want. You also can add custom column headers. (The code to add column headers is not included in the CreateCust function.) When you execute the function from the Debug window, you receive a progress report from the Debug.Print statements in the code.

Code Listing 31.1. Declarations and Code to Create a New Excel Workbook

							Option Compare Database
Option Explicit

Private xlaCust As Object 'Application
Private xlwCust As Object 'Workbook
Private xlsCust As Object 'Worksheet

Function CreateCust() As Integer
'Purpose: Create new Excel 95 worksheet from Customers table

   'Declare local variables (Object variables are module-level)
   Dim dbNWind As Database     'Current database
   Dim rstCust As Recordset    'Table Recordset over Customers
   Dim intRow As Integer       'Row counter
   Dim intCol As Integer       'Column counter

   'Assign DAO pointers
   Set dbNWind = CurrentDb()
   Set rstCust = dbNWind.OpenRecordset("Customers", dbOpenTable)

   DoCmd.Hourglass True

   'Create a new Excel Worksheet object
   Set xlwCust = CreateObject("Excel.Sheet.8")
   Set xlsCust = xlwCust.ActiveSheet
   Debug.Print "Worksheet created"

   'Give the new worksheet a name
   xlsCust.Name = "Customers"

   'Get the Application object for the Quit method
   Set xlaCust = xlsCust.Parent.Parent

   intRow = 1
   intCol = 1

   rstCust.MoveFirst   'Go to the first record (safety)
   Do Until rstCust.EOF
      'Loop through each record
      For intCol = 1 To rstCust.Fields.Count
         'Loop through each field
         If Not IsNull(rstCust.Fields(intCol - 1)) Then
            xlsCust.Cells(intRow, intCol).Value = _
            CStr(rstCust.Fields(intCol - 1))
         End If
							Next intCol
      Debug.Print "Record "& intRow
      rstCust.MoveNext
      intRow = intRow + 1
   Loop

   Debug.Print "Formatting columns"
   For intCol = 1 To xlsCust. Columns.Count
      'Format each column of the worksheet
      xlsCust.Columns(intCol).Font.Size = 8
      xlsCust.Columns(intCol).AutoFit
      If intCol = 8 Then
         'Align numeric and alphanumeric postal codes left
         xlsCust.Columns(intCol).HorizontalAlignment = xlLeft
      End If
							Next intCol

   DoCmd.Hourglass False

   Debug.Print "Saving Workbook and exiting"
   xlwCust.SaveAs (CurDir & "Cust.xls")
   xlaCust.Quit
   Set xlsCust = Nothing
							Set xlwCust = Nothing
							Set xlaCust = Nothing
End Function

Figure 31.15 shows the Private Object variable declarations in the Declarations section of the module, followed by the first few lines of the preceding code and entries in the Debug window resulting from executing the CreateCust function.

You must coerce to String the data type of the Variant values returned by rstCust. Fields(intCol -1) with the CStr function. If you omit the CStr function, Excel 97 displays #N/A# instead of the proper value. If the Recordset contains field data types other than Text, use the appropriate CType function to determine the data type for the column.

The xlLeft constant, assigned to the value of the HorizontalAlignment property of the eighth column, is an Excel intrinsic constant defined when you create a reference to the Microsoft Excel 8.0 Object library. Selecting Constants in the Classes list of the Object Browser with the Excel library active displays the xlConst constants. Figure 31.16 shows the numeric value of xlLeft, one of the constant values that is valid for the HorizontalAlignment property.

Run the function shown in the preceding listing by typing ? CreateCust in the Debug window. Figure 31.17 shows the Cust.xls workbook with the Customer worksheet created by the CreateCust() function opened in Excel 97. Unless you specify a path, Excel saves Cust.xls in the default My Documents folder.

Sending Data to Microsoft Word 97 with Automation

Microsoft Word 97 replaces the venerable Word Basic macro language with VBA 5.0. Word 95 exposed the Word.Basic object, which gave VBA programmers access to about 800 Word Basic commands, but Word 95 was an Automation server only. Now Word 97 is a full-fledged Automation server and client with programming capabilities similar to Excel 97. You can create references to type libraries and then use Word VBA to program other application's objects, such as Access 97's Application object or the DAO.

Using Word 97's Online VBA Help File. Word 97's object model is even more complex than the object model of Excel 97, so you need the Word VBA help file to guide you when using Word as an Automation server. To display the object model for the Word Application and Document objects, follow these steps:

For quick access to help with Word objects and the syntax for properties and methods, create a reference to the Microsoft Word 8.0 Object Library (see Figure 31.22). When you select Word in the Library/Class drop-down list of the Object Browser, Word 97's objects appear in the Classes list. Figure 31.23 shows the properties and methods for the Bookmark object in the Members of ′Bookmark′ list.

Modifying a Word Document with VBA Code. The generic code for opening an existing Word 97 Document with VBA and saving any changes made to the file is as follows:

							Private wdaName
							As Object 'Word Application object
Private wddName
							As Object 'Word Document object

Set wddName = GetObject("d:pathfilename. doc")
Set wdaName = wddName. Application

'... Code to manipulate document

wdaName.Save
wdaName.Quit
Set wddName
							= Nothing
							Set wdaName
							= Nothing
						

If the document includes bookmarks, you can select and replace the bookmarked text with the following code:

wddName.Bookmarks("BookmarkName").Select
wdaName.Selection = "Replacement text"

One of the most effective methods of using Access 97 to create custom Word 97 documents is to define a set of named bookmarks and replace the bookmarked text with text from an Access Recordset object. In this case, a Word Bookmark object corresponds to the Cells(x, y) property of an Excel Worksheet object, where the x, y arguments specify a single cell. Figure 31.24 shows code in the Debug Window for reading and writing book-marked text in the Word file for the manuscript of this chapter. Selection is a property of the Application object, not the Document object. Only one Selection can be active in an instance of Word, no matter how many Documents are open.

To create and save a new Word document, use the following generic code:

							Private wdaName
							As Object 'Word Application object
Private wddName
							As Object 'Word Document object

Set wdaName = CreateObject("Word. Application")
wdaName.Documents.Add 'Empty document
Set wddName = wdaName. ActiveDocument

'... Code to manipulate document

wddName.SaveAs FileName:="filename.doc"
wdaName.Quit
Set wddName
							= Nothing
							Set wdaName
							= Nothing
						

It isn't obvious how to add text to an empty document you create with the Documents.Add method. When you add an empty document, it contains a single character (a paragraph marker), which represents the Content property of the Document. The following code creates a Range object representing the entire document and then applies the InsertBefore method to add text to the empty document:

							Private wdrName
							As Object 'Word Range object

Set wdrName = wddName. Content 'Range = 1 character
wdrName.InsertBefore "Text to insert"

Figure 31.25 is an example of using the InsertBefore method in the Debug window for adding text to a newly created document. The Select method of the Range object selects the entire Range. With the selection active, you can apply the Add method to the Bookmarks collection to add a persistent Bookmark object to contain the selection.

Programming a Word Document in an Access Object Frame. Most people who alter word processing documents want to view the modifications before final acceptance. Thus, the majority of Access applications that use Automation with Word 97 as the server are likely to involve Word documents contained in bound or unbound object frames. Chapter 21, "Using Access with Microsoft Word and Mail Merge," explains how to add a bound object frame containing an embedded Word document.

◂◂ See Embedding or Linking Word Documents in Access Tables.

The following generic code, with a reference to the Microsoft Word 8.0 Object Library, lets you apply Word 97 methods to the embedded document contained in a bound or unbound object frame:

							Dim wdaName
							As Word.Application
Dim wddName
							As Word.Document

[Forms!FormName!]ControlName.Action = acOLEActivate
Set wdaName = [Forms!FormName!]ControlName.Object.Application
Set wddName = wdaName. ActiveDocument

'...Code to manipulate document

[Forms!FormName!]ControlName.Action = acOLEClose
Set wddName
							= Nothing
							Set wdaName
							= Nothing
						

The ControlName.Action = acOLEActivate statement is required if the embedded object has not been activated when you execute the preceding code example, either in the Debug window or a VBA procedure. An embedded object in a frame must be activated before you can refer to its Object.Application.PropertyName property. Linked objects in object frames don't support Automation. You receive a This object does not support OLE Automation error message when you execute Set wdaName = uofName.Object. Application.WordBasic for a linked file.

Figure 31.26 shows an example of activating an embedded Word 97 object and then reading the value of bookmarked text by using the Selection object. To execute the code shown in Figure 31.26, you must first insert an unbound Word 97 object created from a file with bookmarks that are named "Excel8Head" and "Word8Head" in a form named frmWord. You must also create a reference to the Microsoft Word 8.0 object library and declare the object variables wdaUA97 and wddUA97 in the Declarations section of a module.

By using Automation and code similar to that shown in Figure 31.26, you can create a document that consists of nothing but empty bookmarks. Then you can fill the bookmarks with text contained in Text or Memo fields of tables or from the Value property of control objects on forms. Using Automation provides a much more flexible method of creating specialized documents than using Access 95's Merge It feature.