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("opstab")
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
objExcelApp.Workbooks(SIMSWorkbookName).Worksheets("DowSIMSExtract").Cells.S
elect()
objExcelApp.Selection.Copy()
objExcelApp.Workbooks(lsReportName).Worksheets("SIMS 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(False) '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.ReleaseComObject(objExcelApp)
objExcelApp = Nothing
Catch ex As Exception
objExcelApp.Workbooks.Close()
objExcelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(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 said:
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 said:
You are definately and MVP. That fixed my problem. Can you tell me where
that kind of information is documented?
Thanks!
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.ReleaseComObject(objExcelApp)
objExcelApp = Nothing
--
Rob Windsor [MVP-VB]
G6 Consulting
Toronto, Canada
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.
