Strange disposing of Excel problem
Strange disposing of Excel problem
Strange disposing of Excel problem
Strange disposing of Excel problem
Strange disposing of Excel problem
Strange disposing of Excel problem Strange disposing of Excel problem Strange disposing of Excel problem Strange disposing of Excel problem Strange disposing of Excel problem Strange disposing of Excel problem Strange disposing of Excel problem Strange disposing of Excel problem
Strange disposing of Excel problem Strange disposing of Excel problem
Strange disposing of Excel problem
Go Back  Xtreme Visual Basic Talk > > > Strange disposing of Excel problem


Reply
 
Thread Tools Display Modes
  #1  
Old 03-27-2013, 04:58 AM
DrPunk's Avatar
DrPunkStrange disposing of Excel problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default Strange disposing of Excel problem


I'm having a problem disposing Excel objects. I'm going through my code line by line to see what's causing Excel to live in the task manager and I've got suc a weird one I don't know what to do.

The code opens a spreadsheet, checks the first sheet is the correct name, and checks if there's only one sheet (i.e. not already processed).
Code:
Dim excelApp As Excel.Application
Dim excelBooks As Excel.Workbooks
Dim excelBook As Excel.Workbook
Dim excelSheets As Excel.Sheets
Dim excelSheet As Excel.Worksheet
Dim sheetCount As Integer

Try
    excelApp = New Excel.Application
Catch ex As Exception
    MessageBox.Show("Error starting Excel. Check Office installation.", "Unexpected error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    Return False
End Try

excelApp.Workbooks.Open(DirectCast(lstSpreadsheets.SelectedItem, BankItem).SpreadSheetPath)

' I read on the internet that it's best to never use 2 dots when refering to
' the interop objects, the sheetCount is just to test referencing sheets.Count
' or not
excelSheets = excelApp.Worksheets
excelBooks = excelApp.Workbooks
excelSheet = excelSheets(1)
sheetCount = excelSheets.Count

If excelSheet.Name.ToUpper = CorrectSheetName Then
            
    If sheetCount > 1 Then
            
        MessageBox.Show("The selected spreadsheet contains more than one sheet." & vbCrLf & vbCrLf & _
                        "This spreadsheet may already have been processed.", "Invalid spreadsheet", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

        sheetCount = 0
        excelBooks.Close()
        excelApp.Quit()

        Release(excelBooks)
        excelBooks = Nothing
        Release(excelSheet)
        excelSheet = Nothing
        Release(excelSheets)
        excelSheets = Nothing
        Release(excelApp)
        excelApp = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()
        Return False
    End If
End if
Now, that disposal FAILS. Excel remains in the task manager.

But, if I take out the "sheetCount > 1" check then everything disposes correctly. Excel is gone from the task manager. For example...
Code:
Dim excelApp As Excel.Application
Dim excelBooks As Excel.Workbooks
Dim excelBook As Excel.Workbook
Dim excelSheets As Excel.Sheets
Dim excelSheet As Excel.Worksheet
Dim sheetCount As Integer

Try
    excelApp = New Excel.Application
Catch ex As Exception
    MessageBox.Show("Error starting Excel. Check Office installation.", "Unexpected error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    Return False
End Try

excelApp.Workbooks.Open(DirectCast(lstSpreadsheets.SelectedItem, BankItem).SpreadSheetPath)

' I read on the internet that it's best to never use 2 dots when refering to
' the interop objects, the sheetCount is just to test referencing sheets.Count
' or not
excelSheets = excelApp.Worksheets
excelBooks = excelApp.Workbooks
excelSheet = excelSheets(1)
sheetCount = excelSheets.Count

If excelSheet.Name.ToUpper = CorrectSheetName Then
            
    If True Then
            
        MessageBox.Show("The selected spreadsheet contains more than one sheet." & vbCrLf & vbCrLf & _
                        "This spreadsheet may already have been processed.", "Invalid spreadsheet", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

        sheetCount = 0
        excelBooks.Close()
        excelApp.Quit()

        Release(excelBooks)
        excelBooks = Nothing
        Release(excelSheet)
        excelSheet = Nothing
        Release(excelSheets)
        excelSheets = Nothing
        Release(excelApp)
        excelApp = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()
        Return False
    End If
End if
The line in red is the ONLY thing that has changed.

This second one works, the first doesn't.

WHY?

It makes no sense to me at all. What am I keeping referenced to stop it being disposed?

Edit :- The release is just doing the releasecomobject
Code:
Private Sub Release(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
    Catch ex As Exception

    End Try
End Sub
__________________
There are no computers in heaven!

Last edited by DrPunk; 03-27-2013 at 05:03 AM.
Reply With Quote
  #2  
Old 03-27-2013, 06:18 AM
DrPunk's Avatar
DrPunkStrange disposing of Excel problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Gets a bit stranger.

I change the "If sheetCount > 1 Then" to "If excelSheets.Count > 1 Then" and Excel disposes properly.

Great!!!!! No idea why, but at least it's progress.

I can get rid of sheetCount then. I get rid of sheetCount and everywhere it's used and suddenly it doesn't dispose properly, it stays in the task manager.

What?!?!?!?!?!?!?

I put all sheetCount back, it disposes properly again.

I take out line by line to find out what part is causing the problem.

Turns out, if I don't have the "sheetCount = excelSheets.Count" then it doesn't dispose of Excel properly.

So...
Code:
excelSheets = excelApp.Worksheets
excelBooks = excelApp.Workbooks
excelSheet = excelSheets(1)
sheetCount = excelSheets.Count

If excelSheet.Name.ToUpper = CorrectSheetName Then

    If excelSheets.Count > 1 Then
... disposes correctly.
Code:
excelSheets = excelApp.Worksheets
excelBooks = excelApp.Workbooks
excelSheet = excelSheets(1)
'sheetCount = excelSheets.Count ' sheetCount line commented out

If excelSheet.Name.ToUpper = CorrectSheetName Then

    If excelSheets.Count > 1 Then
... doesn't dispose correctly.

What on earth is going on?
__________________
There are no computers in heaven!
Reply With Quote
  #3  
Old 03-27-2013, 07:03 AM
DrPunk's Avatar
DrPunkStrange disposing of Excel problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

At the moment I think that most of the problems are caused by the opening of the spreadsheet line...
Code:
excelApp.Workbooks.Open(DirectCast(lstSpreadsheets.SelectedItem, BankItem).SpreadSheetPath)
Changing that to...
Code:
Dim excelBooks as Excel.WorkBooks
Dim excelBook as Excel.WorkBook

excelBooks = excelApp.WorkBooks
excelBook = excelBooks.Open(spreadsheet)
... and then cleaning up all the excelBook(s) objects seems to be much tidier. I can get rid of sheetCount fine and it still works.
__________________
There are no computers in heaven!
Reply With Quote
  #4  
Old 03-27-2013, 07:28 AM
DrPunk's Avatar
DrPunkStrange disposing of Excel problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Moving on...

Next thing I do is check for some valid data in the first sheet.
Code:
row = 1
found = False
count = 0
Do
    excelRange = excelSheet.Cells(row, 2)
    cell = excelRange.Value

    If ValidBarcode(cell) Then
        found = True
    Else
        row += 1
    End If
    count += 1
Loop Until found Or count = 100
After running this, Excel would not dispose properly.

Commenting out the "excelRange = excelSheet.Cells(row, 2)" showed it was this line that caused the problem.

What to do? For lack of anything else to try I thought I'd reference excelSheets(1) instead of the excelSheet that was set to excelSheets(1)...
Code:
row = 1
found = False
count = 0
Do
    excelRange = excelSheets(1).Cells(row, 2)
    cell = excelRange.Value

    If ValidBarcode(cell) Then
        found = True
    Else
        row += 1
    End If
    count += 1
Loop Until found Or count = 100
And BINGO! Excel now disposes properly.

This is STUPID!!!!
__________________
There are no computers in heaven!
Reply With Quote
  #5  
Old 04-01-2013, 01:28 AM
Shankar_R Shankar_R is offline
Newcomer
 
Join Date: Mar 2013
Posts: 4
Default

I am a newbie and don't pretend to offer an explanation to your problem, because I don't have any. With apologies, can I instead ask you a question, since I think I am going to be faced with similar issues shortly.

Can we assume that if Excel quits at the end of running the .NET program, there will be no other COM objects lurking in system memory? Is it a sufficient condition? Or do we need to check for others in the Task Manager?

My program is a lot more complicated in terms of reading spreadsheets, which will include named variables and arrays. I hope I don't have to spend too much time and effort on these kinds of issues.

Thanks.

Shankar
Reply With Quote
  #6  
Old 04-01-2013, 09:31 AM
AtmaWeapon's Avatar
AtmaWeaponStrange disposing of Excel problem AtmaWeapon is offline
Fabulous Florist

Forum Leader
* Guru *
 
Join Date: Feb 2004
Location: Austin, TX
Posts: 9,500
Default

Your cleanup code is very different from that suggested in this post. My guess is the various changes subtly alter the timing of how the GC collects things, and certain versions accidentally work.
__________________
.NET Resources
My FAQ threads | Tutor's Corner | Code Library
I would bet money 2/3 of .NET questions are already answered in one of these three places.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Strange disposing of Excel problem
Strange disposing of Excel problem
Strange disposing of Excel problem Strange disposing of Excel problem
Strange disposing of Excel problem
Strange disposing of Excel problem
Strange disposing of Excel problem Strange disposing of Excel problem Strange disposing of Excel problem Strange disposing of Excel problem Strange disposing of Excel problem Strange disposing of Excel problem Strange disposing of Excel problem
Strange disposing of Excel problem
Strange disposing of Excel problem
 
Strange disposing of Excel problem
Strange disposing of Excel problem
 
-->