Excel process remains in task manager when using OleDB
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Excel process remains in task manager when using OleDB

  1. #1
    Join Date
    Mar 2009
    Posts
    7

    Excel process remains in task manager when using OleDB

    Hi everyone,
    I'm working on a project where i need to check the values within an existing excel file.
    The problem is the the Excel.exe process is still active in task manager even after I release all the appropriate COM objects I've created. I've searched a lot for this issue and it seems that
    da.Fill(dt) is the problem.
    Here is the code.
    Any suggestions??


    Code:
                      conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dlg1.FileName + ";Extended Properties=\"Excel 8.0;HDR=YES\"");            
                                dt = new DataTable();
                                ExcApp = new Excel.Application();
                                ExcApp.DisplayAlerts = false;
                                Excel.Workbooks _Wb = ExcApp.Workbooks;
                                wb = _Wb.Open(dlg1.FileName, 0, false, 5,
                                 "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,
                                  0, false, false, false);
    
                                Excel.Sheets _Sheet = wb.Worksheets;
                                Excel.Worksheet _WSheet = (Excel.Worksheet)_Sheet[1];// = (Excel.Worksheet)wb.Worksheets[1];//xcel.Worksheet(wb.WorkSheets("Sheet1"));
                                Excel.Range _range = _WSheet.UsedRange;
                                string name = _WSheet.Name;
                                // _range.Tab
                                cmd = string.Format("Select * from [{0}$]", name);
                                //  OleDbCommand c = new OleDbCommand(cmd, conn);
                                da = new OleDbDataAdapter(cmd, conn);
    
                                da.Fill(dt);
    
                                
                             //do something with the datatable
    
                                
    
                                dlg1.FileName = dlg1.FileName + ".txt";
                                wb.Save();
                                wb.SaveAs(dlg1.FileName, Excel.XlFileFormat.xlUnicodeText, "", "", true, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlOtherSessionChanges,
                                false, false, Type.Missing, Type.Missing);
                                wb.Save();
                                ExcApp.DisplayAlerts = true;
    
                                #region Dispose region
                                dt.Dispose();
                                dt = null;
                                cmd = null;
                                conn.ConnectionString = null;
                                conn.Dispose();
                                conn = null;
                                da.SelectCommand.Dispose();
                                da.Dispose();
                                da = null;
                              
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(_range);
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(_Sheet);
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(_WSheet);
                                wb.Close(true, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                                _Wb.Close();
                                ExcApp.Workbooks.Close();
                           
                                ExcApp.Quit();
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(_Wb);
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcApp.Workbooks);
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcApp);
                                _range = null;
                                _Sheet = null;
                                _WSheet = null;
                                wb = null;
                                _Wb = null;
                                ExcApp = null;
                                GC.Collect();
                                GC.WaitForPendingFinalizers();

  2. #2
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: Excel process remains in task manager when using OleDB

    I would advise to use the 'using' syntax. All object that are disposable can use this syntax.

    Maybe if you call Dispose() only is not enough: for example, when using a StreamWriter, this should be flushed en closed first before disposing. When the 'using' syntax is used, this is done automaticcally.

    Thus
    Code:
    StreamWriter sw = new StreamWriter("myfile.txt");
    sw.WriteLine("test");
    sw.Flush();
    sw.Close();
    sw.Dispose();
    is replaced by
    Code:
    using (StreamWriter sw = new StreamWriter("myfile.txt")){
       sw.WriteLine("test");
    }
    maybe in your code something should be closed (or flused) before it is disposed. I don't know Excel namespace that well.

    Check if there is some Close() method on the Excel objects or just use the 'using' syntax.

  3. #3
    Join Date
    Mar 2009
    Posts
    7

    Re: Excel process remains in task manager when using OleDB

    Thank you for your reply,
    I have used the using syntax but nothing seems to change..
    Any other suggestions?

  4. #4
    Join Date
    Nov 2002
    Location
    .NET 3.5 VS2008
    Posts
    1,039

    Re: Excel process remains in task manager when using OleDB

    Quote Originally Posted by goisve View Post
    Thank you for your reply,
    I have used the using syntax but nothing seems to change..
    Any other suggestions?
    I've not examined what you are doing in great detail. But it seems to me that you don't actually need to use the Excel application at all. You can do all of it with ADO.NET and the OLEDB drivers (including saving to a new file). Not using the Excel application would bypass your problem.
    Last edited by nelo; April 15th, 2009 at 10:15 AM. Reason: More information.

  5. #5
    Join Date
    Dec 2005
    Location
    Waterloo ON
    Posts
    545

    Re: Excel process remains in task manager when using OleDB

    I met the same problem before. What I did is find the process in process list and kill it.
    The difficulty is that you have no idea how difficult it is.

    .Net 3.5/VS 2008

  6. #6
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,537

    Re: Excel process remains in task manager when using OleDB

    Quote Originally Posted by goisve View Post
    Hi everyone,
    I'm working on a project where i need to check the values within an existing excel file.
    The problem is the the Excel.exe process is still active in task manager even after I release all the appropriate COM objects I've created. I've searched a lot for this issue and it seems that
    da.Fill(dt) is the problem.
    Here is the code.
    Any suggestions??
    Have you already tried to use the dataAdapter itself in a 'using' clause
    Code:
     using(OleDbDataAdapter  da = new OleDbDataAdapter(cmd, conn)){
        da.Fill(dt);
    }
    My generell experience with excel and word is
    a) if you can avoid calling them... avoid it
    b) If you cannot avoid to use one of them then use small subroutines where you only do the specific needed actions for example one subroutine to open the application, another one to use the worksheets .. use the needed try - catch routines in this subroutines so you can differentiate between cases where the application for any reason wan't opened and cases where you didn't get access to the needed Worksheet, because the problems I have seen in my applications had been that there had been for examples problems anywhere in the code so an error disables to d what was wanted to do and in such cases the application wasn't closed properly so it (Excel.exe) remains in the process List. Thus then created additional problems when trying to execute the application again.
    Handling in steps corrected the issue as for example: failed to open.... unnecessary to close. But all this IMHO needs a very well arranged exceptions handling.

    BTW Why do you have thre save() methods one after the other ?
    Code:
    wb.Save();
    wb.SaveAs(dlg1.FileName, Excel.XlFileFormat.xlUnicodeText, "", "", true, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlOtherSessionChanges, false, false, Type.Missing, Type.Missing);
    wb.Save();
    Whats behind that ?
    Last edited by JonnyPoet; April 21st, 2009 at 05:53 AM.
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

  7. #7
    Join Date
    Nov 2002
    Location
    .NET 3.5 VS2008
    Posts
    1,039

    Re: Excel process remains in task manager when using OleDB

    Quote Originally Posted by jasonli View Post
    I met the same problem before. What I did is find the process in process list and lkill it.
    I like it. Straight to the point.

  8. #8
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,537

    Re: Excel process remains in task manager when using OleDB

    Quote Originally Posted by nelo View Post
    I like it. Straight to the point.
    Yes, but its no solution for selling a program which doesn't close Excel in a correct way. You cannot tell the user: so... ah... sorry... ah... if you have some troubles with my program.... so a... ah ... sorry... ups, simple go and kill the open Excel thread and all will be fine again. Thk you, end of service.
    Last edited by JonnyPoet; April 16th, 2009 at 05:49 AM.
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

  9. #9
    Join Date
    Nov 2002
    Location
    .NET 3.5 VS2008
    Posts
    1,039

    Re: Excel process remains in task manager when using OleDB

    Quote Originally Posted by JonnyPoet View Post
    Yes, but its no solution for selling a program which doesn't close excel in a correct way. You cannot ttell the user: so... ah... sorry... ah... if yoou have some troubles with my program.... so a... ah ... sorry... ups, simple go and kill the open excell theread and all will be fine again. Thk you, end of service.
    Very good point. You are wise.

  10. #10
    Join Date
    Mar 2009
    Posts
    7

    Re: Excel process remains in task manager when using OleDB

    [QUOTE=JonnyPoet;1833275]
    Quote Originally Posted by goisve View Post
    Hi everyone,
    I'm working on a project where i need to check the values within an existing excel file.
    The problem is the the Excel.exe process is still active in task manager even after I release all the appropriate COM objects I've created. I've searched a lot for this issue and it seems that
    da.Fill(dt) is the problem.
    Here is the code.
    Any suggestions??

    Have you already tried to use the dataAdapter itself in a 'using' clause
    Code:
     using(OleDbDataAdapter  da = new OleDbDataAdapter(cmd, conn)){
        da.Fill(dt);
    }
    My generell experience with excel and word is
    a) if you can avoid calling them... avoid it
    b) If you cannot avoid to use one of them then use small subroutines where you only do the specific needed actions for example one subroutine to open the application, another one to use the worksheets .. use the needed try - catch routines in this subroutines so you can differentiate between cases where the application for any reason wan't opened and cases where you didn't get access to the needed Worksheet, because the problems I have seen in my applications had been that there had been for examples problems anywhere in the code so an error disables to d what was wanted to do and in such cases the application wasn't closed properly so it (Excel.exe) remains in the process List. Thus then created additional problems when trying to execute the application again.
    Handling in steps corrected the issue as for example: failed to open.... unnecessary to close. But all this IMHO needs a very well arranged exceptions handling.

    BTW Why do you have thre save() methods one after the other ?
    Code:
    wb.Save();
    wb.SaveAs(dlg1.FileName, Excel.XlFileFormat.xlUnicodeText, "", "", true, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlOtherSessionChanges, false, false, Type.Missing, Type.Missing);
    wb.Save();
    Whats behind that ?

    Nothing behind that!! I left the second save method() by accident!
    I' ll follow your instructions.Hope it will work!

  11. #11
    Join Date
    Mar 2009
    Posts
    7

    Re: Excel process remains in task manager when using OleDB

    I checked today that if I remove the da.Fill(dt) command
    the Excel.exe process terminates.
    what's wrong about the fill command?
    I'm disposing all the objects (dataAdapter, dataTable, connection) that are affected

  12. #12
    Join Date
    Mar 2009
    Posts
    7

    Re: Excel process remains in task manager when using OleDB

    Quote Originally Posted by nelo View Post
    I've not examined what you are doing in great detail. But it seems to me that you don't actually need to use the Excel application at all. You can do all of it with ADO.NET and the OLEDB drivers (including saving to a new file). Not using the Excel application would bypass your problem.

    I'm modifying the Excel File and the i want to save it as a tab-delimeted text file. How can i Do that without the excel application?

  13. #13
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,537

    Re: Excel process remains in task manager when using OleDB

    [quote=goisve;1833458]
    Quote Originally Posted by JonnyPoet View Post


    Nothing behind that!! I left the second save method() by accident!
    I' ll follow your instructions.Hope it will work!
    I see I thought this was intentionally, because I have had problems with storing files too in old VB programs. So I thought you may have troubles here too.
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

  14. #14
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,537

    Re: Excel process remains in task manager when using OleDB

    Quote Originally Posted by goisve View Post
    I checked today that if I remove the da.Fill(dt) command
    the Excel.exe process terminates.
    what's wrong about the fill command?
    I'm disposing all the objects (dataAdapter, dataTable, connection) that are affected
    Have you already tried to use the 'using'
    clause as I suggested?
    -
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

  15. #15
    Join Date
    Dec 2005
    Location
    Waterloo ON
    Posts
    545

    Re: Excel process remains in task manager when using OleDB

    Quote Originally Posted by JonnyPoet View Post
    Yes, but its no solution for selling a program which doesn't close Excel in a correct way. You cannot tell the user: so... ah... sorry... ah... if you have some troubles with my program.... so a... ah ... sorry... ups, simple go and kill the open Excel thread and all will be fine again. Thk you, end of service.
    At the end of program, after everything is done, use Process class to check running process list and find excel to kill it. How do users know that excel application doesn't terminate correctly?

    In my program I open excel file and save data in it, just can't terminate the process. It just stays in process list, like a ghost. Very annoying!

    My way is just a workaround.
    The difficulty is that you have no idea how difficult it is.

    .Net 3.5/VS 2008

Page 1 of 2 12 LastLast

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
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center