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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

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

  1. #1
    Join Date
    Mar 2009
    Posts
    67

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

    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.

    Code:
    ''' <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

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Excel might still be active because you have not saved the workbook. Most likely it has poped up a dialog asking; do you want to save?

    If you close the workbook, the close method allows you to choose to save or not. This will then prevent excel from asking you when you exit.

    Also, and I don't know if it matters, but I usually free up my excel objects first.
    ...
    First I free up all object references to Cells, Ranges, Worksheets, etc..
    Next I close all workbooks and then free up their object references.
    Finally I exit the excel app and free up it's object reference.

    Hope this helps!!

  3. #3
    Join Date
    Feb 2004
    Location
    Longueuil, Québec
    Posts
    577
    You give back the control to the user when you set the Visible and UserControl properties to True. When this is the case, you lose the capability to release Excel programmatically. It is then the user's task to close Excel.
    Jacques Bourgeois
    JBFI
    http://www3.sympatico.ca/jbfi/homeus.htm

Similar Threads

  1. Replies: 12
    Last Post: 03-09-2010, 10:51 AM
  2. Replies: 1
    Last Post: 08-19-2006, 05:15 PM
  3. C# & .NET
    By Vikas Garg in forum Careers
    Replies: 18
    Last Post: 07-13-2002, 12:58 PM
  4. wot is CLR
    By lostguy in forum .NET
    Replies: 8
    Last Post: 04-03-2002, 07:41 PM
  5. Replies: 2
    Last Post: 11-16-2000, 02:11 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center