Excel 2002 won't die, using Automation

[l33t_c0w]

Slightly complex scenario maybe, so please bear with me as I try to explain it. The actual question will be at the end, but needs an introduction.

This is a small program I'm writing to query an SQL server, get a bunch of information, and then write to an Excel sheet and format the data a bit (stuff like certain rows being different colors, some cells in bold, a few page breaks).

The spreadsheet I'm writing to is in a pre-made blank workbook sitting in the same directory as the program itself, that has macros in it. Initially I just wanted to write all the information, do an objExcel.visible = true, and then let the user play with it. The thing I noticed though was that there was a major slowdown with the macros contained in the spreadsheet. Things take a few seconds each now where before they took a small fraction of a second. It really isn't acceptable.

So, then I decided to instead try to just save the spreadsheet and close excel to get it "unhooked" from the visual basic program. The problem is, the process won't die. I visited http://support.microsoft.com/default...b;en-us;317109 and tried applying that to my program. Now, when I leave the program running and create additional spreadsheets, I can watch processes spawn and then die, but the very first process always stays. It causes Excel to hang / crash when I try to open a file I'd created with the program during that session. If I kill it there seem to be no ill effects and then everything runs well, but that isn't an option unless I can be sure that I'm killing the right process. I figure there's either a glitch or I'm doing something dumb. Since I'm pretty new to programming in general, I wouldn't rule out either.

Has anyone dealt with persisting evil excel processes and automation before?

edit: forgot the code.

Private Sub cmdBuildSheet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdBuildSheet.Click
 
        Dim ExcelArr As Object(,)
        Dim colsCount As Integer
        Dim rowsCount As Integer
        Dim objExcel As Excel.Application
        Dim objWorkbooks As Excel.Workbooks
        Dim objWorkbook As Excel.Workbook
        Dim objSheet As Excel.Worksheet
        Dim objRange As Excel.Range
 
        Try
 
            radNoWorkOrders.Enabled = False
            radWorkOrders.Enabled = False
 
            objExcel = DirectCast(CreateObject("Excel.Application"), Excel.Application)
            objWorkbooks = objExcel.Workbooks
            objWorkbook = objExcel.Workbooks.Open(Application.StartupPath & "\NOTOUCH.xls")
            objSheet = DirectCast(objWorkbook.ActiveSheet, Excel.Worksheet)
 
            _arrIndex = 1 
 
            If radWorkOrders.Checked = True Then 
 
                ReDim _PartsArr(11, 39)
 
                _PartsArr(0, 1) = "Parent"
                _PartsArr(1, 1) = cboPart.Text
                _PartsArr(3, 1) = txtDescription.Text
 
                GetPartsWO(cboPart.Text, 1)
 
                objRange = objSheet.Range("A:D")
                objRange.NumberFormat = "@" 
                objRange = objSheet.Range("E:G")
                objRange.NumberFormat = "0" 
                objRange = objSheet.Range("H:H")
                objRange.NumberFormat = "@" 
                objRange = objSheet.Range("I:I")
                objRange.NumberFormat = "0" 
                objRange = objSheet.Range("J:K")
                objRange.NumberFormat = "@" 
 
                Dim iterateArr As Integer = _PartsArr.GetUpperBound(1)
                Do Until Not _PartsArr(1, iterateArr) = "" Or Not _PartsArr(9, iteratearr) = ""
                    iterateArr = iterateArr - 1
                Loop
                rowsCount = iterateArr
 
            ElseIf radNoWorkOrders.Checked = True Then  
                ReDim _PartsArr(10, 39)
 
                _PartsArr(0, 1) = "Parent"
                _PartsArr(1, 1) = cboPart.Text 
                _PartsArr(3, 1) = txtDescription.Text
 
                GetParts(cboPart.Text, 1)
 
                objRange = objSheet.Range("A:D")
                objRange.NumberFormat = "@" 
                objRange = objSheet.Range("E:E")
                objRange.NumberFormat = "0" 
                objRange = objSheet.Range("F:F")
                objRange.NumberFormat = "0.00" 
                objRange = objSheet.Range("G:H")
                objRange.NumberFormat = "0" 
                objRange = objSheet.Range("I:I")
                objRange.NumberFormat = "@" 
                objRange = objSheet.Range("J:J")
                objRange.NumberFormat = "0" 
 
                Dim iterateArr As Integer = _PartsArr.GetUpperBound(1)
                Do Until Not _PartsArr(1, iterateArr) = ""
                    iterateArr = iterateArr - 1
                Loop
                rowsCount = iterateArr
 
            End If 
 
            colsCount = _PartsArr.GetUpperBound(0)
 
            ReDim ExcelArr(rowsCount, colsCount)
            For iterate1 As Integer = 0 To rowsCount
                For iterate2 As Integer = 0 To colsCount - 1 
                    ExcelArr(iterate1, iterate2) = _PartsArr(iterate2, iterate1)
                Next
            Next
 
            Dim objPageSetup As Excel.PageSetup = objSheet.PageSetup
            objPageSetup.TopMargin = 55 
            objPageSetup.HeaderMargin = 18
            objPageSetup.BottomMargin = 0
            objPageSetup.FooterMargin = 0
            objPageSetup.LeftMargin = 0
            objPageSetup.RightMargin = 0
            objPageSetup.PrintTitleRows = "A1:K1"
 
            objRange = objSheet.Range("A1:A1")
            objRange.Resize(rowsCount + 1, colsCount).Font.Size = 8
            objRange = objSheet.Range("A2:A2")
            objRange.Resize(rowsCount, colsCount).Borders.Value = 1
 
            objRange = objSheet.Range("A1:A1")
            objRange.Resize(rowsCount + 1, colsCount + 1).Value = ExcelArr
 
            For iterateColors As Integer = 1 To rowsCount + 1
                Select Case _PartsArr(colsCount, iterateColors - 1)
                    Case Is = "PU  " 
                        objRange = objSheet.Range("D" & iterateColors.ToString)
                        objRange.Interior.Color = RGB(255, 153, 204)
                    Case Is = "MA  " 
                        objRange = objSheet.Range("D" & iterateColors.ToString)
                        objRange.Interior.Color = RGB(255, 255, 0)
                    Case Is = "FA  "
                        objRange = objSheet.Range("D" & iterateColors.ToString)
                        objRange.Interior.Color = RGB(153, 204, 0)
                    Case Is = "CO  " 
                        objRange = objSheet.Range("D" & iterateColors.ToString)
                        objRange.Interior.Color = RGB(153, 204, 255)
                End Select
            Next
 
            Dim objPageBreaks As Excel.HPageBreaks = objSheet.HPageBreaks
            Dim pageFlag As Boolean = False 
            For iterateRows As Integer = 1 To rowsCount + 1
                If _PartsArr(0, iterateRows - 1) = "  1" Then
                    objRange = objSheet.Range("A" & iterateRows.ToString)
                    objRange.Resize(1, colsCount).Font.Bold = True
                    If pageFlag = True Then
                        objPageBreaks.Add(objSheet.Cells(iterateRows, 1))
                    Else
                        pageFlag = True
                    End If
                End If
            Next
 
            objRange = objSheet.Range("A1:A1")
            objRange.RowHeight = 25.5
 
            If radWorkOrders.Checked = True Then
                objRange = objSheet.Range("F1:G1")
                objRange.WrapText = True
                objRange = objSheet.Range("F1:G1")
                objRange.ColumnWidth = 5 
                objSheet.Cells.EntireColumn.AutoFit()
                objRange = objSheet.Range("H:H")
                objRange.ColumnWidth = 8
            Else
                objRange = objSheet.Range("G1:H1")
                objRange.WrapText = True
                objRange = objSheet.Range("G1:H1")
                objRange.ColumnWidth = 5
                objSheet.Cells.EntireColumn.AutoFit()
            End If
 
            objWorkbook.SaveAs(Application.StartupPath & "\" & _PartsArr(1, 1) & ".xls")
 
            NAR(objPageBreaks)
            NAR(objPageSetup)
            NAR(objRange)
            NAR(objSheet)
            objWorkbook.Close(False)
            NAR(objWorkbook)
            NAR(objWorkbooks)
            objExcel.Quit()
            NAR(objExcel)
 
            GC.Collect()
            GC.WaitForPendingFinalizers()
 
            MessageBox.Show("Completion!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
 
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace)
        Finally
            radWorkOrders.Enabled = True
            radNoWorkOrders.Enabled = True
        End Try
    End Sub
 
    Private Sub NAR(ByVal o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
        Finally
            o = Nothing
        End Try
    End Sub

[RobDog888]

Welcome to the Forums.

You can use this on the excel application object you have created to explicitly destroy your excel object. Usually this is from some issue in your code where yor referencing excel default objects and excel instanciated objects.

'oApp = Excel.Applicaiton
System.Runtime.InteropServices.Marshal.ReleaseComObject(oApp)

[l33t_c0w]

I think that's what I'm doing. It also interests me how the subsequent processes die like they're supposed to, and only the first sticks around.

Unless it's something more like, the first process is the application process and the others are "workbook processes" or something like that.

[RobDog888]

Ah, ok I see your code now. This issue may be that your passing the object ByVal and not the default ByRef. When passed as ByVal it passes a copy of the object that will not update its changes to the original passed in object. so you are not really releasing the object.

Private Sub NAR([b]ByVal[/b] o As Object)
 
'Should be
Private Sub NAR(ByRef o As Object)
'Or 
Private Sub NAR(o As Object)

[l33t_c0w]

Excel's still going after I changed that. Do you think I might need to explicitly declare and then release *every* excel property/method/etc that I use? I thought I was already kind of going a little overboard.

Thanks for the speedy response, by the way.

[RobDog888]

Yes, before you posted your code I didnt know you werent doing that.
You always need to explicitly close and destory your COM objects.

objWorkbooks = Nothing
objWorkbook.Close
objWorkbook = Nothing
objSheet = Nothing
objRange = Nothing
objPageBreaks = Nothing
 
objExcel.Quit
objExcel = Nothing

[l33t_c0w]

setting the code to nothing seems to have the same effect as using System.Runtime.InteropServices.Marshal.ReleaseComObject, which is to have the initial excel process stick around until the program is terminated, but the excel.exe processes from subsequenet clicking of the button terminate properly

[RobDog888]

The reason why Excel "hangs" is because it has an object variable still left instanciated. So always .Close, .Quit, and = Nothing is a way of insuring that the ReleaseComObject method weill succeed.

[RobDog888]

You should also remove the objWorkbooks object since your not using it anywhere in the procedure.

[l33t_c0w]

Then, I think the question might be "what Excel object am I leaving lying around instanced?" I think it's related to the Excel application, and not to the workbook or worksheet or anything, because the additional processes for additional workbooks and whatnot terminate properly.

I took out the workbooks object. Heheh. oops.

[RobDog888]

I dont see anything else that may be holding up the Excel process. Could you possibly have other Excel code somewhere else in your application?

[l33t_c0w]

Ahh, it wouldn't be that early-on directcast would it? actually, there are two of them. I don't know what to change the second one to though, since objworkbook.activesheet is an Object, and objworkbook.sheets(1) is also an Object

objExcel = DirectCast(CreateObject("Excel.Application"), Excel.Application)
            objWorkbook = objExcel.Workbooks.Open(Application.StartupPath & "\NOTOUCH.xls")
            objSheet = DirectCast(objWorkbook.ActiveSheet, Excel.Worksheet)

to

objExcel = New Excel.Application
            objWorkbook = objExcel.Workbooks.Open(Application.StartupPath & "\NOTOUCH.xls")
            objSheet = m00?

[RobDog888]

No, as DirectCast is only an explicit CType.

Using Excel properties like ActiveSheet and ActiveWorkbook could be causing an issue with the processing hanging but shouldnt be creating a second instance of Excel in the beginning.

Can you step through your code and have your taskmanager open to monitor when each Excel process is created?

[l33t_c0w]

I'm sorry. What I meant was that for a given session the program is run, that button that that sub belongs to may be clicked more than once, to generate more than one report. And only the process from the first click gets "stuck"

The processing hanging was my major problem. I don't mind there being multiple processes otherwise, though, if it was closing correctlythe first time there wouldn't be, of course.

What can I use instead of ActiveWorksheet?

[RobDog888]

Workbooks(1).Sheets(1)
'Or
Workbooks("Book1").Sheets("sheet1") or what ever the names are in your workbook.

What I usually do is create a class level excel application object. Then upon a button click I would just open or add a new workbook to it. this will improve performance since you are not creating new Excel applicaiton instances each time you press the button.
Then you will have a reference to only one excel applicaiton object variable. When you r appplication closes you also close and destroy your excel application variable object.

You will need to handle multple workbook instances in the workbooks collection upon closing but if your not saving each one then its not a problem.

[l33t_c0w]

the editor still complains that those are of type "object" -- would it be appropriate to use a directcast then?

also, there are no other bits of code related to excel in the program. If you're hit with any flashes of inspiration, I'll watch the thread. Otherwise, thanks very much for the help and I'll keep reading

[RobDog888]

Yes, if your using Option Strict On then you will need the DirectCast or CType.

[LostInJavaScriptLand]

I have the same problem. No matter what I do, Excel won't close.

I wrote an example that will keep Excel open...every time. Type the filename of an xls file, press the button, and Excel will stay open...even though all objects are dereferenced in the correct way.

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Core
Imports System.Windows.Forms
Imports System.Drawing
Imports System
 
Namespace test_excel
    Public Class MainForm
        Inherits System.Windows.Forms.Form
        Private button1 As System.Windows.Forms.Button
        Private textBox1 As System.Windows.Forms.TextBox
        
        Public Shared Sub Main
            Dim fMainForm As New MainForm
            fMainForm.ShowDialog()
        End Sub
        
        Public Sub New()
            MyBase.New
            '
            ' The Me.InitializeComponent call is required for Windows Forms designer support.
            '
            Me.InitializeComponent
        End Sub
        
        #Region " Windows Forms Designer generated code "
        ' This method is required for Windows Forms designer support.
        ' Do not change the method contents inside the source code editor. The Forms designer might
        ' not be able to load this method if it was changed manually.
        Private Sub InitializeComponent()
            Me.textBox1 = New System.Windows.Forms.TextBox
            Me.button1 = New System.Windows.Forms.Button
            Me.SuspendLayout
            '
            'textBox1
            '
            Me.textBox1.Location = New System.Drawing.Point(8, 8)
            Me.textBox1.Name = "textBox1"
            Me.textBox1.Size = New System.Drawing.Size(192, 20)
            Me.textBox1.TabIndex = 1
            Me.textBox1.Text = ""
            '
            'button1
            '
            Me.button1.Location = New System.Drawing.Point(208, 8)
            Me.button1.Name = "button1"
            Me.button1.TabIndex = 0
            Me.button1.Text = "Try It"
            AddHandler Me.button1.Click, AddressOf Me.Button1Click
            '
            'MainForm
            '
            Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
            Me.ClientSize = New System.Drawing.Size(292, 37)
            Me.Controls.Add(Me.textBox1)
            Me.Controls.Add(Me.button1)
            Me.Name = "MainForm"
            Me.Text = "MainForm"
            Me.ResumeLayout(false)
        End Sub
        #End Region
        
        Public Function Excel_Still_Runs(ByVal FileName As String)
            Dim xlsApp As New Excel.Application()
            Dim xlsWorkbooks As Excel.Workbooks
            Dim xlsWorkbook As Excel.Workbook
            
            ' Open the workbook
            xlsWorkbooks = xlsApp.Workbooks
            xlsWorkbook = xlsWorkBooks.Open(FileName)
            
            ' Close and release the workbook
            xlsWorkbook.Close(False)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkbook)
            xlsWorkbook = Nothing
            
            ' Release the workbooks collection
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkbooks)
            xlsWorkbooks = Nothing
            
            ' Issue the quit function...even though we know it won't work
            xlsApp.Quit()
            
            ' Release the application
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp)
            xlsApp = Nothing
        End Function
        
        Private Sub Button1Click(sender As System.Object, e As System.EventArgs)
            Excel_Still_Runs(Me.textBox1.Text)
        End Sub
    End Class
End Namespace

[RobDog888]

Are you creating 2 instances of your form? What is your startup object? What version of VB are you running? Also, you should set the objects = to nothing before releasing the com object.

Another point is that you have an unnecessary Imports of Office.Core when all you need is the Office.Interop.

[LostInJavaScriptLand]

No...one instance of the form. I use SharpDevelop for my IDE...which creates the shared main function to instantiate the form...which that shouldn't matter. I could just as well do the exact same thing with Excel using a console window.

The code is written for .NET 1.1, but I have also did the same code for .NET 1.0, and the same thing happens.

Now, If I set the objects to nothing before releasing the com object, how would it know what to release? Plus, Microsoft seems to agree with the way I'm doing it (look at the NAR() function for C#): http://support.microsoft.com/default.../317109/EN-US/

[RobDog888]

Did you remove the imports of the Office.Core ?

I disagree with that ms example as I have seen it before. They are passing the objects BrVal and trying to destroy and release the copy and not the actual reference as if they passed it ByRef instead.

For your issue may be a timing issue since you are creating/opening line after line. What if you create in one button click and in another you destroy/release?

[LostInJavaScriptLand]

Office.Core is in the code I'm actually working with. I had forgotten to remove it from the sample. But it doesn't matter if it's there or not...the error remains.

As for passing by reference, as you can tell in my example code...there is no passing by value, except for the filename, as I release the COM objects inline.

Plus, what is the point of automating a process if I have to click buttons in-between? Kinda defeats the purpose.

If you have example code that actually closes Excel properly, I would love to see it.

Sorry if I seem a bit agitated...but I have been dealing with this problem for the past year. The only option I found that somewhat works is to kill the process by executing taskkill using the shell command.

[RobDog888]

I understand the frustration as others have the same issues with killing office apps from .net.

The ByRef I was refering to was in the MS example.

The multiple button clicks was just for testing purposes to try to isolate any possible issues.

From what I understand, Office creates a wrapper for an office app and its this wrapper that retains the process until all references to office objects are released and destroyed.

I tried your exact code in a test project and added a Sleep line so I could see Excel appear in the task processes list and it ran correctly and destroyed excel correctly.

Do you have the PIAs installed?

Which version of VB are you using?

Is your Office updated with the lasted Office Update Online?

[billhuard]

If I kill it there seem to be no ill effects and then everything runs well, but that isn't an option unless I can be sure that I'm killing the right process.

Here is how you can kill the right process.

First, after you started the Excel process that you want to kill (the first one I think) call this function

Declare Function GetCurrentProcessId Lib "kernel32" () As Long

This function return the process ID of the Excel process just created.
Put the result in a variable

Once you have done everything you want to do and want to kill the god dam process

do

Function KillPId()
 
        Set ProcessList = GetObject("winmgmts://.").InstancesOf("win32_process")
 
    For Each Process In ProcessList
        If Process.ProcessID = variable Then ' variable = the processID obtain by the GetCurrentProcessId function
                          Process.Terminate
        End If
    Next 
 
    End Function

Hope it will helps a bit

[LostInJavaScriptLand]

RobDog888,

That is strange that it works for you but not for me...I just tried sleeping after every command to see if it was Excel that was loosing it's mind...but it still does the same thing.

I'm using VB.NET 1.1 and using SharpDevelop 1.1 for my IDE.

Also, I'm working with Office 2003 SP2, and I'm using the Office XP PIA's (in a folder and not installed in the GAC). I also thought that the version of PIA's that I was using could be causing the issue...so I just changed the PIA's in my project to the 2003 version (which was installed in the GAC)...still does the same thing.

-----

billhuard,

Your solution works...that is essentially what I'm currently using. But where the difference in my code is...I'm not killing the process from a macro, but rather I'm killing Excel from my .NET Application. I use a function called "GetWindowThreadProcessID" which is in user32.dll to get the process ID by providing the window handle to the specific Excel application (from Application.Hwnd). Then, I just feed that process ID into the taskkill command, and it kills the task.

But, I've also had it to where the process ID could not be found, and it killed just enough processes on my computer to get it to the point that it wouldn't shut down...lol. Also, if a user opens an Excel file during the import process, I've also had it to where the file will open up with the Excel process I'm trying to kill. That is why I'm looking for the "correct" way to do it.

[RobDog888]

Maybe its a #Develop issue? I am running 2003 1.1 and Office 2003 SP2 with 2003 PIAs.

[LostInJavaScriptLand]

I have also had this issue occur with VS 2002 (.NET 1.0) using Office XP and the XP PIAs. For right now, I found a temporary solution. I switched over to Koogra, which can read Excel files without using Excel.

http://sourceforge.net/projects/koogra