< Day Day Up > 

You can write an Excel macro to control the most important component of Microsoft Word, its automation server. In such a macro, Excel would act as the client application and Word would act as the server application. You can also write a VB application to control Excel. The process of one application controlling another is referred to as Automation, formerly OLE Automation.

Automation allows you to develop complex macros that have the ability to control objects from a variety of applications. This is an incredibly powerful tool when creating application files. Automation allows the user to interact with multiple applications, such as Access, and not even realize the interaction is occurring. Automation allows a single seamless interface for the end user.

As you are aware, you can use the Insert menu to add a variety of Objects into your workbook. You can alternatively use Excel VBA to create the same types of objects. When you create the object, you'll have full access to the object's properties and methods available from its host application. This automation method is preferable for developers, rather than using the Object command from the Insert menu. When the object is embedded, the user must know how to use the Automation object's application. However, when you use VBA to manipulate the object, you can program the object so that the user can manipulate it by clicking the object.

Before you work with the external object, you must create an instance of the object. This can be done using one of the two types of binding, early or late. Binding refers to matching the function calls to the actual code that implements the function.

Late Binding

In late binding, the matching process between the object variable and the object takes place when the application is run. The result is slower performance compared to early binding, in which the binding takes place when the application is compiled.

With late binding, you need to declare general object variables because the Object Library belonging to the foreign application is not activated. It is more challenging to program the foreign objects because the properties, methods, and events won't automatically appear while programming your procedure in regard to the foreign object. However, late binding lets you create an Application object regardless of the version installed on the user's system. This is the preferred method of binding when the file will be distributed to users that might have different versions of the software. Therefore, your procedure would open Microsoft Word regardless of the version installed.

You use the CreateObject function to create the object or the GetObject function to create the instance of the application. The object is then declared as a generic Object type, and its object reference is resolved at run time. The following procedure displays how to use late binding with the Word application:

Sub UsingLateBinding()
Dim oApp As Object
Dim oDoc As Object
On Error Resume Next
Set oApp = GetObject(, "Word.Application")

If oApp Is Nothing Then
Set oApp = CreateObject("Word.Application")
End If
On Error GoTo 0
If oApp Is Nothing Then
MsgBox "The application is not available!", vbExclamation
End If
With oApp
.Visible = True
Set oDoc = _
.Documents.Open("C:\GSC\Employee Info\Health Benefits.doc")
oDoc.Close True
End With
Set oDoc = Nothing
Set oApp = Nothing
End Sub

This additional sample procedure also uses late binding but shows you how to create an entry in the Outlook Calendar.

Sub MakeOutlookAppointmentLateBinding()
Dim olApp As Object
Dim olAppointment As Object
Const olAppointmentItem = 1

Set olApp = CreateObject("Outlook.Application")
Set olAppointment = olApp.CreateItem(olAppointmentItem)

With olAppointment
.Subject = "Spring Sales Initiative Meeting"
.Location = "Radisson: Meeting Room A"
.Start = DateSerial(2005, 3, 7) + TimeSerial(9, 30, 0)
.End = DateSerial(2005, 3, 7) + TimeSerial(11, 30, 0)
.ReminderPlaySound = True
End With
Set olApp = Nothing
End Sub

The basic technique in programming another application is to create an object variable referring to that application. The object variable in this case is olApp. You then use the olApp variable to refer to objects in the external application's object model. In this example, the CreateItem method of Outlook's Application object is used to create a reference to a new AppointmentItem object.

Because Outlook's constants are not available when late binding is used, you must define your own constants, such as olAppointmentItem in this example, or substitute the value of the constant as the parameter value. The properties and methods of the Appointment object in the With…End With structure modify the new object that was created.

When declaring the olApp and olAppointment as generic Object types, late binding is forced on the VBA procedure. All the links to Outlook can't be established until the procedure executes the CreateObject function. The CreateObject input argument defines the application name and class of object to be created. Outlook is the name of the application and Application is the class. Many applications allow you to create objects at different levels in the object model. For example, Excel allows you to create WorkSheet or Chart objects from other applications, using Excel.WorkSheet or Excel.Chart as the input parameter of the CreateObject function.


Save Your Memory
It's good programming practice to close the external application when you are finished with it and set the object variable to Nothing. This releases the memory used by the link and the application.

If you execute this macro in Excel, it appears as though nothing has happened. However, if you open Outlook and navigate to March 7, 2005, you'll see the appointment has been added to the Calendar. Figure 21-2, displays the appointment created in the Calendar on March 7, 2005.

click to expand
Figure 21-2: The Outlook Calendar with the newly created Spring Sales Initiative Meeting on the morning of March 7, 2005.

Early Binding

In early binding, the binding between the object variable and the object occurs when the application is compiled. The end result is better performance compared to late binding. You can add an Object Library using the References command from the Tools menu in the Visual Basic Editor. When the reference has been added to the VBA Project, you are able to declare specific object variables. This is an easier method of programming foreign objects because the Visual Basic Editor will display the same programming help regarding the foreign object that it would display for the object belonging to the application you are working from.

Before programming the procedure, add the appropriate reference. Follow these steps to add a reference to Microsoft Outlook.

  1. Open the Visual Basic Editor.

  2. Select the References option from the Tools menu.

  3. Scroll through the available references until you find the Microsoft Outlook Object Library.

  4. Check the box beside the reference.

  5. Click OK to close the References dialog box.

Figure 21-3, displays the References dialog box with the Outlook Object Library selected.

click to expand
Figure 21-3: The References dialog box displays all available references. To activate a library file, select the reference and click OK.

Now that the reference has been added to the Visual Basic Editor, you'll have the available assistance with objects created using this library file. VBA will search through the type libraries, in the order shown from top down, to find the references to object types. If the same object type is present in more than one library, VBA will use the first one found. You can select a library and click the Priority buttons to move it up and down the list to change the order in which libraries are searched. There's no need to depend on priority; you can reference the object with the application object. For example, instead of using AddressList, use Outlook.AddressList.

Review the following example to see how early binding is used. The procedure lists all the names in the Outlook Contacts folder, placing them in column A of the active worksheet. Make sure that you have added a reference to the Outlook Object Library before executing this procedure.

Sub DisplayOutlookContactNamesEarlyBinding()
Dim olApp As Outlook.Application
Dim olNameSpace As Outlook.Namespace
Dim olAddresslist As AddressList
Dim olEntry As AddressEntry
Dim i As Long

Set olApp = New Outlook.Application
Set olNameSpace = olApp.GetNamespace("Mapi")
Set olAddresslist = olNameSpace.AddressLists("Contacts")
For Each olEntry In olAddresslist.AddressEntries
i = i + 1
Cells(i, 1).Value = olEntry.Name

Set olApp = Nothing
End Sub

You probably noticed that while this code was executed, you received a warning similar to the one in Figure 21-4. The security against viruses has increased dramatically since the release of Office XP. The trend has continued with the release of Office 2003. Any attempt by programs trying to access e-mail addresses will execute a warning message. You'll also receive a warning each time an e-mail message is sent using VBA code.

click to expand
Figure 21-4: Warning message you get when executing code that interrogates your Contacts folder in Outlook.

start sidebar
Inside Out
Early Binding vs. Late Binding

Both early binding and late binding have advantages and disadvantages. Late binding is slower than early binding because the binding occurs during run time. When you complete the work in design time, the code will run faster. When you are writing code using late binding, you lose some conveniences. Specifically, you aren't able to use the IntelliSense that displays the available properties and methods available for the objects that you're using. The Object Browser also won't list the objects for the application objects that you're programming. Another disadvantage is that the convenience of built-in constants isn't available when using late binding.

It would appear that a strong case has been made to use early binding; however, there's one strong advantage to late binding that shouldn't be overlooked. When late binding is used, it doesn't matter which version of the application is installed. The application object will search for the version installed on your system and choose the correct object mode to reference. In early binding, you'll choose the application's Object Library from the References list. The References list will contain only installed object libraries. If you choose to share your procedure with others, the list will be coded specifically to the version of the software that's installed on your system.

The moral of the story is to write your code initially using early binding, but before distributing the file you should modify the code to use late binding. It would be a good idea to modify your personal projects to late binding as well, so a problem won't arise when your software is upgraded in the future.

end sidebar

In the DisplayOutlookContactNamesEarlyBinding procedure, you declared olApp to be an Outlook.Application type. The other Dim statements also declare object variables of the type that you required to complete the objectives. If the same object name is used in more than one Object Library, you can precede the object name by the name of the application, rather than depend on the priority of the type library. You'll notice this technique was used with the Outlook NameSpace property. The New keyword is used when assigning a reference to Outlook.Application to olApp to create a new instance of Outlook.

Because the variable types were declared, the VBA procedure is forced to use early binding. You could use the CreateObject function to create the olApp object variable, instead of the New keyword, without affecting the early binding. But it's more efficient to use the New keyword.

 < Day Day Up > 

Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: