VB2005 problem with Excel

[Guest]

A VB2003 application upgraded to VB2005 that builds an Excel spreadsheet.
Everything appears to work correctly except that the Excel object does not go
away it is still in the Process list in task manager.

This problem did not exist in the VB2003 version.

Are there a any ideas of what might be happening?

All of the Excel objects are being disposed of by setting them to Nothing
and the Quit method is called for Excel. I've even used the
System.Runtime.InteropServices.Marshal.ReleaseComObject method as well as the
WaitForPendingFinalizers method and nothing seems to effect this.

At the applications END statement an error shows of "ObjectDisposedException
was not handled".

 

[Carlos J. Quintero [VB MVP]]

I suppose that you have already read and applied:

Office application does not quit after automation from Visual Studio .NET
client
http://support.microsoft.com/default.aspx?scid=kb;en-us;317109

So, I would recommend to remove statements that manipulate Excel until you
find the culprit. In other words, post a minimal sample to reproduce the
problem, so other people can take a look.

--

Best regards,

Carlos J. Quintero

MZ-Tools: Productivity add-ins for Visual Studio .NET, VB6, VB5 and VBA
You can code, design and document much faster.
Free resources for add-in developers:
http://www.mztools.com

 

[Guest]

I've found out by experimenting around that Excel was not going away due to
a secondary problem I'm having with the IBM iSeries .net data provider.

Changing the application to use the ODBC provider resolved the Excel issue.

Now it's just a matter of waiting for IBM to provide an update to their .net
data provider for the iSeries machine.

 

[Guest]

Carlos,

Even though I've resolved part of the problem I find that Excel does not
disappear from the Process list until the application terminates.

This is not a particular problem for this specific application but it would
be for others especailly the ones that tend to remain open on the desktop for
most of the day and Excel is used for a reporting tool.

Here's the sample code.

Imports Excel
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim objexcel As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
objexcel = New Excel.Application
xlWB = objexcel.Workbooks.Add
xlWS = xlWB.Sheets("Sheet1")
xlWS.Select()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWS)
xlWS = Nothing
xlWB.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWB)
xlWB = Nothing
objexcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(objexcel)
objexcel = Nothing

End Sub
End Class

One other thing I'm using Excel 2000 the Object Library version 9.

 

[Carlos J. Quintero [VB MVP]]

Hi Darrell ,

In your sample code neither objexcel.Workbooks nor xlWB.Sheets are using a
explicit variable that is released explicitly later, so Excel will stay
alive...

Please read again CAREFULLY the article that I mentioned, the Resolution
section...

--

Best regards,

Carlos J. Quintero

MZ-Tools: Productivity add-ins for Visual Studio .NET, VB6, VB5 and VBA
You can code, design and document much faster.
Free resources for add-in developers:
http://www.mztools.com

 

[Guest]

I read it carefully. The item it does not mention is how to select a specific
sheet in the Workbook. But like I said this is different behavior then found
in VB6 or VB2003.

Here is the modified code that works:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim objExcel As Excel.Application
Dim xlWBs As Excel.Workbooks ' added
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlSheets As Excel.Sheets 'new object added

objExcel = New Excel.Application
xlWBs = objExcel.Workbooks ' added
xlWB = xlWBs.Add ' changed
xlSheets = objExcel.Sheets ' added
xlWS = xlSheets("Sheet1") ' changed
xlWS.Select()
NAR(xlWS)
NAR(xlSheets)
xlWB.Close()
NAR(xlWB)
NAR(xlWBs)
objexcel.Quit()
NAR(objexcel)
End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch
Finally
o = Nothing
End Try
End Sub

 

[Carlos J. Quintero [VB MVP]]

Hi Darrell,

I read it carefully. The item it does not mention is how to select a
specific sheet in the Workbook.

It does not mention that, but it points that you need to use a variable each
time that you retrieve a property from the object model to release it later
explictly.

So, each time than in your code you see:

objexcel.Workbooks.Add

that will cause problems. And the same happens with:

xlWB.Sheets("Sheet1")

if you think that it is the short form of:

xlWB.Sheets.Item("Sheet1")

But like I said this is different behavior then found in VB6

Yes, due to COM Interop

or VB2003.

I don´t think so. The same should happen in VB2005, VB2003 or VB2002. In
fact, the article was written for VB.NET 2002 and VB.NET 2003, before VB
2005...

The proper way to debugging these things is start with:

Dim objexcel As Excel.Application

objexcel = New Excel.Application
objexcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(objexcel)
objexcel = Nothing

ensure that it releases Excel correctly, and each time that you add new
lines of code, check again that Excel is released.

--

Best regards,

Carlos J. Quintero

MZ-Tools: Productivity add-ins for Visual Studio .NET, VB6, VB5 and VBA
You can code, design and document much faster.
Free resources for add-in developers:
http://www.mztools.com