Page 481 - Microsoft Office Excel 2003 Programming Inside Out

Excel and Other Office Applications

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.

Interacting with Other Office Applications

Using VBA to access other Microsoft Office applications is similar to using code to work with
objects within the code’s host application. To begin, you create an object variable that points
to the Application object representing the Office application that contains the objects you
want to work with. It’s preferable to use an early bound object variable by using the New
keyword. Alternatively, you can choose to use the CreateObject function or the GetObject Chapter 21
function to create an object variable.
When VBA code manipulates objects within the same application, the reference to the
Application object is implicit. However, when you are automating another application, the
reference to the Application object must be explicit.
Review the following examples to see the difference between how the Application object is
referenced, implicitly and explicitly. The first procedure demonstrates how to create a new
Excel workbook and how to reference the Application object implicitly. The second proce
dure demonstrates how to refer to the Microsoft Word application explicitly and to create a
new document. For the second procedure to be executed successfully, the reference to the
Microsoft Word Object Library must first be added to the Excel application using the Tools,
References command in the Visual Basic Editor.

Sub CreateNewWorkbookFromExcel()
Dim xlNew As Excel.Workbook
Set xlNew = Workbooks.Add
ActiveCell.Value = "Created " & Date
End Sub

Sub CreateWordDocumentFromExcel()
Dim wdApp As Word.Application
Dim docNew As Word.Document

455
Part 6: Excel and the Outside World: Collaborating Made Easy