Click here to Skip to main content
12,701,854 members (34,130 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB 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 16:49pm
qbndl8403
Comments
TnTinMn 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.170118.1 | Last Updated 11 Nov 2013
Copyright © CodeProject, 1999-2017
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