1. Open Excel
Before we can go on about stopping Excel, we’ll quickly look at how to start Excel. If this is nothing new you may move on to the next page. However, you need to be aware of one line that may not appear in your code: note where the Caption property of the Excel application is set. It is set to a unique identifier by creating a GUID string.
Public Function OpenExcel(ByVal Interact As Boolean) As _
Microsoft.Office.Interop.Excel.ApplicationClass
Dim App As Microsoft.Office.Interop.Excel.ApplicationClass
App = New Microsoft.Office.Interop.Excel.ApplicationClass
App.DisplayAlerts = Interact
App.Interactive = Interact
App.Visible = Interact
' uniquely mark this instance of Excel
App.Caption = System.Guid.NewGuid.ToString.ToUpper
Return App
End Function
The above code listing includes everything that is required to make an instance of the Excel Application in VB.NET.
If you need to instantiate a particular version of Excel, see “Achieving Backward Compatibility with .NET Interop: Excel as Case Study” first.
Depending on your platform, there may be additional steps required to get Excel running. One of the most common issues that will come up in an ASP.NET environment is listed below, along with the solution steps.
| Common Error:
The single most common issue encountered when instantiating an automation object on the server in an ASP.NET environment looks something like this: BEGIN
ERROR on Excel open [System.UnauthorizedAccessException]: Access is denied.
... DONE!
System Application Event Log reports:
Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
User: MACHINENAME\ASPNET
Computer: MACHINENAME
Description:
The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID {00024500-0000-0000-C000-000000000046} to the user MACHINENAME\ASPNET SID (S-1-5-21-##########-##########-#########-####). This security permission can be modified using the Component Services administrative tool. |
Solution:
- Open “COM Config.” from mmc console (Control Panel | Administrative Tools | Component Services). For Windows 2000, run comcnfg from a command line.
- Find "Microsoft Excel Application" {00024500-0000-0000-C000-000000000046}
- Right-click and select Properties | Security tab
- Under "Launch and Activation Permissions", select "Customize" and click "Edit" button
- Add ASPNET Machine Account and allow "Local Launch" and "Local Activation"
|
2. The MAIN Sub
The following code listing shows what a simple Main() routine would look like for a console application that (a) creates an Excel automation object, (b) calls a couple procedures on it, and then (c) closes. If you are writing in ASP.NET, you can do something similar in the Page_Load method [please understand that this goes into your server code and thus will launch Excel on the server]. Note that the implementation of the CloseExcel() method will comprise the remainder of this article.
Sub Main()
Dim myExcel As Microsoft.Office.Interop.Excel.ApplicationClass
Dim sh As Microsoft.Office.Interop.Excel.Worksheet
myExcel = OpenExcel(True)
sh = myExcel.Workbooks.Add().ActiveSheet
sh.Range("A1:A1").Value = "Hello World"
Console.WriteLine("... hit ENTER to close:")
Console.ReadLine()
CloseExcel(myExcel, True)
Console.WriteLine("... hit ENTER to exit:")
Console.ReadLine()
End Sub