how to properly close excel app using visual basic/visual studio

Question

ReleaseComObject(Excel) leaves a background process running that

GC.Collect() doesn't remove.

Code that doesn't work:

        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet) : xlWorkSheet = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook) : xlWorkBook = Nothing
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp) : xlApp = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()

Answer 1

Hi @Paul Bedard ,

Make sure that all references to Excel objects have been freed or set to Nothing before closing Excel.

Be sure to Catch and handle any potential exceptions. If an exception occurs while releasing an Excel object, it may cause Excel to fail to close properly.

Best Regards.

Jiachen Li

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If you are using .NET Framework, try NetOfficeFw.Excel.

https://www.nuget.org/packages/NetOfficeFw.Excel/

Imports Excel = NetOffice.ExcelApi

Using xlApp = New Excel.Application
    Try
        Dim xlWorkBook = xlApp.Workbooks.Open("test.xlsx")
        Dim xlWorkSheet = xlWorkBook.Worksheets(1)
    Finally
        xlApp.Quit()
    End Try
End Using

Answer 3

    Private Sub Load_data()
        Dim ICsn As Integer = 1
        Dim Sps As Integer 'Number of sweep points
        Dim Cnum As Integer = 1 ' Column number



        Dim xlApp As Excel.Application = New Excel.Application()
        Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(TempPath + "\" + TemplateName1 + ".xlsx")
        xlApp.Visible = False
        Dim xlWorkSheets As Excel.Sheets = xlWorkBook.Worksheets
        Dim xlWorkSheet As Excel.Worksheet '= CType(xlWorkSheets("UUT_" + CStr(ICsn)), Excel.Worksheet)

        For ICsn = 1 To qty
            xlWorkSheet = CType(xlWorkSheets("UUT_" + CStr(ICsn)), Excel.Worksheet) 'xlWS = xlApp.Worksheets("UUT_" + CStr(ICsn))         ' Select the worksheet based on IC number. 
            xlWorkSheet.Cells(54, 1) = ICnum(ICsn - 1) 'xlWB.Cells(54, 1) = ICnum(ICsn - 1)

            For Cnum = 1 To NumCol                                          ' Column sweep to enter data from pin combinations
                'MessageBox.Show(Rnum)
                For Sps = 1 To 51                                          ' Difference is the inclusive number of sweep data points
                    xlWorkSheet.Cells(Sps + 1, Cnum * 2) = rxmsg(ICsn - 1, Cnum - 1, Sps - 1)    ' Row Sweep to enter data
                Next Sps
            Next Cnum
        Next ICsn

        xlWorkBook.SaveAs(SavePath + "\" + Fname)
        xlWorkBook.Close(SavePath + "\" + Fname)
        xlApp.Quit()


        ' CLEAN UP. (CLOSE INSTANCES OF EXCEL OBJECTS.)
       
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) ' : xlApp = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook) ' : xlWorkBook = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet) ' : xlWorkSheet = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()


    End Sub

Answer 4

If you want to call GC.Collect, I think it's a good idea to do it like this:

Private Sub Load_Data()
    Load_Data_Internal()
    GC.Collect()
    GC.WaitForPendingFinalizers()
End Sub

Private Sub Load_Data_Internal()
    ' Excel operations
    ' If you make all variables local
    ' There is no need to call ReleaseComObject here.
End Sub

---

The following are the causes of release leaks:

(1) If there are two periods in one line, a release leak will occur.

    Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(TempPath + "\" + TemplateName1 + ".xlsx")

Dim xlWorkBooks As Excel.Workbooks = xlApp.Workbooks
Dim xlWorkBook As Excel.Workbook = xlWorkBooks.Open(TempPath + "\" + TemplateName1 + ".xlsx")
      ・
      ・
      ・
Marshal.ReleaseComObject(xlWorkBook)
Marshal.ReleaseComObject(xlWorkBooks)

(2) Sheets object is not released

    Dim xlWorkSheets As Excel.Sheets = xlWorkBook.Worksheets

(3) When extracting a Sheet object from a WorkSheets object

        For ICsn = 1 To qty
	        xlWorkSheet = CType(xlWorkSheets("UUT_" + CStr(ICsn)), Excel.Worksheet) 
              ・
              ・
              ・
            Marshal.ReleaseComObject(xlWorkSheet)
        Next ICsn

(4) When the Worksheet.Cells property returns a Range object

        xlWorkSheet.Cells(54, 1) = ICnum(ICsn - 1)

        xlWorkSheet.Cells(Sps + 1, Cnum * 2) = rxmsg(ICsn - 1, Cnum - 1, Sps - 1)

        Dim xlCells1 As Excel.Range = xlWorkSheet.Cells
        Dim xlRange1 As Excel.Range = xlCells1(54, 1)
        xlRange1.Value = = ICnum(ICsn - 1)
        Marshal.ReleaseComObject(xlCells1)
        Marshal.ReleaseComObject(xlRange1)

        Dim xlCells2 As Excel.Range = xlWorkSheet.Cells
        Dim xlRange2 As Excel.Range = xlCells2(Sps + 1, Cnum * 2)
        xlRange2.Value = rxmsg(ICsn - 1, Cnum - 1, Sps - 1)
        Marshal.ReleaseComObject(xlCells2)
        Marshal.ReleaseComObject(xlRange2)

(5) Worksheet object has already been released (3)

    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)
    'System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet)
    Marshal.ReleaseComObject(xlWorkSheets)