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.
>
>
