VB.NET Excel Program Leaves EXCEL.EXE floating after completion

Question

I am writing a program that loops through all of the sheets in an Excel workbook and saves each sheet as its own workbook. It turned out to be a bit trickier than I expected, since the Sheet.Copy method creates a strange object (see here for the MSDN discussion that I believe is relevant: http://msdn.microsoft.com/en-us/library/ms178779.aspx).

Anyway, I found another Stack Overflow post that got me to where I am, which is essentially complete, outside of one hanging EXCEL.EXE process that is left after the program completes (check the update for one more issue that came up, but I think they are related).

Here is my code:

Imports System.Data
Imports System.IO
Imports Microsoft.Office.Interop
Imports Office = Microsoft.Office.Core
Imports xlNS = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    'Get information from text boxes
    Dim InputLocation As String
    Dim OutputLocation As String

    InputLocation = InputLoc.Text & "\" & FileName.Text

    If OutputLoc.Text = "" Then
        OutputLocation = InputLoc.Text
    Else
        OutputLocation = OutputLoc.Text
    End If

    'Make file to save files in
    ' Get date and time in filename as well
    Dim TLDateTime As String
    Dim TLDay As String
    Dim TLMonth As Integer
    Dim TLYear As Integer
    Dim TLHour As Integer
    Dim TLMinute As Integer
    Dim TLDate As String
    Dim TLTime As String
    Dim TLSecond As Integer

    TLDay = DateTime.Now.Day
    TLMonth = DateTime.Now.Month
    TLYear = DateTime.Now.Year
    TLHour = DateTime.Now.Hour
    TLMinute = DateTime.Now.Minute
    TLSecond = DateTime.Now.Second

    Dim MyDate As New DateTime(TLYear, TLMonth, TLDay, TLHour, TLMinute, TLSecond)
    Dim MyString As String = MyDate.ToString("MMMddyyyy_HHmmss")
    TLDate = TLMonth.ToString + TLDay.ToString + TLYear.ToString
    TLTime = TLHour.ToString + TLMinute.ToString
    TLDateTime = TLDate + "_" + TLTime

    Try
        Directory.CreateDirectory(OutputLocation & "\" & "Field Sales Report Graphs " & TLDateTime)
        OutputLocation = OutputLocation & "\" & "Field Sales Report Graphs " & TLDateTime
    Catch
        MsgBox("Trying to create a file that exists, please delete it. If the file does not exist check to make sure your output location exists")
    End Try

    'Open up excel file with information in it

    Dim xlApp1 As Excel.Application
    Dim locs As Excel.Workbook

    Dim exportsheet As Excel.Worksheet
    xlApp1 = New Excel.Application
    xlApp1.Visible = True
    xlApp1.Application.DisplayAlerts = False
    locs = xlApp1.Workbooks.Open(InputLocation)

    'locsws = locs.ActiveSheet
    Dim wkshtcount = locs.Worksheets.Count - 1
    Dim fileNames As New ArrayList

    For counter = 1 To wkshtcount + 1
        'identify and copy sheet to move
        exportsheet = CType(locs.Worksheets(counter), Excel.Worksheet)
        fileNames.Add(exportsheet.Name)
        exportsheet.Copy(Type.Missing, Type.Missing)

        exportsheet = xlApp1.Workbooks("Book" & counter).Sheets(1)

        exportsheet.SaveAs(Filename:=OutputLocation & "\" & fileNames(counter - 1) & ".xlsx")

        'close excel and release com objects
        System.Runtime.InteropServices.Marshal.ReleaseComObject(exportsheet)
        exportsheet = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1.ActiveWorkbook)
        xlApp1.ActiveWorkbook.Close(False)

    Next
    'close excel and release com objects
    locs.Close(False)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(locs)
    locs = Nothing
    xlApp1.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1)
    xlApp1 = Nothing


End Sub
End Class

Now I think the problem comes from the end of the loop, where I try to close the export file and the new worksheet it creates:

        'close excel and release com objects
        System.Runtime.InteropServices.Marshal.ReleaseComObject(exportsheet)
        exportsheet = Nothing
        xlApp1.Workbooks(fileNames(counter - 1)).Close(False)

I can't figure out what to do to release the ComObject for the new worksheet that is created. I have been trying all sorts of things, but it always throws a COM error when I do it and if I try to define it as nothing (like I do with exportsheet) is says that it is read only by default, so I can't do it. It seems like it should be something as simple as:

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1.Workbooks(fileNames(counter - 1)))

But that is not the case. I have tried a few variants of this and I am thinking it has to do with the MSDN link above, but I can't quite sort out what to do. So the code works for my purposes, outside of leaving one EXCEL.EXE after it is done.

As far as a test file, I am just using an Excel file with 3 sheets and I put some information on each and change the sheet name, so it is easy to see if it is working or not.

Any thoughts would be appreciated, thanks.

Update: I just turned the visibility off from my main Excel application and things still pop up, which leads me to believe that the way I am using Copy is creating a new Excel application, but I am not really sure how to reference it. If anyone knows how to turn off visibility there as well, it would be much appreciated.

Final Update: On the off chance that some poor soul ran into the same issue I was running into, the first update should solve it, but it is also important to note that the excel.exe will hang until you close the application. I am report automation code as a windows form application (so coworkers can give the file location and such) and there is going to be an excel.exe process running until you close the pop up window from the program. Maybe garbage collect does not run until you close the application window or it just hangs on to an instance of excel.exe for some other reason.

Have you tried the "Using...End Using" construct, which closes objects, ex. "Using xlApp1 = new Excel.Application"? — ron tornambe, Feb 24 '12 at 18:29
I was unaware of it. To implement it would I just wrap it around everything starting at xlApp1? I am not sure how it would go with my update that I think there is a new excel application being created by the copy method. — asjohnson, Feb 24 '12 at 18:34
From the MSDN on using, "Sometimes your code requires an unmanaged resource, such as a file handle, **a COM wrapper**, or a SQL connection." That looks promising, I will try it out and see. Thanks. — asjohnson, Feb 24 '12 at 18:44
I've tried getting it to fit in a few different ways and I think my lack of experience with it is catching up with me. I keep getting told that ReadOnly variable cannot be the target of an assignment and that it hides a vaiable in an enclosing block. — asjohnson, Feb 24 '12 at 18:56
I was just trying to get Using to work generally, so I was trying: Using xlApp1 As Excel.Application = New Excel.Application and End Using after the loop (not exactly sure where it should go, but I was trying it right after the loop). It tells me it hides a variable in an enclosing block and that it must implement system.IDisposable. — asjohnson, Feb 24 '12 at 19:45

score 3 · Answer 1 · edited Nov 19 '12 at 14:24

Below is code that works for me (NOTE order that I release objects, which is important)

xlWorkBook.Close()
xlApp.Quit()
ReleaseObject(xlWorkSheet)
ReleaseObject(xlWorkBook)
ReleaseObject(xlApp)

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

score 2 · Answer 2 · answered Feb 25 '12 at 00:18

2

You might need to do the following if you have unreferenced COM objects that are being created:

GC.Collect()
GC.WaitForPendingFinalizers()

Taken from this MSDN forum post: http://social.msdn.microsoft.com/Forums/en-US/netfxbcl/thread/cb5f7948-c229-483e-846b-a1cfbbcd86ca/

answered Feb 25 '12 at 00:18

Jared Shaver

1,339
8
12

You know, I used to have that in there (twice in a row is what I had read you are supposed to do), but I removed it due to a post I came across (it was on stack overflow, I believe, but I can't find it right now) that said unless you know how to use it, you should not be using it and that you are probably just making things worse, so I took it out. I just added it back in (tried at the end and in the loop, just in case) and I still have a hanging instance of excel.exe. Good suggestion though, I facepalmed for a second when I thought it might be because I removed that. – asjohnson Feb 27 '12 at 15:16

ron tornambe · Answer 3 · 2012-02-25T02:21:04.717

Okay, this works form me for a very simple worksheet. Let me know if you have any questions.

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    'Get information from text boxes
    Dim InputLocation As String
    Dim OutputLocation As String

    InputLocation = InputLoc.Text & "\" & Filename.Text

    If OutputLoc.Text = "" Then
        OutputLocation = InputLoc.Text
    Else
        OutputLocation = OutputLoc.Text
    End If

    'Make file to save files in
    ' Get date and time in filename as well
    Dim TLDateTime As String
    Dim TLDay As String
    Dim TLMonth As Integer
    Dim TLYear As Integer
    Dim TLHour As Integer
    Dim TLMinute As Integer
    Dim TLDate As String
    Dim TLTime As String
    Dim TLSecond As Integer

    TLDay = DateTime.Now.Day
    TLMonth = DateTime.Now.Month
    TLYear = DateTime.Now.Year
    TLHour = DateTime.Now.Hour
    TLMinute = DateTime.Now.Minute
    TLSecond = DateTime.Now.Second

    Dim MyDate As New DateTime(TLYear, TLMonth, TLDay, TLHour, TLMinute, TLSecond)
    Dim MyString As String = MyDate.ToString("MMMddyyyy_HHmmss")
    TLDate = TLMonth.ToString + TLDay.ToString + TLYear.ToString
    TLTime = TLHour.ToString + TLMinute.ToString
    TLDateTime = TLDate + "_" + TLTime

    Try
        Directory.CreateDirectory(OutputLocation & "\" & "Field Sales Report Graphs " & TLDateTime)
        OutputLocation = OutputLocation & "\" & "Field Sales Report Graphs " & TLDateTime
    Catch
        MsgBox("Trying to create a file that exists, please delete it. If the file does not exist check to make sure your output location exists")
    End Try

    'Open up excel file with information in it
    Dim xlApp1 As Excel.Application = New Excel.Application
    xlApp1.Visible = True
    xlApp1.Application.DisplayAlerts = False

    Dim locs As Excel.Workbook
    locs = xlApp1.Workbooks.Open(InputLocation)
    Dim fileNames As New ArrayList
    Dim counter As Integer = 1
    For Each ws As Excel.Worksheet In locs.Worksheets
        fileNames.Add(ws.Name)
        ws = xlApp1.Workbooks(counter).Sheets(1)
        ws.SaveAs(Filename:=OutputLocation & "\" & fileNames(counter - 1) & ".xlsx")
        'close excel and release com objects
        System.Runtime.InteropServices.Marshal.ReleaseComObject(ws)
        xlApp1.Workbooks(counter).Close(False)
        counter += 1
    Next
    System.Runtime.InteropServices.Marshal.ReleaseComObject(locs)
    locs = Nothing
    xlApp1.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1)
    xlApp1 = Nothing
End Sub

When I run it I get an error on: Using xlApp1 As IDisposable = New Excel.Application InvailidCastException was unhandled Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'System.IDisposable'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{805D7A98-D4AF-3F0F-967F-E5CF45312D2C}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). — asjohnson, Feb 24 '12 at 20:30
Dang, can you format in comments at all or does it always get so jumbled? I tried the 4 space thing for code, but no love. In any case, you don't get that castexception when you compile? — asjohnson, Feb 24 '12 at 20:31
Sadly, it looks like "using" a dead-end. IDisposable is not implemented for COM objects, which is no surprise; butit is required for Using. Let me play with your original version for a bit. This should be simple! — ron tornambe, Feb 24 '12 at 20:48
Thanks for the suggestion. I came across how you use Using with sql connections and that was informative for future sql connection use. Thanks for looking at it. I agree that it should be simple and have spent 4 hours and a few handfuls of hair to get the nice reminder that simple != easy. I do believe there is a solution out there and that it will likely be 1 line, now just to find it. — asjohnson, Feb 24 '12 at 20:55
So I tried your approach here and it does not seem to work. When I run it, it just saves the whole workbook in the first time through the loop and then exits. It looks interesting though, so I am going to see if I can merge it with my own code and get it to work. — asjohnson, Feb 27 '12 at 15:18
So I looked at your solution a bit more. I like the for each loop (was wondering how to get that to work in vb.net), but SaveAs only saves the workbook, not the individual sheet, even if you are saving a specific individual sheet. It also runs into trouble with the workbook closing, so the loop only goes through once and when I did get it to loop it runs into trouble, because it tries to reference multiple work books, but the code you have only has one. The for each loop is definitely a lot nicer than what I had, so I am going to use it and keep tinkering. Thanks. — asjohnson, Feb 27 '12 at 15:45

score 1 · Accepted Answer · edited May 23 '17 at 12:31

The solution I finally came across was to add

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1.ActiveWorkbook)
xlApp1.ActiveWorkbook.Close()

to the loop. I found this answer in the comments of The first answer on another stack overflow post. Basically, the problem I was having is that the worksheet.copy method creates a workbook object with no reference, but it turns out it can be referenced by referring to the activesheet. If you wanted to do more with it than just kick it out the door like I am, I imagine you could create a new workbook object and assign it or as the post I link to suggests, you can save it as something as well. For my puposes, it is just fine to release it after saving the worksheet I want and this removed my excel.exe hanging process.

If you want a bit more elgant code option you should check out the ron tornambe post where he does the for each loop I managed to not get right instead of the wonky thing I create. Basically you would want to use his loop in my code and you would have it all set up. Thanks as always stack overflow.

Its shocking how much taking a few days off from a problem and then coming back to it can change your approach and generate a solution. — asjohnson, Feb 27 '12 at 16:29
You might like to have a look at the NetOffice interop libraries (http://netoffice.codeplex.com) instead of the PIA libraries. They have some improved COM reference counting to deal with exactly this kind of tricky case, with the added advantage of targeting different Office versions. — Govert, Feb 27 '12 at 20:07
Thanks for the suggestion, I will take a look. Anything that makes COM references less terrible is something that I am interested in. — asjohnson, Feb 27 '12 at 21:07

VB.NET Excel Program Leaves EXCEL.EXE floating after completion

4 Answers4

Linked

Related