Programming Excel with VBA and .NET

Program Other Office Applications

You can program any of the Office applications from Excel by following these steps:

1. In the Visual Basic Editor, choose Tools → References and select the Office application from the list of Available References (Figure 23-4) and click OK to close the dialog.

2. Declare an object variable using one of the Office application’s objects.

3. Create an instance of that object.

4. Use the properties and methods of the object.

5. Close the object and set the object variable to Nothing when you are done.

Figure 23-4 shows establishing a reference to the Word object library. COM applications expose their object through type libraries (.tlb) or object libraries (.olb). Those two kinds of libraries are often used interchangeably and the words mean basically the same thing.

Figure 23-4. Referencing Word from Excel

Once you’ve created a reference to the application, you can view the objects the application provides in the Object Browser (Figure 23-5).

The following code demonstrates the programming steps for working with the Word application:

' Worksheet class ' Step 2: Declare object. Dim WithEvents m_wd As Word.Application   Sub StartWord( ) ' Step 3: Create a new instance of the application. Set m_wd = New Word.Application ' Step 4: Use properties and methods (makes Word visible). m_wd.Visible = True End Sub   Sub CloseWord( ) ' Step 5: Close Word. If Not (m_wd Is Nothing) Then ...