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();

---

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.

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

Quote:

---

Originally Posted by goisve

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.

I met the same problem before. What I did is find the process in process list and kill it.

Quote:

---

Originally Posted by goisve

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 :D
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 ?

Quote:

---

Originally Posted by jasonli

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.:D

Quote:

---

Originally Posted by nelo

I like it. Straight to the point.:D

---

Yes, :D 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. :cool: Thk you, end of service. :lol: :eek:

Quote:

---

Originally Posted by JonnyPoet

Yes, :D 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. :cool: Thk you, end of service. :lol: :eek:

---

Very good point. You are wise.

[QUOTE=JonnyPoet;1833275]

Quote:

---

Originally Posted by goisve

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 :D
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!

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

Quote:

---

Originally Posted by nelo

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?

[quote=goisve;1833458]

Quote:

---

Originally Posted by JonnyPoet

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.

Quote:

---

Originally Posted by goisve

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?
-

Quote:

---

Originally Posted by JonnyPoet

Yes, :D 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. :cool: Thk you, end of service. :lol: :eek:

---

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.

Quote:

---

Originally Posted by jasonli

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.

---

How can you be sure it is that excel process started by your code. ? Very dangerous workaround.
It easily can happen that one wants to terminate your program just while he is doing some other work just in an excel file.
He closes your application and his work in the excel application is also shut down ( withut storing his work in before as it was simple killed :sick:

Happy Sunday my dear. No sorry, this is impossible. Two possibilities: A) We have to find why it doesn't correctly close or
b) dont use excel for this job.

I finally managed to solve the problem. I used subroutines for opening, saving and checking the excel file and this fixed the problem. I can't explain why, but as long as this works it's fine by me!!

Quote:

---

Originally Posted by goisve

I finally managed to solve the problem. I used subroutines for opening, saving and checking the excel file and this fixed the problem. I can't explain why, but as long as this works it's fine by me!!

---

;) Wasn't this what I suggested ? Look here :) post #6

Quote:

---

Originally Posted by JonnyPoet

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.

---

Great you have got it working without some strange workarounds.

Quote:

---

Originally Posted by JonnyPoet

;) Wasn't this what I suggested ? Look here :) post #6Great you have got it working without some strange workarounds.

---

Yes, Jonny your post really helped me out and I 'm grateful for that. Sorry I forgot to mention it!

It's not funny to kid the others.

Nice post....

Thanks to all for the work-arounds...

But I have tried to kill the Excel application right after using it...
And it worked amazingly...!!!

My code is in VB.net.... but you can try the same in C# as well....

Dim wBook As Excel._Workbook = Nothing
Try
wBook = GetObject(path)
'in c# use [wBook = System.Runtime.InteropServices.Marshal.BindToMoniker(path)]
'write code to manipulate the WorkBook

Catch ex As Exception
Throw ex
Finally
If Not wBook Is Nothing Then

For Each wSheet As Excel.Worksheet In wBook.Worksheets
System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet)
Next

wBook.Close()
While System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook) > 0

End While
wBook = Nothing
End If
End Try

The only trick is....
I have used the interface Excel._WorkBook instead of Excel.WorkBook.

You can try....

Trying to kill the Excel process isn't a safe method. what is the gurantee that you are killing the exact excel application you had instantiated , there might be chance that you end up killing some one else's excel application . Isn't it ??