Excel won't Close - Common issue with a twist

[Mike G]

Background: I wrote a VB .NET class that can be passed a form. Any
datagrids on the passed in form are listed to the user and the user
can select which ones they want exported to Excel. Each grid is
exported to a different worksheet within the same workbook.

The Excel file is created successfully, but Excel remains open in Task
Manager until the application is closed entirely. To troubleshoot
this, I have commented out almost all the Excel references to range
objects and worksheet objects to isolate where the problem is
occurring. Basically, in the scaled down code, I have three lines of
test code that, when placed right after the declaration of the
Worksheet object, work fine and the Excel closes properly. When I
move these further down in the code into some nested if statements,
Excel does not close. In the code, I have the working version
uncommented out, and I also show the commented place where the same
three lines would cause this error. They are:

wrkSheet = wrkBook.Worksheets.Add()
wrkSheet.Name = "TEST"
wrkSheet.Range("A1").Value = "TEST"

Steps I've tried:
1.) As mentioned, I scaled everything down so there are no references
to any more Excel objects than there need to be. In the test version,
I no longer reference the range object. There shouldn't be any
implicit references to Excel objects in the version below.
2.) I've made sure all objects are first released using
ReleaseComObject and then set to nothing.
3.) I've confirmed that the objects are released and set to nothing in
proper order and I've checked the variable state after each release
and it appears to be working fine.

The twist is, the only thing different between a working version and a
version that fails to close Excel is the placement of some lines of
code.

Any help would be greatly appreciated. I apologize for the extraneous
code in the example below but it seemed silly to remove everything
just to give an example.

*****************************************************************************

Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnExport.Click

' get the file name to save the list view information in from
the standard save dialog
Dim saveFileDialog1 As New SaveFileDialog

Dim xx As Integer = 0
Dim yy As Integer = 0
Dim rowCount As Integer = 0
Dim colCount As Integer = 0
Dim EndOfRows As Boolean = False
Dim NoRows As Boolean = False
Dim RowTest As String = ""

Dim vctl As Control
Dim vctl2 As Control

Dim app As New Excel.Application
Dim wrkBooks As Excel.Workbooks = app.Workbooks
Dim wrkBook As Excel.Workbook = wrkBooks.Add()
Dim wrkSheet As Excel.Worksheet
Dim wrkRange As Excel.Range

''test *****NOTE: When the next three lines of code are moved
to later
'' on in the module, Excel will not close. When left
here,
'' Excel closed fine. That is the only difference in
this
'' scaled down version of the code.

wrkSheet = wrkBook.Worksheets.Add()
wrkSheet.Name = "TEST"
wrkSheet.Range("A1").Value = "TEST"

''End Test **** NOTE

If chkBox1.Checked = True Or chkBox2.Checked = True Or
chkBox3.Checked = True _
Or chkBox4.Checked = True Or chkBox5.Checked = True Or
chkBox6.Checked = True _
Or chkBox7.Checked = True Or chkBox8.Checked = True Or
chkBox9.Checked = True Then

Try
saveFileDialog1.InitialDirectory = m_DefaultDirectory

saveFileDialog1.Filter = "Excel files (*.xls)|*.xls"
saveFileDialog1.FilterIndex = 1
saveFileDialog1.CheckFileExists = False
saveFileDialog1.CheckPathExists = True
saveFileDialog1.RestoreDirectory = True
saveFileDialog1.FileName = Date.Now.Year.ToString &
"-" & Date.Now.Month.ToString & "-" & Date.Now.Day.ToString _
& "_ExportGrids"

If saveFileDialog1.ShowDialog() = DialogResult.OK Then

For Each vctl In m_Form.Controls
If TypeOf vctl Is DataGrid Then
For Each vctl2 In Me.Controls
If TypeOf vctl2 Is CheckBox Then
If CType(vctl2, CheckBox).Checked
= True Then
If CType(vctl2, CheckBox).Tag
= CType(vctl, DataGrid).Name Then
xx = 0
yy = 0
NoRows = False
EndOfRows = False

colCount =
CType(CType(vctl, DataGrid).DataSource, DataTable).Columns.Count()
rowCount =
CType(CType(vctl, DataGrid).DataSource, DataTable).Rows.Count
If rowCount = 0 Then
NoRows = True
End If

If Not NoRows Then
'****NOTE: If I uncomment these
' and Excel doesn't close
'wrkSheet =
wrkBook.Worksheets.Add()
'wrkSheet.Name = "TEST"
'wrkSheet.Range("A1").Value
= "TEST"
'****End NOTE
Dim
strColumnArray(colCount) As String
Dim
strRowArray(rowCount, colCount) As String
Dim strRange As String
Dim EndColumn As
String

'wrkSheet.Name =
FormatWrkSheetName(CType(vctl, DataGrid).CaptionText)


For yy = 0 To colCount
- 1
strColumnArray(yy)
= CType(CType(vctl, DataGrid).DataSource,
DataTable).Columns(yy).ColumnName.ToString
Next
Select Case colCount
Case Is > 26
EndColumn =
"A" & Chr((colCount - 26) + 64)
Case Is > 52
EndColumn =
"B" & Chr((colCount - 26) + 64)
Case Is > 78
EndColumn =
"C" & Chr((colCount - 26) + 64)
Case Is > 104
EndColumn =
"D" & Chr((colCount - 26) + 64)
Case Else
EndColumn =
Chr((colCount) + 64)
End Select

strRange = "A1:" &
EndColumn & "1"

'wrkSheet.Range(strRange).Value = strColumnArray
'wrkRange =
CType(wrkSheet.Range(strRange), Excel.Range)
'wrkRange.Value =
strColumnArray
'wrkRange.Font.Bold =
True

strRange = "A2:" &
EndColumn & (rowCount + 1).ToString
For xx = 0 To rowCount
- 1
For yy = 0 To
colCount - 1

strRowArray(xx, yy) = CType(vctl, DataGrid).Item(xx, yy).ToString
Next
Next
'wrkRange =
CType(wrkSheet.Range(strRange), Excel.Range)
'wrkRange.Value =
strRowArray

'NAR(wrkRange)
'NAR(wrkSheet)

strColumnArray =
Nothing
strRowArray = Nothing
strRange = Nothing
End If

End If
End If
End If
Next

End If

Next

If File.Exists(saveFileDialog1.FileName) Then
File.Delete(saveFileDialog1.FileName)
End If

wrkBook.SaveAs(saveFileDialog1.FileName)

MessageBox.Show(Text:="Exported Grids File Created
Successfully. " & _
"Please refer to file: " & vbCrLf & _
saveFileDialog1.FileName.ToString & vbCrLf & _
"to see created file.", caption:="Successful
Export", _
buttons:=MessageBoxButtons.OK,
Icon:=MessageBoxIcon.Information)
End If

Catch ex As Exception
MessageBox.Show(Text:="Failed to Export Data Grids. "
& vbCrLf & "Error: " & ex.Message.ToString, _
caption:="Failed Export.", _
buttons:=MessageBoxButtons.OK,
Icon:=MessageBoxIcon.Error)
Finally
'app.DisplayAlerts = False

NAR(wrkRange)
NAR(wrkSheet)

Call wrkBook.Close(SaveChanges:=False)
NAR(wrkBook)

Call wrkBooks.Close()
NAR(wrkBooks)

app.Quit()
NAR(app)

GC.Collect()
GC.WaitForPendingFinalizers()

Me.Close()
Me.Dispose()

End Try
Else
MessageBox.Show(Text:="No Grids selected for Export.", _
caption:="Failed Export", _
buttons:=MessageBoxButtons.OK,
Icon:=MessageBoxIcon.Error)

Me.Close()
Me.Dispose()
End If
End Sub

Private Sub NAR(ByRef o As Object)
If Not IsNothing(o) Then
Try
ReleaseComObject(o)
Catch
Finally
o = Nothing
End Try
End If
End Sub

 

[Jason Newell]

Mike,
This may not help, but you might try putting Marshal.ReleaseComObject in
a while loop until it reaches 0. It is possible that a given object has had
more than one AddRef() called behind the scenes. It usually only takes 1
object to have a ref count > 1 to cause an application to not close. I also
noticed that your using sytax like "wrkSheet.Range("A1").Value". Well,
wrkSheet.Range("A1") actually returns an COM object that since you don't set
it equal to anything, it may not be getting released. HTH.

Jason Newell

 

[Jason Newell]

Correction,
" It usually only takes 1 object to have a ref count > 1 to cause an
application to not close."

Should read:

" It usually only takes 1 object to have a ref count > 0 to cause an
application to not close."

Jason Newell

object to have a ref count > 1 to cause an application to not close.

 

[Mike G]

Thanks for the follow-up Jason.

Sorry about the typo - the line of code wrkSheet.Range("A1") was set
to a value in my original code. It was a cut and paste mistake when I
was highlighting the differences between working and non-working code.

Do you know of a way to display the number of references left after
calling the Marshal.ReleaseComObject method? I'd like to try your
looping suggestion.

From what I can see, it is almost as if the COM variables somehow
change scope because of where they are declared and instantiated
within my nested loops and ifs. The three lines of code, if kept out
of the loops, work fine. If inside the loops, not so good... I tried
to recreate the problem in a simpler version (one "For Each" loop, one
"if" statement") and I could not do it.

 

[Mike G]

Thanks for the follow-up Jason.

Sorry about the typo - the line of code wrkSheet.Range("A1") was set
to a value in my original code. It was a cut and paste mistake when I
was highlighting the differences between working and non-working code.

Do you know of a way to display the number of references left after
calling the Marshal.ReleaseComObject method? I'd like to try your
looping suggestion.

From what I can see, it is almost as if the COM variables somehow
change scope because of where they are declared and instantiated
within my nested loops and ifs. The three lines of code, if kept out
of the loops, work fine. If inside the loops, not so good... I tried
to recreate the problem in a simpler version (one "For Each" loop, one
"if" statement") and I could not do it.

 

[Paul Clement]

On 11 Feb 2004 06:54:02 -0800, (e-mail address removed) (Mike G) wrote:

¤ Background: I wrote a VB .NET class that can be passed a form. Any
¤ datagrids on the passed in form are listed to the user and the user
¤ can select which ones they want exported to Excel. Each grid is
¤ exported to a different worksheet within the same workbook.
¤
¤ The Excel file is created successfully, but Excel remains open in Task
¤ Manager until the application is closed entirely. To troubleshoot
¤ this, I have commented out almost all the Excel references to range
¤ objects and worksheet objects to isolate where the problem is
¤ occurring. Basically, in the scaled down code, I have three lines of
¤ test code that, when placed right after the declaration of the
¤ Worksheet object, work fine and the Excel closes properly. When I
¤ move these further down in the code into some nested if statements,
¤ Excel does not close. In the code, I have the working version
¤ uncommented out, and I also show the commented place where the same
¤ three lines would cause this error. They are:
¤
¤ wrkSheet = wrkBook.Worksheets.Add()
¤ wrkSheet.Name = "TEST"
¤ wrkSheet.Range("A1").Value = "TEST"
¤
¤ Steps I've tried:
¤ 1.) As mentioned, I scaled everything down so there are no references
¤ to any more Excel objects than there need to be. In the test version,
¤ I no longer reference the range object. There shouldn't be any
¤ implicit references to Excel objects in the version below.
¤ 2.) I've made sure all objects are first released using
¤ ReleaseComObject and then set to nothing.
¤ 3.) I've confirmed that the objects are released and set to nothing in
¤ proper order and I've checked the variable state after each release
¤ and it appears to be working fine.
¤
¤ The twist is, the only thing different between a working version and a
¤ version that fails to close Excel is the placement of some lines of
¤ code.
¤
¤ Any help would be greatly appreciated. I apologize for the extraneous
¤ code in the example below but it seemed silly to remove everything
¤ just to give an example.

Below is a Microsoft KB article that documents the solution Jason was referring to:

PRB: Office Application Does Not Quit After Automation from Visual Studio .NET Client
http://support.microsoft.com/default.aspx?scid=kb;en-us;317109

When all else fails you can terminate the app using API function calls:

Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal
lpWindowName As String) As Int32
Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Int32, ByVal wMsg
As Int32, ByVal wParam As Int32, ByVal lParam As Int32) As Int32

Public Function TerminateExcel()

Dim ClassName As String
Dim WindowHandle As Int32
Dim ReturnVal As Int32
Const WM_QUIT = &H12

Do

ClassName = "XLMain"
WindowHandle = FindWindow(ClassName, Nothing)

If WindowHandle Then
ReturnVal = PostMessage(WindowHandle, WM_QUIT, 0, 0)
End If

Loop Until WindowHandle = 0

End Function


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Doug]

Mike,

I've been struggling with this issue for a couple of days myself and
just discovered a VERY simple solution. Hopefully it will work for
you...

I tried various versions of ReleaseComObject, set object = Nothing and
calling GC.Collect(). The problem was that I was doing all of this
from within the same method that created the objects. I now have a
wrapper around my Export method. The wrapper calls GC to free up any
objects that Export was using. I no longer have ANY calls to
ReleaseComObject to set object = Nothing.

See code sample:

Public Shared Sub Export(ByVal dataGrid As
System.Windows.Forms.DataGrid)

'This method does handles all of the Excel manipulation
ExecuteExport(dataGrid)

'Must explicitly release the grip on Excel objects
'otherwise Excel process remains in memory until our
'app closes (even if user closes Excel).
'Also must do this outside the ExecuteExport method so that
'ExecuteExport will have released any Excel objects that it
'created (either explicity or implicitly).
GC.Collect()

End Sub

 

[Mike G]

Doug (and Everyone else, too!),

Thanks for the feedback. Your suggestion worked. I segregated out my
excel export into a separate function from my cleanup and garbage
collection and this worked. Same code, but different location. I
passed in the datagrid that I wanted to export, the export ran, and
after exiting the function, the cleanup took care of all the Excel
objects. My sanity has returned.

Mike