Hello
I use the below procedure to open a worksheet from a windows form. It works fine, the problem I have is that whenever I click on a button on my form to go to a particular sheet it creates a new instance of Excel in the background process. In the Apps there is only one instance, but the background processes, I see multiple Excel which are created whenever I click to go to any sheet on my form.
I tried using the following to release the background processes:
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
It worked but because I have a button that closes the winform and Excel, whenever I put that at the end of my code the form would close, but not Excel.
How can I prevent that from happening? It is not an issue on my computer, but my client's PC will be running multiple applications which are resource user heavy so having multiple instances open under background process will slow down their PC even more. How can I prevent this from happening?
Here is my code to open and check for Excel:
Public Sub GoToSheets(sheetName As String)
'This sub is used to open the workbook on the selected sheet.
'This checks to see if Excel workbook is opened, if not it
'opens Excel, the workbook and then the selected sheet. If the workbook is
'opened, it goes to the selected sheet.
'@param sheetName, sheet to be displayed
Dim xlApp As Excel.Application
Try
'get an existing excel.application object
xlApp = CType(GetObject(, "Excel.Application"), Application)
Catch ex As Exception
'no existing excel.application object - create a new one
xlApp = New Excel.Application
End Try
Dim xlWBName As String = "2011.1004.Compensation Template"
Dim xlBookPath As String = Path.Combine(Directory.GetCurrentDirectory())
xlApp.Visible = True
Try
'get the opened workbook
xlBook = xlApp.Workbooks(xlWBName & ".xlsx")
Catch ex As Exception
'open it
xlBook = xlApp.Workbooks.Open(xlBookPath & "\" & xlWBName & ".xlsx")
End Try
xlSheet = CType(CType(xlBook.Sheets(sheetName), Excel.Worksheet), Excel.Worksheet)
frmNavigation.Close()
xlSheet.Activate()
DashboardView()
frmWelcomePage.Hide()
chkForm()
End Sub
Here is the code I use on the Close button on my form:
Sub closeXLApp()
'This sub is called to close the application without
'saving any changes to the workbook. The sub closes
'the app, workbook and sheet and performs some garbage clean up
'as well making sure that the opened Excel instance is cleared from memory.
xlBook.Close(SaveChanges:=False)
xlApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
xlSheet = Nothing
xlBook = Nothing
xlApp = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
End Sub