Excel process continues running (.Net Framework 3.5, VS.Net 2008, VB.Net 2008).

Question

0.00/5 (No votes)

See more:

Hi, I'm calling the following method from a button click event to export a datatable to excel. After the export is completed, the excel application object is quit, released and assigned to nothing. But in reality it's not getting released and stays active unless the entire application is closed. So every time the button is clicked for export, a new excel application object keeps on running. How can I solve this? Please help. Regards.

The problem doesn't occur if two of the lines from the method below are not used. But I can't omit them as they are really needed. Check the * marked lines.

''' <summary>
    ''' Exports data from a datatable to excel.
    ''' </summary>
    Friend Shared Sub ExportToExcel(ByVal dtbl As DataTable)
        Dim exa As Excel.Application = Nothing
        Dim wkb As Excel.Workbook = Nothing
        Dim wks As Excel.Worksheet = Nothing
        Dim intColIndex, intRowIndex As Integer
        intColIndex = 0 : intRowIndex = 2

        Try
            exa = New Excel.Application
            exa.SheetsInNewWorkbook = 1
            wkb = exa.Workbooks.Add
            wks = wkb.ActiveSheet

            For intColIndex = 1 To dtbl.Columns.Count
                wks.Cells(1, intColIndex) = dtbl.Columns(intColIndex - 1).ColumnName
            Next

            For Each row As DataRow In dtbl.Rows
                For intColIndex = 1 To dtbl.Columns.Count
                    wks.Cells(intRowIndex, intColIndex) = row(intColIndex - 1)
                Next

                intRowIndex += 1
            Next

            For intColIndex = 1 To dtbl.Columns.Count
                wks.Range(wks.Cells(1, intColIndex), wks.Cells(1, intColIndex)).Font.Bold = True
                wks.Range(wks.Cells(1, intColIndex), wks.Cells(1, intColIndex)).Font.Underline = True
            Next

		'***** The problem doesn't occur if the following two lines are not used *****
            wks.Range(wks.Cells(1, 1), wks.Cells(dtbl.Rows.Count + 1, dtbl.Columns.Count)).Columns.WrapText = False
            wks.Range(wks.Cells(1, 1), wks.Cells(dtbl.Rows.Count + 1, dtbl.Columns.Count)).Columns.AutoFit()
		'*****************************************************************************

            exa.Visible = True
            exa.UserControl = True

            If Not exa Is Nothing Then exa.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wks)
            wks = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wkb)
            wkb = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exa)
            exa = Nothing
        Catch ex As Exception
            wks = Nothing
            wkb = Nothing
            exa = Nothing
            MsgBox("The following error has occurred:" & vbCrLf & ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            GC.Collect()
        End Try
    End Sub

Posted 15-Apr-11 3:56am

priyamtheone

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manfred Rudolf Bihy · Accepted Answer · 2011-04-15T04:12:00

First close/save all your Excel objects:

pseudo

//wks.Save() is optional
wks.Close()
//wkb.Save() is optional
wkb.Close()

Marshal.ReleaseComObject(wks)
Marshal.ReleaseComObject(wkb)

wks = Nothing
wkb = Nothing

exa.Quit()
Marshal.ReleaseComObject(exa)
exa = Nothing

GC.Collect()
GC.WaitForPendingFinalizers()

That should do the trick!

Regards,

-MRB

Dylan Morley · Accepted Answer · 2011-04-15T04:24:00

Have a look at my answer[^] to a similar issue

I included code for an ExcelWrapper class that performs process cleanup

I've also explained a common reason for why Excel instances hang around...accidentally incrementing COM references by accessing properties by their collections.

I know it's in C#, you could port to VB quite easily though

Excel process continues running (.Net Framework 3.5, VS.Net 2008, VB.Net 2008).

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0