468,780 Members | 2,324 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,780 developers. It's quick & easy.

Help with vb.net controling excel and excel is hanging in mem

I have a vb.net app that is controling excel 2000 thru the com interop
interface. I have referenced the excel 9.0 library and have cut down the
code in the problem subroutine to this:

Dim objExcelApp As New Excel.Application

objExcelApp.Quit()
objExcelApp = Nothing

The problem is excel is hanging in memory and has to be closed by the task
manager. Anyone have any ideas why it is hanging.
Nov 20 '05 #1
5 2249
This is happening because the object that objExcelApp references has an
internal reference to Excel through COM automation and Excel will stay alive
as long as the object is alive. At first glance it would appear that this
would happen when you set objExcelApp to Nothing but that's not the case.
The object isn't destroyed at the point you set the variable to Nothing,
it's destroyed at some later point by the Garbage Collector thus extending
the life of Excel.

What you need to do is tell COM to release the internal connection between
your .NET object and the Excel COM object. You do this by adding one line of
code after the call to Quit as below:

Dim objExcelApp As New Excel.Application

objExcelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComO bject(objExcelApp)
objExcelApp = Nothing

--
Rob Windsor [MVP-VB]
G6 Consulting
Toronto, Canada

"David C. Allen" <da*****@avci.net> wrote in message
news:eL*************@tk2msftngp13.phx.gbl...
I have a vb.net app that is controling excel 2000 thru the com interop
interface. I have referenced the excel 9.0 library and have cut down the
code in the problem subroutine to this:

Dim objExcelApp As New Excel.Application

objExcelApp.Quit()
objExcelApp = Nothing

The problem is excel is hanging in memory and has to be closed by the task
manager. Anyone have any ideas why it is hanging.

Nov 20 '05 #2
* "David C. Allen" <da*****@avci.net> scripsit:
I have a vb.net app that is controling excel 2000 thru the com interop
interface. I have referenced the excel 9.0 library and have cut down the
code in the problem subroutine to this:

Dim objExcelApp As New Excel.Application

objExcelApp.Quit()
objExcelApp = Nothing

The problem is excel is hanging in memory and has to be closed by the task
manager. Anyone have any ideas why it is hanging.


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

--
Herfried K. Wagner [MVP]
<http://dotnet.mvps.org/>
Website Address Changed!
Nov 20 '05 #3
Hi David,

Check out Herfried's response for a link to a KB article. You might also
consider the following book:

Professional Visual Basic Interoperability - COM and VB6 to .NET
by Billy Hollis , Rockford Lhotka (WROX)

Rob
"David C. Allen" <da*****@avci.net> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
You are definately and MVP. That fixed my problem. Can you tell me where
that kind of information is documented?

Thanks!
"Rob Windsor [MVP]" <rw******@NO.MORE.SPAM.bigfoot.com> wrote in message
news:O9**************@tk2msftngp13.phx.gbl...
This is happening because the object that objExcelApp references has an
internal reference to Excel through COM automation and Excel will stay alive
as long as the object is alive. At first glance it would appear that this
would happen when you set objExcelApp to Nothing but that's not the case. The object isn't destroyed at the point you set the variable to Nothing,
it's destroyed at some later point by the Garbage Collector thus extending the life of Excel.

What you need to do is tell COM to release the internal connection between your .NET object and the Excel COM object. You do this by adding one

line of
code after the call to Quit as below:

Dim objExcelApp As New Excel.Application

objExcelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComO bject(objExcelApp)
objExcelApp = Nothing

--
Rob Windsor [MVP-VB]
G6 Consulting
Toronto, Canada

"David C. Allen" <da*****@avci.net> wrote in message
news:eL*************@tk2msftngp13.phx.gbl...
I have a vb.net app that is controling excel 2000 thru the com interop
interface. I have referenced the excel 9.0 library and have cut down the code in the problem subroutine to this:

Dim objExcelApp As New Excel.Application

objExcelApp.Quit()
objExcelApp = Nothing

The problem is excel is hanging in memory and has to be closed by the

task manager. Anyone have any ideas why it is hanging.



Nov 20 '05 #4
Well it's doing it again. Not sure why. Here is the entire subroutine:

Sub ProcessExcelFile(ByVal lsReportPath As String, ByVal lsReportName As
String)

Dim objExcelApp As New Excel.Application

Dim SIMSWorkbookName As String

Try

lblStatus.Text = "Opening:" & lsReportPath & lsReportName

lblStatus.Refresh()

'open up the excel and the workbook

objExcelApp.Workbooks.Open(lsReportPath & lsReportName)

If chkShowExcel.Checked = True Then

objExcelApp.Visible = True

End If

objExcelApp.Workbooks(lsReportName).Unprotect("ops tab")

objExcelApp.DisplayAlerts = False

objExcelApp.Worksheets("SIMS RAW DATA").Visible = True

objExcelApp.Worksheets("SIMS RAW DATA").Activate()

objExcelApp.ActiveSheet.Cells.select()

objExcelApp.Selection.ClearContents()

SIMSWorkbookName = Path.GetFileName(msSIMSData)

objExcelApp.Workbooks.Open(msSIMSData)

objExcelApp.Workbooks(SIMSWorkbookName).Worksheets ("DowSIMSExtract").Activat
e()

objExcelApp.Workbooks(SIMSWorkbookName).Worksheets ("DowSIMSExtract").Cells.S
elect()

objExcelApp.Selection.Copy()

objExcelApp.Workbooks(lsReportName).Worksheets("SI MS RAW DATA").Activate()

objExcelApp.ActiveSheet.Range("A1").Select()

objExcelApp.ActiveSheet.Paste()

'objExcelApp.Run("ProcessSIMSData")

If chkPause.Checked = True And chkShowExcel.Checked = True Then

MessageBox.Show("Please review the chart and press ok when ready to close
chart file", gcAPPNAME, MessageBoxButtons.OK, MessageBoxIcon.Information)

End If

objExcelApp.Workbooks(SIMSWorkbookName).Close(Fals e) 'don't save any changes

'Close down the workbook and excel

objExcelApp.Run("hidereports")

objExcelApp.ActiveWorkbook.Protect("opstab")

'objExcelApp.ActiveWorkbook.Save()

objExcelApp.Workbooks(lsReportName).Close(True) 'Close and save changes

'Close down Excel

objExcelApp.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComO bject(objExcelApp)

objExcelApp = Nothing

Catch ex As Exception

objExcelApp.Workbooks.Close()

objExcelApp.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComO bject(objExcelApp)

objExcelApp = Nothing

lblStatus.Text = "Error processing workbook name from: " & lsReportPath &
lsReportName & " Please check your task manager to ensure EXCEL is not hung
in memory by looking at the processes tab."

If gbBatchMode Then

WriteToEventLog("Error: " & Err.Description)

Else

MessageBox.Show("Error: " & Err.Description, gcAPPNAME,
MessageBoxButtons.OK, MessageBoxIcon.Warning)

End If

End Try

End Sub

"Rob Windsor [MVP]" <rw******@NO.MORE.SPAM.bigfoot.com> wrote in message
news:OF**************@TK2MSFTNGP12.phx.gbl...
Hi David,

Check out Herfried's response for a link to a KB article. You might also
consider the following book:

Professional Visual Basic Interoperability - COM and VB6 to .NET
by Billy Hollis , Rockford Lhotka (WROX)

Rob
"David C. Allen" <da*****@avci.net> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
You are definately and MVP. That fixed my problem. Can you tell me where
that kind of information is documented?

Thanks!
"Rob Windsor [MVP]" <rw******@NO.MORE.SPAM.bigfoot.com> wrote in message
news:O9**************@tk2msftngp13.phx.gbl...
This is happening because the object that objExcelApp references has an internal reference to Excel through COM automation and Excel will stay

alive
as long as the object is alive. At first glance it would appear that this would happen when you set objExcelApp to Nothing but that's not the case. The object isn't destroyed at the point you set the variable to Nothing, it's destroyed at some later point by the Garbage Collector thus extending the life of Excel.

What you need to do is tell COM to release the internal connection between your .NET object and the Excel COM object. You do this by adding one line
of
code after the call to Quit as below:

Dim objExcelApp As New Excel.Application

objExcelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComO bject(objExcelApp)
objExcelApp = Nothing

--
Rob Windsor [MVP-VB]
G6 Consulting
Toronto, Canada

"David C. Allen" <da*****@avci.net> wrote in message
news:eL*************@tk2msftngp13.phx.gbl...
> I have a vb.net app that is controling excel 2000 thru the com interop > interface. I have referenced the excel 9.0 library and have cut down the > code in the problem subroutine to this:
>
> Dim objExcelApp As New Excel.Application
>
> objExcelApp.Quit()
> objExcelApp = Nothing
>
> The problem is excel is hanging in memory and has to be closed by

the task
> manager. Anyone have any ideas why it is hanging.
>
>



Nov 20 '05 #5
Ok I looked at the KB article. I put gc.collect at the end and it still had
a problem. Then I changed line:

objExcelApp.ActiveSheet.Cells.select()

to:

objExcelApp.Worksheets("SIMS RAW DATA").Cells.select()

and it was all better. Maybe I should do the same to the following lines
also just to be safe:

objExcelApp.ActiveSheet.Cells.select()
objExcelApp.Selection.ClearContents()
"David C. Allen" <da*****@avci.net> wrote in message
news:Of**************@TK2MSFTNGP12.phx.gbl...
Well it's doing it again. Not sure why. Here is the entire subroutine:

Sub ProcessExcelFile(ByVal lsReportPath As String, ByVal lsReportName As
String)

Dim objExcelApp As New Excel.Application

Dim SIMSWorkbookName As String

Try

lblStatus.Text = "Opening:" & lsReportPath & lsReportName

lblStatus.Refresh()

'open up the excel and the workbook

objExcelApp.Workbooks.Open(lsReportPath & lsReportName)

If chkShowExcel.Checked = True Then

objExcelApp.Visible = True

End If

objExcelApp.Workbooks(lsReportName).Unprotect("ops tab")

objExcelApp.DisplayAlerts = False

objExcelApp.Worksheets("SIMS RAW DATA").Visible = True

objExcelApp.Worksheets("SIMS RAW DATA").Activate()

objExcelApp.ActiveSheet.Cells.select()

objExcelApp.Selection.ClearContents()

SIMSWorkbookName = Path.GetFileName(msSIMSData)

objExcelApp.Workbooks.Open(msSIMSData)

objExcelApp.Workbooks(SIMSWorkbookName).Worksheets ("DowSIMSExtract").Activat e()

objExcelApp.Workbooks(SIMSWorkbookName).Worksheets ("DowSIMSExtract").Cells.S elect()

objExcelApp.Selection.Copy()

objExcelApp.Workbooks(lsReportName).Worksheets("SI MS RAW DATA").Activate()

objExcelApp.ActiveSheet.Range("A1").Select()

objExcelApp.ActiveSheet.Paste()

'objExcelApp.Run("ProcessSIMSData")

If chkPause.Checked = True And chkShowExcel.Checked = True Then

MessageBox.Show("Please review the chart and press ok when ready to close
chart file", gcAPPNAME, MessageBoxButtons.OK, MessageBoxIcon.Information)

End If

objExcelApp.Workbooks(SIMSWorkbookName).Close(Fals e) 'don't save any changes
'Close down the workbook and excel

objExcelApp.Run("hidereports")

objExcelApp.ActiveWorkbook.Protect("opstab")

'objExcelApp.ActiveWorkbook.Save()

objExcelApp.Workbooks(lsReportName).Close(True) 'Close and save changes

'Close down Excel

objExcelApp.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComO bject(objExcelApp)

objExcelApp = Nothing

Catch ex As Exception

objExcelApp.Workbooks.Close()

objExcelApp.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComO bject(objExcelApp)

objExcelApp = Nothing

lblStatus.Text = "Error processing workbook name from: " & lsReportPath &
lsReportName & " Please check your task manager to ensure EXCEL is not hung in memory by looking at the processes tab."

If gbBatchMode Then

WriteToEventLog("Error: " & Err.Description)

Else

MessageBox.Show("Error: " & Err.Description, gcAPPNAME,
MessageBoxButtons.OK, MessageBoxIcon.Warning)

End If

End Try

End Sub

"Rob Windsor [MVP]" <rw******@NO.MORE.SPAM.bigfoot.com> wrote in message
news:OF**************@TK2MSFTNGP12.phx.gbl...
Hi David,

Check out Herfried's response for a link to a KB article. You might also
consider the following book:

Professional Visual Basic Interoperability - COM and VB6 to .NET
by Billy Hollis , Rockford Lhotka (WROX)

Rob
"David C. Allen" <da*****@avci.net> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
You are definately and MVP. That fixed my problem. Can you tell me where that kind of information is documented?

Thanks!
"Rob Windsor [MVP]" <rw******@NO.MORE.SPAM.bigfoot.com> wrote in message news:O9**************@tk2msftngp13.phx.gbl...
> This is happening because the object that objExcelApp references has an > internal reference to Excel through COM automation and Excel will stay alive
> as long as the object is alive. At first glance it would appear that

this
> would happen when you set objExcelApp to Nothing but that's not the

case.
> The object isn't destroyed at the point you set the variable to Nothing, > it's destroyed at some later point by the Garbage Collector thus

extending
> the life of Excel.
>
> What you need to do is tell COM to release the internal connection

between
> your .NET object and the Excel COM object. You do this by adding one

line
of
> code after the call to Quit as below:
>
> Dim objExcelApp As New Excel.Application
>
> objExcelApp.Quit()
> System.Runtime.InteropServices.Marshal.ReleaseComO bject(objExcelApp)
> objExcelApp = Nothing
>
> --
> Rob Windsor [MVP-VB]
> G6 Consulting
> Toronto, Canada
>
>
>
> "David C. Allen" <da*****@avci.net> wrote in message
> news:eL*************@tk2msftngp13.phx.gbl...
> > I have a vb.net app that is controling excel 2000 thru the com interop > > interface. I have referenced the excel 9.0 library and have cut
down
the
> > code in the problem subroutine to this:
> >
> > Dim objExcelApp As New Excel.Application
> >
> > objExcelApp.Quit()
> > objExcelApp = Nothing
> >
> > The problem is excel is hanging in memory and has to be closed by

the task
> > manager. Anyone have any ideas why it is hanging.
> >
> >
>
>



Nov 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by John Winterbottom | last post: by
1 post views Thread by Michael Tkachev | last post: by
9 posts views Thread by pic078 via AccessMonster.com | last post: by
1 post views Thread by =?iso-8859-1?q?Jean-Fran=E7ois_Michaud?= | last post: by
8 posts views Thread by =?Utf-8?B?SmVycnkgQw==?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
2 posts views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.