Using Excel with VB.net

[partyk1d24]

For all of you that have been having trouble getting VB.net to work right with the Excel interops it can be a bit of a bear so I will post the code I got made to get it to work right. It is a simple example but it may help som people

Imports Excel = Microsoft.Office.Interop.Excel...

    Private Sub getInfo()
        'declares the variables
        Dim excelApp As New Excel.Application
        Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add(System.Reflection.Missing.Value)
        Dim partySheet As Excel.Worksheet = Nothing
        'try to open the workbook and a worksheet
        Try
            excelBook = excelApp.Workbooks.Open("C:\Program Files\DISClient\Templates\TemplateMerge.xls")
            partySheet = excelBook.Worksheets("MergeData")
        Catch ex As Exception
            MsgBox(ex)
        Finally
            'MAKE SURE TO KILL ALL INSTANCES BEFORE QUITING! if you fail to do this
            'The service (excel.exe) will continue to run
            NAR(partySheet)
            excelBook.Close(False)
            NAR(excelBook)
            excelApp.Workbooks.Close()
            NAR(excelApp.Workbooks)
            'quit and dispose app
            excelApp.Quit()
            NAR(excelApp)
            'VERY IMPORTANT
            GC.Collect()
        End Try
    End Sub
    Private Sub NAR(ByVal o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch ex As Exception
        Finally
            o = Nothing
        End Try
    End Sub

Thanks to the forums for helping me to get it to work

[toecutter]

excellent ..i was looking for some code like this a while back
cheers m8

[gurpreet]

Please help me release excel from memory in this simple example.

objExcel = New Excel.Application


bExcelOpen = True
objExcel.DisplayAlerts = False
objExcel.AlertBeforeOverwriting = False
objWorkbook = objExcel.Workbooks.Add(My.Application.Info.DirectoryPath & "\Templates\TestTemplate.xls")
System.GC.Collect()
System.GC.WaitForPendingFinalizers()
objWorkbook.Close()

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objWorkbook)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objExcel.Workbooks)
objWorkbook = Nothing

objExcel.Quit()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objExcel)
objExcel = Nothing

[gurpreet]

The program I submitted releases excel if I close my application.
My problem is that the application is a 24x7 and may not close in months.
Any help/suggestion will be highly appreciable.
Thanks

[joewmaki]

Gurpreet, your code works fine for me. Created a simple form with your code run from a button. Code runs, excel opens, code completes. There is no instance of excel running. Could you be instantiating it somewhere else in your application?

[wesgoad]

Brilliant and simplistic! Every other coder on the planet wants to make it more complicated. Thanks!

[JBourgeois]

Unless you really know what you are doing, do not call the garbage collector (the lines of code calling System.GC) in your applications.

The garbage collector is a complex thing and calling it can have effects you did not want.

First of all, it is used to control managed objects (.NET objects). Excel being an ActiveX object, it is not cleared from memory when you call GC.Collect. In fact, doing so can have a detrimental effect. Because GC does not know the references held by or to Excel, in calling GC on the interop you risk closing a copy of Excel that is used by other applications or manually by the user.

It's been my experience that the garbage collector is global in the .NET environment, and does not work per application. In calling it, you temporarily freeze all the running .NET applications while the GC clean up the memory. And temporarily can be quite a long time as far as the user is concerned.

[kbord_jockey]

Thought I would post an addition to whoever was trying to get this to work as it was very frustrating and I spent hours on these forums and msdn researching.

This is a really basic example but the keys to me were to ensure you had the loop for releasing the com objects and also that you used Excel.Workbooks in addition to Excel.Workbook.

I got this to work without calling the Garbage collector, as the jury is still out on whether or not that is recommended. Best of luck!

Check out this msdn link as well:

http://support.microsoft.com/kb/317109

Dim xlApp As Excel.Application          'Excel Template Application
    Dim xlWbs As Excel.Workbooks            'Excel Application workbooks
    Dim wbTemplate As Excel.Workbook        'Template Workbook
    Dim wsTemplateSheet As Excel.Worksheet        'Template Sheet
    Dim wsRange As Excel.Range

public sub XLMain()

dim strReportPath as string = "C:\Some Directory\Excel is in\and junk.xls"

                xlApp = New Excel.Application

                xlWbs = xlApp.Workbooks

                wbTemplate = xlWbs.Open(strReportPath, , True)

'Now Excel is open, do stuff
'My code used references to template and named ranges

'Close Excel:

closeExcel  'Call Close Excel Sub

End Sub

'Functions/Subs

Public Sub RCO(ByVal obj As Object)
        Try
            Dim intRel As Integer = 0
            Do
                intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            Loop While intRel > 0
            msgbox("Final Released obj # " & intRel)
        Catch ex As Exception
            msgbox("Error releasing object" & ex.ToString)
            obj = Nothing
        End Try
    End Sub

Private Sub CloseExcel()
        'Sub to ensure Excel has been properly closed out

        'Close out Range
        Try
                RCO(wsRange)
                wsRange = Nothing
        Catch

        End Try

        'Close out Worksheet
        Try
                RCO(wsTemplateSheet)
                wsTemplateSheet = Nothing
        Catch

        End Try


        'Close out Workbook
        Try
            'wbTemplate.Close(False)
            RCO(wbTemplate)
            wbTemplate = Nothing
        Catch

        End Try


        'Close out Workbooks
        Try
            xlWbs.Close()
                RCO(xlWbs)
                xlWbs = Nothing
        Catch

        End Try

        'Close out Excel App
        Try
                xlApp.Quit()
                RCO(xlApp)
                xlApp = Nothing
        Catch

        End Try

'Could call GC.Collect at this point if needed....

    End Sub

[dymarski]

Hi kbord_jockey,

Thanks for yr post, it really does the job when the object is to be released after having closed it from the application.

What I actually have is a slightly different scenario:
I pass some data to an Excel workbook and I need to keep Excel open as the user operates the exported data at his own.
The application that sends data to Excel may remain running 24/7 but the user often quits Excel upon data are handled as saved.
Still with this scenario Excel would keep staying in memory even after beeing closed by the user unless the .net aplication is closed.

To have it coded:

Private Sub XlSendData()
    Dim locExcel As Excel.Application = New Excel.Application
    Dim locWorkbook As Excel.Workbook = locExcel.Workbooks.Add
    Dim locWorksheet As Excel.Worksheet = locWorkbook.Worksheets(1)

    ' Start sending data to Excel
    '
    ' Finish sending data to Excel

    ' Set Excel visible so that the user can work with it
    locExcel.Visible = True

    ' At this point Excel must be running but the sub is to release the references
    Try
      System.Runtime.InteropServices.Marshal.FinalReleaseComObject(locWorksheet)
      System.Runtime.InteropServices.Marshal.FinalReleaseComObject(locWorkbook)
      System.Runtime.InteropServices.Marshal.FinalReleaseComObject(locWorkbook)
      System.Runtime.InteropServices.Marshal.FinalReleaseComObject(locExcel)

    Catch ex As Exception
      locWorksheet = Nothing
      locWorkbook = Nothing
      locExcel = Nothing

    End Try

    ' The user is still working with Excel
    ' This application will keep running for many more hours / days
    ' When the user closes Excel it remains in memory until this application is closed
End Sub

Have you got an idea on how to let Excel release the memory in this scenario?

Rgrds
Viaceslav

Originally Posted by kbord_jockey

Thought I would post an addition to whoever was trying to get this to work as it was very frustrating and I spent hours on these forums and msdn researching.

This is a really basic example but the keys to me were to ensure you had the loop for releasing the com objects and also that you used Excel.Workbooks in addition to Excel.Workbook.

I got this to work without calling the Garbage collector, as the jury is still out on whether or not that is recommended. Best of luck!

Check out this msdn link as well:

http://support.microsoft.com/kb/317109