-
Jan 14th, 2011, 09:43 AM
#1
Thread Starter
Addicted Member
[RESOLVED] Too many Excel.exe tasks are opened...
Hi. I have the code below:
Code:
Dim i As Integer
Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.Workbook
objExcel.Workbooks.Add()
Dim objSheet As Excel.Worksheet
objWorkbook = objExcel.Workbooks.Open("C:\Data.xlsx")
objSheet = objWorkbook.Worksheets(1)
For i = 0 To 5
objSheet.Cells(i + 1, 10).value = 1234
Next
objExcel.DisplayAlerts = False
objExcel.AlertBeforeOverwriting = False
objExcel.SaveWorkspace()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objSheet)
objWorkbook.Close(SaveChanges:=False)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objWorkbook)
objExcel.Application.Quit()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objExcel)
At the end of this routine, eventhough the data was correctly saved to my Data.xlsx file, this routine also opened 15 EXCEL.EXE tasks in task manager.
I thought that I had cleaned up before quitting.
By the way, whether I write to 1 or 5 or 10 cells in Data.xlsx, 15 EXCEL.EXE tasks would be opened regardless.
Is there a way to (easily and) completely close all EXCEL.EXE in task manager before leaving my application?
Last edited by dtvonly; Jan 14th, 2011 at 09:47 AM.
-
Jan 14th, 2011, 09:50 AM
#2
Fanatic Member
Re: Too many Excel.exe tasks are opened...
This will kill EVERY excel process running, even those that you did not start....
Code:
Private Sub KillExcel()
For Each p As Process In Process.GetProcesses
If p.ProcessName = "Excel.exe" Then
p.Kill
End If
Next
End Sub
HTH,
D
Platforms of choice: Visual Studio 2005/2008 Professional : Visual Studio 2010 Enterprise : PHP - Notepad++/WAMP
Please Rate If I helped you.
Please remember to mark threads as closed if your issue has been resolved.
Reserved Words in Access | Connection Strings
-
Jan 14th, 2011, 10:19 AM
#3
Re: Too many Excel.exe tasks are opened...
Code:
objExcel.Workbooks.Add()
'and
objWorkbook = objExcel.Workbooks.Open("C:\Data.xlsx")
Are your problem. You can not "double-dot" use an interop object. Apparently it declares a "hidden" variable to hold the .WorkBooks object, but you do not have a handle to remove it. I have done exactly this myself last week. You have to go all out declaring and clearing to stop it, something like this:
Code:
mobjExcel_App = New Excel.Application
mobjExcel_WorkBooks = mobjExcel_App.Workbooks
mobjExcel_WorkBook = mobjExcel_WorkBooks.Open("blah blah".xls)
mobjExcel_WorkSheets = mobjExcel_WorkBook.Worksheets
...
FinalReleaseComObject(mobjExcel_WorkSheets)
FinalReleaseComObject(mobjExcel_WorkBook)
Hopefully you get the idea, as soon as you need to double-dot, stick it into a local var first.
There may be better examples out there on the net then mine .
-
Jan 14th, 2011, 10:19 AM
#4
Thread Starter
Addicted Member
Re: Too many Excel.exe tasks are opened...
yep that worked. thank you.
-
Jan 14th, 2011, 10:21 AM
#5
Thread Starter
Addicted Member
Re: Too many Excel.exe tasks are opened...
thanks Grimfort. I will keep that in mind.
-
Jan 14th, 2011, 10:23 AM
#6
Re: Too many Excel.exe tasks are opened...
I don't see why you have got the following line:
Originally Posted by dtvonly
objExcel.Workbooks.Add()
..that creates an extra workbook that you do not use, or close.
I also don't understand what this part of the code is for:
objExcel.DisplayAlerts = False
objExcel.AlertBeforeOverwriting = False
objExcel.SaveWorkspace()
I don't think I've ever used .SaveWorkspace , and based on assumptions about what it does I don't see why you have got it.
Originally Posted by dminder
This will kill EVERY excel process running, even those that you did not start....
-
Jan 14th, 2011, 10:32 AM
#7
Re: [RESOLVED] Too many Excel.exe tasks are opened...
I ran the code you posted pretty much as is (just changing the filename) and no instances of Excel.exe were left running on my machine.
I would start your code with a clean slate and make sure you have no instances of excel running when you start your app and then see how many are being left behind. You should also consider putting your cleanup code in a finally block, to make sure that it gets executed if your app has an error.
I have seen a lot of instances of excel left open on developer machines when they forget to .Quit and when they are debugging and just stop their app in the middle of it running.
Edit: ok, I was a little late to this party, but I think some of the info I posted was still good, so i'll leave the post as is.
Last edited by Negative0; Jan 14th, 2011 at 10:34 AM.
Reason: Way behind the thread.
-
Jan 14th, 2011, 10:38 AM
#8
Re: Too many Excel.exe tasks are opened...
Originally Posted by dtvonly
thanks Grimfort. I will keep that in mind.
Killing every excel process is a REALLY bad idea, mkay. Imagine if later on you decide to multi-thread this. If your excel copy is cut off by another thread you get a whole heap of COM exceptions.
-
Jan 14th, 2011, 10:57 AM
#9
Re: [RESOLVED] Too many Excel.exe tasks are opened...
In addition to that kind of issue with it, think about the very real risk that a user has been working in Excel for over an hour without saving their work - killing the processes will destroy all of their work without even giving them a warning.
I know it isn't nice to have bugs in your code, but hiding the evidence is a bad way to deal with it (especially when doing that can damage other things, and it usually can). What you should be doing instead is trying to correct the bugs, so that the problems don't occur in the first place.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|