0

I have a desktop winform application that opens excel workbooks. My question is, when the user open the excel workbook, the user will be doing stuff to the workbook then save ect. At which point should I release the com object of that excel or should i leave it open? If i leave it open, the file may get stuck in the heap or whatever you call it and therefore that file cannot be open. Is this the proper way opening and closing it?

I don't have any issues with excel closing the application, i'm trying to debug a situation with excel and figuring out if it's the application opening excel that could be causing excel to become corrupt or not responding anymore

Public Shared xlsApp3 As Excel.Application
Public Shared xlsWB3 As Excel.Workbook

Public Shared Sub releaseObject(ByVal obj As Object)
    Try
        Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Try
End Sub

Public openExcel()
    Dim xlsApp3 As New Excel.Application
    Dim xlsWB3 As Excel.Workbook
    xlsApp3.Visible = True
    xlsWB3 = xlsApp3.Workbooks.Open("c:\myExcel.xlsm")

    'Will it hurt to release the xlsWB3 & xlsApp3 even though the user has not close the workbook itself?
    releaseObject(xlsWB3)
    releaseObject(xlsApp3)
    xlsWB3 = Nothing
    xlsApp3 = Nothing
End Sub
ThisGuyJustNeedsHelp
  • 115
  • 11
  • If you figure out how to release *all* of the objects then Excel will terminate so cannot keep a lock on anything. Actually ensuring that you do is however rarely accomplished, it is not in this sample code. Get rid of the ReleaseComObject() calls and call GC.Collect() only once, be sure to do so in the method that calls openExcel(). Watch out for those Shared variables (do avoid), you have to set them to Nothing explicitly. – Hans Passant Sep 19 '18 at 13:27
  • @HansPassant There's quite a lot of advice out there on Excel suggesting that it may be necessary to call ReleaseComObject explicitly. For example, https://stackoverflow.com/questions/48384650/vb-net-excel-process-wont-close-no-matter-what – Craig Sep 19 '18 at 13:30
  • That is not correct advice. The only way you'll see that those ReleaseComObjects() are unnecessary and risky is to actually try it for yourself. – Hans Passant Sep 19 '18 at 13:37
  • I'm not sure if this is related to open excel or not but some of our open excel files from this method randomly is not able to open after save and closing the workbook. That is why i'm wondering if closing the com object then later saving the workbook is causing the workbook to lock up inside somewhere where it can't be open or is it just an bug from excel that the workbook got corrupted somehow and not even related to this at all. – ThisGuyJustNeedsHelp Sep 19 '18 at 13:45
  • @HansPassant my answer to the linked question *always* works though, and I made all my Excel code in .NET follow that pattern with no issue. How is it risky? – djv Sep 19 '18 at 14:05
  • Forgetting just one release call is enough to fail, no way to debug which one it might be. Extra risky in vb.net and c# version >= 4, they allow expressions that doesn't expose an Excel interface reference, like Cells[x, y]. It is not like you can't make it work, you just can't debug it when it doesn't work. The code in this snippet is a pretty good example, it cannot be debugged at all since it fails to release Excel when a debugger is used. – Hans Passant Sep 19 '18 at 14:16
  • https://stackoverflow.com/a/17131389/17034 – Hans Passant Sep 19 '18 at 14:17
  • after opening and releasing com object, when i'm done working on the excel workbook that i released, when i close the window, in my task manager i see the excel process pop back in there? – ThisGuyJustNeedsHelp Sep 19 '18 at 14:39

0 Answers0