Programming Excel with VBA and .NET

Use Excel as a Component in .NET

Another way for Excel to interact with the .NET world is to program with Excel objects directly in Visual Basic .NET. In this case, Excel becomes a component for use in a .NET application—the reverse of the case just shown.

Using Excel as a component in a .NET application is handy when you want to present application output using the Excel interface—as a spreadsheet or chart, for instance.

To create a .NET application that uses Excel as a component:

1. Create a new Windows application project in Visual Studio .NET.

2. From the Project menu, choose Add Reference. Visual Studio .NET displays the Add Reference dialog box. Click the COM tab. Visual Studio .NET displays the contents of your system’s global assembly cache, as shown in Figure 25-10.

3. Select the Microsoft Excel 11.0 Object Library and click Select, then OK to add the reference to your project. Visual Studio .NET automatically references the PIA for the Excel object library if it is installed on your system.

4. If the PIA is not installed, Visual Studio .NET creates a new interop assembly and adds it to your project (this is not what you want—the PIA is much more reliable). To make sure you are using the PIA, check the Name and Path properties of the Excel reference. They should appear as shown in Figure 25-11.

5. In code, create an instance of the Excel Application object and use that object’s member to perform tasks in Excel.

Figure 25-10. Adding a reference to the Microsoft Excel object library

Figure 25-11. Check ...