Click here to Skip to main content
11,570,785 members (45,090 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
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
Posted 9-Nov-13 15:49pm
qbndl8403
Comments
TnTinMn at 10-Nov-13 21:46pm
   
In your method GoToSheets, you define as local copy of xlApp. In the method closeApp, you are using a non-local xlApp definition.

Try commenting out: Dim xlApp As Excel.Application, in GoToSheets.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

In the unload event of the form write the code for closing (or killing) all the excel processes
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


Advertise | Privacy | Mobile
Web03 | 2.8.150624.2 | Last Updated 11 Nov 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100