VS2005 vb.net DLL - interop EXCEL

[Jon Delano]

Hello All

I am working a on web application created in Visual Studio 2005 VB.
It has to do some work with Excel files, so I created a DLL in VS2005.

My development machine is XP SP3.
When my web app runs it opens a few excel files and fills certain locations
and then the excel spreadsheet is calculated.
(there are many calculations)

To ensure I was closing only the files I had opened during this process I
use code like this:

Dim ClassName As String = "XLMain"
Dim XLNumbers As Int16
Dim WindowHandle As Int32
Dim ReturnVal As Int32
Const WM_QUIT = &H12

For XLNumbers = 12 To 17
WindowHandle = FindWindow(ClassName, "Microsoft Excel - ROI_" &
Request.QueryString("ROIHeaderID") & "_" & CStr(XLNumbers) & ".xls")
If WindowHandle Then
ReturnVal = PostMessage(WindowHandle, WM_QUIT, 0, 0)
End If
Next

WindowHandle = FindWindow(ClassName, "Microsoft Excel - ROI_" &
Request.QueryString("ROIHeaderID") & "_ALL.xls")
If WindowHandle Then
ReturnVal = PostMessage(WindowHandle, WM_QUIT, 0, 0)
End If

This works perfectly on my development machine.

The deployment machine is Windows Server 2003.
None of the excel files close using the code above. The only thing that
works there is using something like this:

' check for any EXCEL processes that stuck
Dim objWMIcimv2 = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\.\root\cim v2")
'Connect to CIMV2 Namespace

Dim objList = objWMIcimv2.ExecQuery _
("select * from win32_process where name='EXCEL.EXE'") 'Find the
process to terminate

Dim objProcess As Object
Dim intRslt As Int16
For Each objProcess In objList
intRslt = objProcess.Terminate 'Terminates a process and all of
its threads.
Next

Now while this removes the excel files in memory, it removes ALL of them.
As this is meant to work with a web site and more then 1 (but maybe 5 total
concurrent users) person may be using the site, this isn't a viable
solution.

The files are opened like this:
(there is a reference to excel in the DLL project)

Dim xlAppl As Excel.Application
Dim xlSheet As Excel.Worksheet

xlAppl = New Excel.Application
xlAppl.Workbooks.Open(strFileName)
xlAppl.Workbooks(1).Activate()

strSheetName = "Input Sheet"
xlSheet = xlAppl.Sheets(strSheetName)

*** code fills in some values

xlAppl.Workbooks(1).Save()
xlAppl.Quit()

xlAppl = Nothing

Some files are left open using this code, which lead me to find something
that allows me to clean up after this.

Is it possible to target process that have certain file names open and shut
them down, in windows server 2003?
Maybe it would just be better for the web app to fill a queue of excel files
that need processing .. and have a desktop application running under a
username?
(though they really require feed back of the changes the new calculations
made .. which are saved to a database)

Any ideas about getting excel to actually close when its asked to?

Thanks