Visual Basics not closing excel

Question

I am working on a program that lets you enter information about a person, and the program then puts it in a Excel file. Opening Excel works fine, but I cant close it. If I open the taskmanager, there is still a instance of Excel open. I tried a lot of things, but couldnt find the answer. I am using VB.net and i have Microsoft office 2013 (if it matters).

   Private Sub opslaan_Click(sender As Object, e As EventArgs) Handles opslaan.Click
    Dim excelApp As Excel.Application
    Dim excelWB As Excel.Workbook
    Dim excelWS As Excel.Worksheet

    excelApp = CreateObject("Excel.Application")
    excelApp.Visible = False

    If My.Computer.FileSystem.FileExists(My.Settings.Location) Then
        excelWB = excelApp.Workbooks.Open(My.Settings.Location)
        excelWS = excelWB.Worksheets(1)
    Else
        excelWB = excelApp.Workbooks.Add
        excelWS = excelWB.Worksheets(1)
    End If


    If My.Computer.FileSystem.FileExists(My.Settings.Location) Then
        excelWB.Save()
    Else
        excelWB.SaveAs(My.Settings.Location)
    End If
    excelWS = Nothing
    excelWB.Close(SaveChanges:=False)
    excelWB = Nothing
    excelApp.Quit()
    excelApp = Nothing
    Me.Close()
End Sub

I hope you can help me.

Updated code - still not working

    Private Sub opslaan_Click(sender As Object, e As EventArgs) Handles opslaan.Click
    Dim excelApp As Excel.Application
    Dim excelWB As Excel.Workbook
    Dim excelWS As Excel.Worksheet

    excelApp = CreateObject("Excel.Application")
    excelApp.Visible = False

    If My.Computer.FileSystem.FileExists(My.Settings.Location) Then
        excelWB = excelApp.Workbooks.Open(My.Settings.Location)
        excelWS = excelWB.Worksheets(1)
    Else
        excelWB = excelApp.Workbooks.Add
        excelWS = excelWB.Worksheets(1)
    End If


    If My.Computer.FileSystem.FileExists(My.Settings.Location) Then
        excelWB.Save()
    Else
        excelWB.SaveAs(My.Settings.Location)
    End If
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWS)
    excelWB.Close(SaveChanges:=False)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWB)
    excelApp.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
    Me.Close()
End Sub

You do need to call `System.Runtime.InteropServices.Marshal.ReleaseComObject(...)` on each COM component before they're fully released. — Enigmativity, Jul 06 '14 at 11:21
Where do i need to place that, i tried a few different spots, and it didnt work. @Enigmativity — mielleman, Jul 06 '14 at 13:14
You should replace every `= Nothing` line with the call to `ReleaseComObject`. Assigning `Nothing` to the variables is an old VB6 trick that should be avoided in .NET. — Enigmativity, Jul 06 '14 at 13:35
To be honest I prefer using something like NPOI or openxml sdk which can create excel spread sheets without automating excel a better way to go. You don't have to deal with issues due to user having a different version of excel than you developed for — Ken Tucker, Jul 06 '14 at 14:04
@Enigmativity I tried it, but Excel was still open, I updated the post with the code (maybe i did something wrong). — mielleman, Jul 06 '14 at 14:24
@mielleman - Please don't edit your question like that. You should not update your question based on answers/comments. The question needs to stand as it was to give context. You can, however, add to your question. And that's what I've done - take a look. — Enigmativity, Jul 06 '14 at 22:50

score 0 · Accepted Answer · edited May 23 '17 at 12:20

As @Enigmativity said, you must release the com object. But not only do you need to release it, you need to call GC.Collect()... Using the GC.Collect will flush these objects and remove them from memory, that's why your seeing it in task manager.

 Private Sub ReleaseObject(ByVal obj As Object)
Try
  System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
Catch ex As Exception
    obj = Nothing
Finally
    GC.Collect()
End Try
 End Sub

You need to call this after your done with your objects...

 excelWB.Close()
 excelApp.Quit()


ReleaseObject(excelWS)
ReleaseObject(excelWB)
ReleaseObject(excelApp)

I had the same exact issue before, you can read more here..

Excel application not quitting after calling quit

Knew it would, glad to be of help. – Trevor Jul 06 '14 at 14:40 — Trevor, Jul 06 '14 at 14:40

Ken Tucker · Answer 2 · 2014-07-06T14:27:23.467

-1

Like @Enigmativity said you need to use Marshal.ReleaseConObject to close excel after you Quit excel

excelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
excelApp = Nothing

edited Jul 06 '14 at 14:27

answered Jul 06 '14 at 13:26

Ken Tucker

4,126
1
18
24

This post is being automatically flagged as low quality because it is only code. Would you mind expanding it a little to explain how it solves the problem? – gung - Reinstate Monica Jul 06 '14 at 14:06

Visual Basics not closing excel

2 Answers2

Linked