[RESOLVED] Trouble closing EXCEL.exe COM instances

[VB.NETGAL]

Hello VB world!

This is my first post so forgive me if I don't supply enough information to my problem first time round. I'm still very much in the learning stage meaning that I have a general understanding of most of my code and part of it is made up from google searches to get me where I am with a small reporting application I'm building.

I have a function which I call below which initializes a new instance of EXCEL.exe in task manager. I'm having trouble disposing of this instance at the end of my function.

If I use

excelApplication.Quit()
        Marshal.ReleaseComObject(excelApplication)

right after I initialize the instance then it gets rid of it no problem. However at the end of my function it is not working. I have done a bit of searching on google before I came here and it basically says I have to release all instances of the com object but I can't work out what they are or if there is a better way to do it or find out. Hoping this is an easy one for someone else; it's been driving me mad for about 3 hours :O

Dim excelApplication As New excel.Application




        Dim fileInfo1 As New System.IO.FileInfo(XLSXFile)
        If fileInfo1.Exists Then
            SetAttr(XLSXFile, vbNormal)
            fileInfo1.Delete()
        End If

        Dim fxml As New System.IO.FileInfo(XMLFile)
        If fxml.Exists Then
            excelApplication.Workbooks.Open(XMLFile)
            excelApplication.Workbooks.Item(1).CheckCompatibility = True
            excelApplication.DisplayAlerts = False
            excelApplication.Workbooks.Item(1).SaveAs(XLSXFile, excel.XlFileFormat.xlWorkbookDefault)
            excelApplication.DisplayAlerts = False
            excelApplication.Workbooks.Close()
            SetAttr(XMLFile, vbNormal)
            fxml.Delete()

            'Else
            '    MessageBox.Show("XML File does not exists")
        End If

        Dim fileInfo2 As New System.IO.FileInfo(XLSXFile)
        If fileInfo2.Exists Then
            SetAttr(XLSXFile, FileAttribute.Normal)
        End If



        excelApplication.Quit()
        Marshal.ReleaseComObject(excelApplication)

Edit: The function takes an XML document and converts it to an XLSX document

[formlesstree4]

If you wanted to try breaking out of relying on Excel to do it for you, you could always try using a library that has similar functionality. As for your actual problem at hand...is there a dispose method you can call on the excelApplication object?

EDIT: There's also this library as well!

[kareninstructor]

Excel when done right is easy in regards to releasing objects yet always prone to keeping objects in memory when done wrong and it takes a good deal of time to get it right.

I agree with formlesstree4, cut the dependency of possible. Personally I depend in Aspose Cells library (expensive until you realize what it does and how much time you save, in the end it pays for itself over and over)

If you must use Excel check out my post from yesterday timestamped Tuesday, April 09, 2013 3:14 PM.

Also one (if not) the best site for Excel automation, and his page on cleaning up Excel

[VB.NETGAL]

Thanks for your reply.. those Libraries look like they could certainly be of use in the future...I will have a look tomorrow if they will be a better option to change an already generated XML doc to XLSX. I think I'd still like to get my head around the current problem though as I'm guessing it's a common issue people encounter when they first start with .NET.

excelapplication doesn't have a dispose method.. Just quit. I think what I will do tomorrow if nobody can point out exactly what I need to do is go through each bit of my code again moving

excelApplication.Quit()
        Marshal.ReleaseComObject(excelApplication)

through it adding this... as this works at first and then progress from there. Thanks for the info on those Libraries

[kareninstructor]

Thanks for your reply.. those Libraries look like they could certainly be of use in the future...I will have a look tomorrow if they will be a better option to change an already generated XML doc to XLSX. I think I'd still like to get my head around the current problem though as I'm guessing it's a common issue people encounter when they first start with .NET.

excelapplication doesn't have a dispose method.. Just quit. I think what I will do tomorrow if nobody can point out exactly what I need to do is go through each bit of my code again moving

excelApplication.Quit()
        Marshal.ReleaseComObject(excelApplication)

through it adding this... as this works at first and then progress from there. Thanks for the info on those Libraries

You already have been pointed in the right direction, let me push a tad harder The article below is referenced in one of the links in my first reply in this thread

Basics of using Excel automation in VB.NET with emphasis on creating and destroy

Also Two Dot Rule

In the end it is never simple with Excel but once understood not hard at all.

[VB.NETGAL]

Hi Kevin, Thanks, I will take a look at that too. and post my results tomorrow! Trying to do my application without paying for additional COM library as it's a personal project for me

I appreciate the links, I'm sure they will be of great use! Night guys

[formlesstree4]

Hi Kevin, Thanks, I will take a look at that too. and post my results tomorrow! Trying to do my application without paying for additional COM library as it's a personal project for me

I appreciate the links, I'm sure they will be of great use! Night guys

COM is never easy in .NET Hence why managed workarounds are usually optimal. I've always tried to stay away from COM controls and look for managed versions or write my own managed version if possible. Good luck with your personal project!

[VB.NETGAL]

Hi Guys,

Just wanted to say thanks for the nudge in the right direction.

I changed the code to this at the beginning

Dim xlApp As excel.Application = Nothing
        Dim xlWorkBooks As excel.Workbooks = Nothing
        Dim xlWorkBook As excel.Workbook = Nothing

        xlApp = New excel.Application
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Add()

Followed by this at the end of the code

''''''''''''''''''''''''''''''''''''''''''''''
        xlApp.Quit()
        ''''''''''''''''''''''''''''''''''''''''''''''
        'The below releases the Excel COM object instance
        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If

        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If

        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If
        ''''''''''''''''''''''''''''''''''''''''''''''

Thanks to your tutorial Kevin and also Formlesstree input!