Closing Excel
Closing Excel
Closing Excel
Closing Excel
Closing Excel
Closing Excel Closing Excel Closing Excel Closing Excel Closing Excel Closing Excel Closing Excel Closing Excel
Closing Excel Closing Excel
Closing Excel
Go Back  Xtreme Visual Basic Talk > > > Closing Excel


Reply
 
Thread Tools Display Modes
  #1  
Old 01-10-2005, 08:10 AM
MoreLikeIt MoreLikeIt is offline
Freshman
 
Join Date: Dec 2004
Location: Netherlands
Posts: 25
Default Closing Excel


Hi,

I'm trying to write data to an excel file and after saving that close excel immediatly.

This works, but there stay's an excel process in task manager. So when another part of the program wants to open the same file it will give me an error on opening the file. It works perfectly when I manually close the processes of excel.

Now I read on this forum that is has something to do with the garbage collector. I have tried al the examples given, but I stil have the problem. But I call the same function for the second time it will open another process and when that is finished it will close the old process, that was still open.

Maybe someone has had this problem before.

Code:
Public Function FillAndSaveExcel(ByVal beschik1t3(,) As Integer, ByVal beschik4t6(,) As Integer, _ ByVal excelArray1(,) As Integer, ByVal excelArray2(,) As Integer, _ ByVal excelArray3(,) As Integer, ByVal totaalarray(,) As Integer) Dim saRet(145, 5) As Integer Dim objBooks As Excel.Workbooks Dim objSheets As Excel.Sheets Dim objSheet1 As Excel._Worksheet Dim objSheet2 As Excel._Worksheet Dim objSheet3 As Excel._Worksheet Dim objSheet4 As Excel._Worksheet Dim range1 As Excel.Range Dim range2 As Excel.Range Dim range3 As Excel.Range Dim range4 As Excel.Range Dim range5 As Excel.Range Dim range6 As Excel.Range ' Create a new instance of Excel and start a new workbook. objApp = New Excel.Application objApp.Workbooks.Open(PgS.excelTemplate) objBooks = objApp.Workbooks objBook = objBooks.Open(PgS.excelTemplate) objSheets = objBook.Worksheets objSheet1 = objSheets(1) objSheet2 = objSheets(2) objSheet3 = objSheets(3) objSheet4 = objSheets(4) 'Get the range where the starting cell has the address 'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols. range1 = objSheet1.Range("B2", Reflection.Missing.Value) range2 = objSheet2.Range("B2", Reflection.Missing.Value) range3 = objSheet3.Range("B2", Reflection.Missing.Value) range4 = objSheet4.Range("F1", Reflection.Missing.Value) range5 = objSheet4.Range("H2", Reflection.Missing.Value) range6 = objSheet4.Range("K2", Reflection.Missing.Value) range1 = range1.Resize(145, 4) range2 = range2.Resize(145, 6) range3 = range3.Resize(145, 6) range4 = range4.Resize(4, 1) range5 = range5.Resize(3, 1) range6 = range6.Resize(3, 1) range1.Value = excelArray1 range2.Value = excelArray2 range3.Value = excelArray3 range4.Value = totaalarray range5.Value = beschik1t3 range6.Value = beschik4t6 objSheet1.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden objSheet2.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden objSheet3.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden objSheet4.Protect() objApp.DisplayAlerts = False objBook.SaveAs(PgS.excelBestandsnaam & "_" & DateTime.Today.ToShortDateString.ToString & ".xls") objApp.DisplayAlerts = True 'Clean up and close range1 = Nothing range2 = Nothing range3 = Nothing range4 = Nothing range5 = Nothing range6 = Nothing objSheet1 = Nothing objSheet2 = Nothing objSheet3 = Nothing objSheet4 = Nothing objSheets = Nothing objBooks.Close() objBooks = Nothing 'objApp.Workbooks.Close() objApp.Quit() objApp = Nothing GC.Collect() End Function

Last edited by MoreLikeIt; 01-10-2005 at 08:17 AM. Reason: line breaks... please use them!
Reply With Quote
  #2  
Old 01-10-2005, 08:20 AM
herilane's Avatar
herilaneClosing Excel herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

Two things that I could spot:

1. You are opening the workbook twice, since you have two Open statements here:
Code:
objApp.Workbooks.Open(PgS.excelTemplate) objBooks = objApp.Workbooks objBook = objBooks.Open(PgS.excelTemplate)
Remove the first of these 3 lines.

2. In the code you have posted, you are not setting objBook to Nothing.

Try changing these and see if that helps.
Reply With Quote
  #3  
Old 01-10-2005, 09:02 AM
MoreLikeIt MoreLikeIt is offline
Freshman
 
Join Date: Dec 2004
Location: Netherlands
Posts: 25
Default

Hi Herilane,

Thanks for this advice.. it resolves the problem for the other functions with excel I have... but with this function theres still 1 excel process active after saved it...

I think this happens because I do a "save as" in this function. So there are 2 excel processes in this function. It works fine, but I can't figure out how to close the second process, because I not really opening it.

Code:
objBook = objBooks.Open(PgS.excelTemplate) ......... objBook.SaveAs(PgS.excelBestandsnaam & "_" & _ DateTime.Today.ToShortDateString.ToString & ".xls")

Thanks for the help on this problem.
Reply With Quote
  #4  
Old 01-10-2005, 09:07 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi MoreLikeIt,

The .SaveAs() Method is not your problem here, I promise... Best I can see, Herilane highlighted what needs fixing. Can you show us your code again, with those two corrections in-place?

Edit: Actually, also, what is your 'PgS' object, exactly? And I guess it's safe to assume that 'PgS.excelBestandsnaam' is returning a String? -- Mike]
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #5  
Old 01-10-2005, 09:12 AM
MoreLikeIt MoreLikeIt is offline
Freshman
 
Join Date: Dec 2004
Location: Netherlands
Posts: 25
Default

Yes I can of course

Code:
Public Function FillAndSaveExcel(ByVal beschik1t3(,) As Integer, ByVal _ beschik4t6(,) As Integer, ByVal excelArray1(,) As Integer, _ ByVal excelArray2(,) As Integer, ByVal excelArray3(,) As Integer, _ ByVal totaalarray(,) As Integer) Dim saRet(145, 5) As Integer Dim objBooks As Excel.Workbooks Dim objSheets As Excel.Sheets Dim objSheet1 As Excel._Worksheet Dim objSheet2 As Excel._Worksheet Dim objSheet3 As Excel._Worksheet Dim objSheet4 As Excel._Worksheet Dim range1 As Excel.Range Dim range2 As Excel.Range Dim range3 As Excel.Range Dim range4 As Excel.Range Dim range5 As Excel.Range Dim range6 As Excel.Range ' Create a new instance of Excel and start a new workbook. objApp = New Excel.Application 'objApp.Workbooks.Open(PgS.excelTemplate) objBooks = objApp.Workbooks objBook = objBooks.Open(PgS.excelTemplate) objSheets = objBook.Worksheets objSheet1 = objSheets(1) objSheet2 = objSheets(2) objSheet3 = objSheets(3) objSheet4 = objSheets(4) 'Get the range where the starting cell has the address 'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols. range1 = objSheet1.Range("B2", Reflection.Missing.Value) range2 = objSheet2.Range("B2", Reflection.Missing.Value) range3 = objSheet3.Range("B2", Reflection.Missing.Value) range4 = objSheet4.Range("F1", Reflection.Missing.Value) range5 = objSheet4.Range("H2", Reflection.Missing.Value) range6 = objSheet4.Range("K2", Reflection.Missing.Value) range1 = range1.Resize(145, 4) range2 = range2.Resize(145, 6) range3 = range3.Resize(145, 6) range4 = range4.Resize(4, 1) range5 = range5.Resize(3, 1) range6 = range6.Resize(3, 1) range1.Value = excelArray1 range2.Value = excelArray2 range3.Value = excelArray3 range4.Value = totaalarray range5.Value = beschik1t3 range6.Value = beschik4t6 objSheet1.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden objSheet2.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden objSheet3.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden objSheet4.Protect() objApp.DisplayAlerts = False objBook.SaveAs(PgS.excelBestandsnaam & "_" & DateTime.Today.ToShortDateString.ToString & ".xls") objApp.DisplayAlerts = True 'Clean up and close range1 = Nothing range2 = Nothing range3 = Nothing range4 = Nothing range5 = Nothing range6 = Nothing objSheet1 = Nothing objSheet2 = Nothing objSheet3 = Nothing objSheet4 = Nothing objSheets = Nothing objBook = Nothing objBooks.Close() objBooks = Nothing objApp.Quit() objApp = Nothing GC.Collect() End Function

I really don't understand what the problem could be then. I'm closing
everthing I think.

Last edited by MoreLikeIt; 01-10-2005 at 09:34 AM.
Reply With Quote
  #6  
Old 01-10-2005, 09:16 AM
MoreLikeIt MoreLikeIt is offline
Freshman
 
Join Date: Dec 2004
Location: Netherlands
Posts: 25
Default

Sorry I didn't answer the question...

Yes PgS.excelbestandsnaam is returning a string. This is the name that the must get with the date behind it.

The PgS object is just containing some values that are used in the program.

thanks for the help
Reply With Quote
  #7  
Old 01-10-2005, 09:31 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

[MoreLikeIt, would you mind adding Line-Breaks in your Post #5? Sorry, it's just hard to read the thread with so much left-right scrolling.]

Ok, a few more things:

(1) Add 'Option Explicit' to the top of your Document. This is the most important thing you can do. You should actually make this a Project-wide setting. Choose Alt|Project|Properties... Then choose 'Common Properties' and then 'Build'. Within that make sure that Option Explicit is set to 'On'. Then click <Apply> or <OK>.


(2) Adding 'Option Explicit' will flag two issues that you need to correct:

(a) 'objApp = New Excel.Application' will now have to be changed to: 'Dim objApp = New Excel.Application'. Get it? You must declare it first under Option Explicit and it really prevents one from making careless mistakes later. Trust me, this is a big help. Notice my signature, I "never leave home without it..."

(b) You need to add the line 'Dim objBook As Excel.Workbook'. You use the objBook, but it is never declared anywhere! Again, use 'Option Strict On' to pick up these kinds of things.


(3) Ok, the above is very important, but is not why you are hanging. I believe you are hanging because you are directly invoking the Excel Application's hidden _Global references. The hidden globals such as _Application, or _Worksheet, etc. need to be avoided at all costs. In your case, you have lines that look like the following:
Code:
Dim objSheet4 As Excel._Worksheet
I have a pretty good suspicion that if you change these lines to be:
Code:
Dim objSheet4 As Excel.Worksheet
you'll be in good shape. (That is, just remove the "_" character, and I think you'll be fine...)

Fingers crossed..!
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #8  
Old 01-11-2005, 05:13 AM
MoreLikeIt MoreLikeIt is offline
Freshman
 
Join Date: Dec 2004
Location: Netherlands
Posts: 25
Default

Hi Mike,

Thanks for that option explicit on tip.. That makes some things very clear for me...

But You were right... This is not why I'm hanging... I made the changes you suggested, but there is not any difference in the behaviour of the program.

This morning I have tested the program and went step by step through the function with the Taskbar next to it...

The problem is not that excel is started twice, but that it isn't closed the first time. (It's never closed actually). When function starts for the second time it will start another excel process and this one isn't closed either. But when the function fineshes for second time, the first excel process will be closed... This does not happen after GC.Collect, but after the end function when it returns to the function which is calling the FillandSave function.

With the other functions that I have for excel this happens also... Only then the excel process also will be closed the first time. So I don't have an open excel process running...

I will place my code again maybe you can see what is wrong... I checked 3 times if every object is set to nothing at the end.

I shall also place a function that does work. I think it's a garbage collector problem, but I think it's strange that when I call a function to close or quit excel it fails to do that.

Sorry for the long poste by the way, maybe you can see something. My eyes are tired of staring at the code.

Many thanks

Code:
Public Function FillAndSaveExcel(ByVal beschik1t3(,) As Integer, _ ByVal beschik4t6(,) As Integer, ByVal excelArray1(,) As Integer, _ ByVal excelArray2(,) As Integer, ByVal excelArray3(,) As Integer, _ ByVal totaalarray(,) As Integer) Dim objApp As New Excel.Application Dim objBook As Excel.Workbook Dim objBooks As Excel.Workbooks Dim objSheets As Excel.Sheets Dim objSheet1 As Excel.Worksheet Dim objSheet2 As Excel.Worksheet Dim objSheet3 As Excel.Worksheet Dim objSheet4 As Excel.Worksheet Dim range1 As Excel.Range Dim range2 As Excel.Range Dim range3 As Excel.Range Dim range4 As Excel.Range Dim range5 As Excel.Range Dim range6 As Excel.Range If File.Exists(PgS.excelBestandsnaam & "_" & _ DateTime.Today.ToShortDateString.ToString & ".xls") Then ' Create a new instance of Excel and start a new workbook. objBooks = objApp.Workbooks objBook = objBooks.Open(PgS.excelBestandsnaam & "_" & _ DateTime.Today.ToShortDateString.ToString & ".xls") objSheets = objBook.Worksheets objSheet1 = objSheets(1) objSheet2 = objSheets(2) objSheet3 = objSheets(3) objSheet4 = objSheets(4) objSheet4.Unprotect() Else ' Create a new instance of Excel and start a new workbook. objBooks = objApp.Workbooks objBook = objBooks.Open(PgS.excelTemplate) objSheets = objBook.Worksheets objSheet1 = objSheets(1) objSheet2 = objSheets(2) objSheet3 = objSheets(3) objSheet4 = objSheets(4) End If 'Get the range where the starting cell has the address 'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols. range1 = objSheet1.Range("B2", Reflection.Missing.Value) range2 = objSheet2.Range("B2", Reflection.Missing.Value) range3 = objSheet3.Range("B2", Reflection.Missing.Value) range4 = objSheet4.Range("F1", Reflection.Missing.Value) range5 = objSheet4.Range("H2", Reflection.Missing.Value) range6 = objSheet4.Range("K2", Reflection.Missing.Value) range1 = range1.Resize(145, 4) range2 = range2.Resize(145, 6) range3 = range3.Resize(145, 6) range4 = range4.Resize(4, 1) range5 = range5.Resize(3, 1) range6 = range6.Resize(3, 1) range1.Value = excelArray1 range2.Value = excelArray2 range3.Value = excelArray3 range4.Value = totaalarray range5.Value = beschik1t3 range6.Value = beschik4t6 objSheet1.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden objSheet2.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden objSheet3.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden objSheet4.Protect() objApp.DisplayAlerts = False objBook.SaveAs(PgS.excelBestandsnaam & "_" & _ DateTime.Today.ToShortDateString.ToString & ".xls") objApp.DisplayAlerts = True 'Clean up and close objBooks.Close() objApp.Workbooks.Close() objApp.Quit() range1 = Nothing range2 = Nothing range3 = Nothing range4 = Nothing range5 = Nothing range6 = Nothing objSheet1 = Nothing objSheet2 = Nothing objSheet3 = Nothing objSheet4 = Nothing objSheets = Nothing objBook = Nothing objBooks = Nothing objApp = Nothing GC.Collect() End Function

This is the function that works, It does not quit excel but it will be disposed when the function is finished.

Code:
Public Function FillExcelArrays(ByRef excelArray1(,) As Integer, _ ByRef excelArray2(,) As Integer, ByRef excelArray3(,) As Integer) If File.Exists(PgS.excelBestandsnaam & "_" & _ DateTime.Today.ToShortDateString.ToString & ".xls") Then Dim objApp As New Excel.Application Dim objBook As Excel.Workbook Dim objBooks As Excel.Workbooks Dim objSheets As Excel.Sheets Dim objSheet1 As Excel.Worksheet Dim objSheet2 As Excel.Worksheet Dim objSheet3 As Excel.Worksheet Dim objSheet4 As Excel.Worksheet Dim range1 As Excel.Range Dim range2 As Excel.Range Dim range3 As Excel.Range Dim excel1(144, 3) As Object Dim excel2(144, 5) As Object Dim excel3(144, 5) As Object ' Create a new instance of Excel and start a new workbook. objBooks = objApp.Workbooks objBook = objBooks.Open(PgS.excelBestandsnaam & "_" & _ DateTime.Today.ToShortDateString.ToString & ".xls") objSheets = objBook.Worksheets objSheet1 = objSheets(1) objSheet2 = objSheets(2) objSheet3 = objSheets(3) objSheet4 = objSheets(4) 'Get the range where the starting cell has the address 'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols. range1 = objSheet1.Range("B2", "E146") range2 = objSheet2.Range("B2", "G146") range3 = objSheet3.Range("B2", "G146") 'Set the range value to the array. excel1 = range1.Value excel2 = range2.Value excel3 = range3.Value Dim i, j As Integer For i = 0 To excel1.GetUpperBound(1) - 1 For j = 0 To excel1.GetUpperBound(0) - 1 excelArray1(j, i) = CInt(excel1(j + 1, i + 1)) Next Next For i = 0 To excel2.GetUpperBound(1) - 1 For j = 0 To excel2.GetUpperBound(0) - 1 excelArray2(j, i) = CInt(excel2(j + 1, i + 1)) Next Next For i = 0 To excel3.GetUpperBound(0) - 1 For j = 0 To excel3.GetUpperBound(1) - 1 excelArray3(i, j) = CInt(excel3(i + 1, j + 1)) Next Next 'Clean up objBooks.Close() objApp.Quit() range1 = Nothing range2 = Nothing range3 = Nothing objSheet1 = Nothing objSheet2 = Nothing objSheet3 = Nothing objSheet4 = Nothing objSheets = Nothing objBook = Nothing objBooks = Nothing objApp = Nothing GC.Collect() End If End Function
Reply With Quote
  #9  
Old 01-11-2005, 08:29 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by MoreLikeIt
The problem is not that excel is started twice, but that it isn't closed the first time. (It's never closed actually).
Yes, I know the behavior exactly. This is tougher to solve in .Net than it is under VB6 or VBA. But I'm not giving up yet!

Let's try one more change. Let's make your "Cleanup and Close" section look like this:
Code:
'Clean up and close range1 = Nothing range2 = Nothing range3 = Nothing range4 = Nothing range5 = Nothing range6 = Nothing objSheet1 = Nothing objSheet2 = Nothing objSheet3 = Nothing objSheet4 = Nothing objSheets = Nothing objBook.Close() objBook = Nothing objBooks = Nothing objApp.Quit() objApp = Nothing GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers()
Sorry that this has been so hard. Give this new version a try and let us know... Fingers crossed!

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #10  
Old 01-12-2005, 12:45 AM
aLwEiS aLwEiS is offline
Newcomer
 
Join Date: Mar 2002
Location: Malaysia
Posts: 16
Default

Hello ppl,

My friend found the solution, he thinks.

objExcel.Workbooks(1).Close (0)
objExcel.Quit
Set objExcel = Nothing

Rgds
__________________
Some are born mad, some achieved madness, some have madness thrust upon them
Reply With Quote
  #11  
Old 01-12-2005, 02:06 AM
MoreLikeIt MoreLikeIt is offline
Freshman
 
Join Date: Dec 2004
Location: Netherlands
Posts: 25
Default

Hi Guys,

Many many thanks for all the help you give me... but the problem is still there. I also tried you're trick aLwEiS, but it didn't help either. The behaviour of the program doesn't change a bit.

Now I did discover that the process that stays running will be closed when the program is closed. That means that the dispose function of .Net itself is killing the process when I quit the program. This makes the problem a lot less dangerous for the memory.

I don't know what I can do now, so I let it for this for a while. Maybe I can think of something next week.

When I find the solution I will let you all know of course.

see you around here..
Reply With Quote
  #12  
Old 02-08-2005, 10:01 AM
bellshapedhead bellshapedhead is offline
Newcomer
 
Join Date: Feb 2005
Posts: 6
Wink

I originally posted this solution in another forum. This solution was written to close a particular Excel instance and leave the rest undisturbed.

FYI... I am running Windows 2000 Professional, Visual Studio 2003, Office 2000 Professional. After trying for a day or two to get the instance to close I just wrote around the problem using the following procedure

1. Loop the through the open processes (before I create my instance of Excel) and add all of the process ids together (they are integers) that are associated with running Excel applications (on my machine) . I usualy have more than one open.
2. Create the instance of Excel
3. Loop through the open processes again and total process ids for open excel applications
4. subtract (1) from (3) and I now have the process id for the Excel instance that I am intereseted in.
5. When I'm done using the instance (and after I have released all objects) I use the kill method to terminate the process.

Works great!

Code segements follow:

'Add up the IDs before creating
For Each proc In Process.GetProcesses
If proc.ProcessName.ToLower = "excel" Then
t1procID = t1procID + proc.Id
End If
Next

'Create the instance
objXLApp = CType(CreateObject("Excel.Application"), Excel.Application)

'add the existing ids after excel creation
For Each proc In Process.GetProcesses
If proc.ProcessName.ToLower = "excel" Then
t2procID = t2procID + proc.Id
End If
Next
'Subtract the two and the result is the proc.id of the Excel instance that we will close when we're through
intprocID = t2procID - t1procID

'DO THINGS and then release objects

'Kill it
For Each proc In Process.GetProcesses
If proc.Id = intprocID Then
proc.Kill()
End If
Next

This is not pretty but it works.
Reply With Quote
  #13  
Old 02-08-2005, 10:53 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Yes, if willing to be brutal about it, Process.Kill() can definately do the trick. It should be the last resort, and one should be as careful as possible when using it.

But your approach is the right idea... I think we can simplify your code a bit, though, to something like this:
Code:
For Each proc As Process In Process.GetProcesses("excel") Try proc.Kill() Catch ' Ignore errors End Try Next proc
However, this closes all Excel applications, including any that the User might have open running separately from your process, so closing them all can really be a big problem. It's pretty similar to a crash; the User would lose all his/her work.

One could call Process.CloseMainWindow() instead of Process.Kill(). This simulates hitting the Big Red [X] button and so allows warnings such as "do you want to save all your work". Another possibilty is to look at the Process.Responding property. If = True, then Process.Kill() should not be necessary (this is probably another Excel instance that the User is currently using) and so don't! So maybe our loop should look like this:
Code:
For Each proc As Process In Process.GetProcessesByName("excel") If Not proc.Responding() Then Try proc.Kill() Catch ' Ignore errors End Try End If Next proc
The best idea to identify which, specific Excel instance you want is via the Caption, which can be picked up via the Process.MainWindowTitle property. If the Excel instance is not Visible, then it's a pretty good bet that the User is not using it, and the .MainWindowTitle will return an empty String ("").

So the best overall plan might be to close all non-responding, non-visible Excel processes:
Code:
For Each proc As Process In Process.GetProcessesByName("excel") If Not proc.Responding() AndAlso Len(proc.MainWindowTitle) = 0 Then Try proc.Kill() Catch ' Ignore errors End Try End If Next proc
Make sense?

But the point is to be as careful as possible when using this... And also to use it only if all other traditional means of closing the Application have failed.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #14  
Old 02-08-2005, 12:35 PM
bellshapedhead bellshapedhead is offline
Newcomer
 
Join Date: Feb 2005
Posts: 6
Default

Actually, my code closes only the instance that I created. This is by design. I have a number of users who have multple instances of Excel open and wouldn't appreciate it if I closed some of there workbooks in mid update. And I only wrote this after all other methods were exhausted.

When I have a problem like this I like to grab it by the short hairs

But, I ALWAYS use this as a last resort.
Reply With Quote
  #15  
Old 02-08-2005, 01:48 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Ah! Ok, very nice!

I got a bit lost in all the addition and subtraction going on and missed the point. And it's a big point! That's pretty darn clever.

Ok, I'm going to paraphrase your code here, if that's ok?
Code:
Dim priorSum As Integer = 0 Dim newSum As Integer = 0 Dim xlProcID As Integer = 0 ' (1) Save the Sum of the Existing "Excel" Processes: For Each proc As Process In Process.GetProcessesByName("excel") priorSum += proc.Id Next proc ' (2) Create the New "Excel" Process: Dim xlApp As New Excel.Application ' (3) Determine the 'newSum' of the Existing "Excel" Processs: For Each proc As Process In Process.GetProcessesByName("excel") newSum += proc.Id Next proc ' (4) Subtract the difference to detarmine the 'xlProcID' xlProcID = newSum - priorSum
From this point onward one can utilize the 'xlApp' reference to manipulate Excel, and when done, if Excel fails to close via normal means it could be closed by making use of the 'xlProcID':
Code:
Try Dim proc As Process = Process.GetProcessById(xlProcID) proc.Kill() Catch ' On error do nothing. End Try
This is a very nice approach, well done.

Thanks BellShape!
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #16  
Old 02-08-2005, 01:55 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

And just for kicks, here's a HashTable implimentation of the same:
Code:
Dim xlProcID As Integer Dim hash As New Hashtable For Each proc As Process In Process.GetProcessesByName("excel") hash.Add(proc.Id.ToString, proc.Id) Next proc Dim xlApp As New Excel.Application For Each proc As Process In Process.GetProcessesByName("excel") If Not hash.ContainsKey(proc.Id.ToString) Then xlProcID = proc.Id Exit For End If Next proc
The HashTable has the advantage of picking up more than one Excel instance if by some unbelievable fluke occurrence that another Excel.Application is created while your code is creating it's instance... Ok, well, I have to admit that that would be all-but-impossible, even if theoretically possible.

Anyway, it's worth learning about the HashTable anyway, it's a nice little class.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #17  
Old 02-08-2005, 02:05 PM
bellshapedhead bellshapedhead is offline
Newcomer
 
Join Date: Feb 2005
Posts: 6
Default

Thanks for the feedback on my humble solution. I am always glad to pick up a little bit more knowledge. I hope the original poster finds this discussion useful. I know I have.
Reply With Quote
  #18  
Old 02-08-2005, 02:12 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Me too.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #19  
Old 02-18-2005, 02:33 AM
MoreLikeIt MoreLikeIt is offline
Freshman
 
Join Date: Dec 2004
Location: Netherlands
Posts: 25
Default

Hi,

This is of course very usefull, also the last resort to get to I think.

bellshapedhead and Mike_R... many thanks for this solution.

greetingzz

MLI
Reply With Quote
  #20  
Old 02-18-2005, 08:50 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

We may all need this more often than we might like to admit... The Excel.Application object can hang if the sub-objects like Range and Worksheet are not disposed before Workbook and then the Excel.Application is last... The problem is that .Net uses non-deterministic finalization, so you can't really control this.

I find that my Excel.Application releases cleanly if done "right", but there is absolutely no guarantee of that... This would seem to be the only way that is absolutely sure. Last resort, yes, but I now use it routinely after calling GC.Collect() and GC.WaitForPendingFinalizers(). With error handling in place, it absolutely cannot hurt... and you just never know if/when it will actually be needed.

Thanks again Bell-Shaped!
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Closing Excel
Closing Excel
Closing Excel Closing Excel
Closing Excel
Closing Excel
Closing Excel Closing Excel Closing Excel Closing Excel Closing Excel Closing Excel Closing Excel
Closing Excel
Closing Excel
 
Closing Excel
Closing Excel
 
-->