Another Automated Excel Closing problem
Another Automated Excel Closing problem
Another Automated Excel Closing problem
Another Automated Excel Closing problem
Another Automated Excel Closing problem
Another Automated Excel Closing problem Another Automated Excel Closing problem Another Automated Excel Closing problem Another Automated Excel Closing problem Another Automated Excel Closing problem Another Automated Excel Closing problem Another Automated Excel Closing problem Another Automated Excel Closing problem
Another Automated Excel Closing problem Another Automated Excel Closing problem
Another Automated Excel Closing problem
Go Back  Xtreme Visual Basic Talk > > > Another Automated Excel Closing problem


Reply
 
Thread Tools Display Modes
  #1  
Old 01-05-2008, 12:48 PM
amjl2000 amjl2000 is offline
Newcomer
 
Join Date: Jan 2008
Posts: 12
Angry Another Automated Excel Closing problem


Hi All,
I'm a bit of a newbie to vb.net, learning from guys at work, and giving myself little projects to help me further myself.

My question relates to Excel, and the opening, using then closing. I have read many tips,guides and thoughts on how to close excel properly so that it removes itself from the Task Manager.

Using these guides, I have code that works properly. But, this is only until I 'insert my own code'

The following code should do the following:
open a Template excel file
add extra data to various cells
save the spreadsheet with a new file name (dialog off to prevent further 'Do you want to overwrite' messages)
close excel.


Now, the code as it stands works OK. But if I put in many more
.Cells(1, 1) = "Hello World"
then when Excel closes, the process remains in the Task Manager.

Is there something I am doing wrong?
I am using .net 2003, and have office12(2007). I have the interop thingies declared at the top of code, and have v12 excel object library referenced. As I said, the code only screws up when more cells are being written to.

Many thanks in advance for the help.
Alex (frustrated)

Oh, and I have tried changing the order in which things close, and have had no luck.



Code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim Filename As String = "C:\Test\Template.xls"
Dim newFilename As String = "C:\Test\NewFile"

Dim excelApp As New Excel.Application

Dim excelBooks As Excel.Workbooks
excelBooks = excelApp.Workbooks ' something on microsoft KB said to do this!

Dim excelBook As Excel.Workbook
Dim excelWorksheet As Excel.Worksheet



excelBook = excelBooks.Add(Filename) ' this opens the template into open workbook
excelWorksheet = excelBook.Worksheets(1)
excelApp.Visible = True ' making it visible to test if it opens
' custom function data
With excelWorksheet
.Cells(1, 1) = "Hello World"

.Cells(1, 1) = "Hello World"
.Cells(3, 1) = "Hello World"
.Cells(2, 1) = "Hello World"
.Cells(3, 1) = "Hello World"

.Cells(1, 1) = "Hello World"

.Cells(1, 1) = "Hello World"
.Cells(1, 1) = "Hello World"
.Cells(1, 1) = "Hello World"
.Cells(1, 1) = "Hello World"
.Cells(1, 1) = "Hello World"
.Cells(1, 1) = "Hello World"

'add more lines here to 'break' the code.

End With

excelBook.Application.DisplayAlerts = False
excelBook.SaveAs(newFilename)

Do While Not excelBook.Saved ' wait here in case it takes a while.
Application.DoEvents()
Loop
excelBook.Application.DisplayAlerts = True

excelBooks.Close()

excelApp.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBooks)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet )

excelBooks = Nothing
excelBooks = Nothing
excelWorksheet = Nothing
excelApp = Nothing

GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()


End Sub
Reply With Quote
  #2  
Old 01-05-2008, 01:15 PM
FuzzyRichie FuzzyRichie is offline
Newcomer
 
Join Date: Jan 2008
Posts: 13
Default Found a solution

Hello Alex!

I seem to have pinpointed the error in your code. When you create the instance of Excel with the line:

Code:
Dim excelApp As New Excel.Application
You seem to be creating the instance, but not associating it with an application per say. However, if you do so like this:

Code:
Dim excelApp As Excel.Application
excelApp = CreateObject("Excel.Application")
from there you will be able to close that specific instance of the application needed. The code "CType(CreateObject("Excel.Application"), Excel.Application)" seems to be better when using Option Strict (as it was giving me a type error using the first type of code).

From there on end there seems to be no other problems that I can tell (although I have been using Word mostly for my project, so it's quite possible that it does not work the same way). Here is the code revised as it would work (hopefully):

Quote:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Filename As String = "C:\Test\Template.xls"
Dim newFilename As String = "C:\Test\NewFile" (isn't there supposed to be a .xls here?)

Dim excelApp As Excel.Application
excelApp = CreateObject("Excel.Application")


Dim excelBooks As Excel.Workbooks
excelBooks = excelApp.Workbooks ' something on microsoft KB said to do this!

Dim excelBook As Excel.Workbook
Dim excelWorksheet As Excel.Worksheet

excelBook = excelBooks.Open(Filename) ' It's already created, just needs to be opened.
excelWorksheet = excelBook.Worksheets(1)
excelApp.Visible = True ' making it visible to test if it opens
' custom function data
With excelWorksheet
.Cells(1, 1) = "Hello World"

.Cells(1, 1) = "Hello World"
.Cells(3, 1) = "Hello World"
.Cells(2, 1) = "Hello World"
.Cells(3, 1) = "Hello World"

.Cells(1, 1) = "Hello World"

.Cells(1, 1) = "Hello World"
.Cells(1, 1) = "Hello World"
.Cells(1, 1) = "Hello World"
.Cells(1, 1) = "Hello World"
.Cells(1, 1) = "Hello World"
.Cells(1, 1) = "Hello World"

'add more lines here to 'break' the code.

End With

excelBook.Application.DisplayAlerts = False
excelBook.SaveAs(newFilename)

Do While Not excelBook.Saved ' wait here in case it takes a while.
Application.DoEvents()
Loop
excelBook.Application.DisplayAlerts = True
' Not needed, as the SaveAs process is completed before the Close process (it's stacked in the application ToDo list)

excelBooks.Close()

excelApp.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBooks)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet )
' This should be done automatically.

excelBooks = Nothing
excelBooks = Nothing
excelWorksheet = Nothing
excelApp = Nothing

GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
' Ok. That works too :D
End Sub
Tell me how that works Alex!
__________________
FuzzyRichie
------------
1st year University of Ottawa student
Software Engineering with Security Option /Génie Informatique avec option Sécurité

Last edited by FuzzyRichie; 01-05-2008 at 01:17 PM. Reason: Quick problem with code
Reply With Quote
  #3  
Old 01-05-2008, 03:25 PM
amjl2000 amjl2000 is offline
Newcomer
 
Join Date: Jan 2008
Posts: 12
Default

Thanks Fuzzy for a swift reply.

I have noted your points:

Dim excelApp As Excel.Application
excelApp = CreateObject("Excel.Application")


My original alteration of your first change did not include the removal of the 'new' declaration - this caused two excel processes to start. But, with the original posted code (with the rest of your alterations), both processes closed properly. By adding extra data insertions into excel cells, the result became 1 process closing, the other remaining.

Once I changed my code to be like your suggestion, the program behaved in the same way as originally posted (i.e. with 1 process), the process did not stop when extra lines of data was entered into excel cells.

It does seem odd, but then I have seen many odd things

Alex
Reply With Quote
  #4  
Old 01-05-2008, 03:26 PM
amjl2000 amjl2000 is offline
Newcomer
 
Join Date: Jan 2008
Posts: 12
Default

oh, and putting filename.xls makes it easier for me, because Excel was saving the files as a .xlsx file! Thanks for this!
Reply With Quote
  #5  
Old 01-05-2008, 04:30 PM
FuzzyRichie FuzzyRichie is offline
Newcomer
 
Join Date: Jan 2008
Posts: 13
Default Ok so that didn't work...

So I see you have Excel 2007

In any case, that is quite odd how it won't close completely. I think I may have found another possible problem Alex.

Code:
Dim excelBooks As Excel.Workbooks
excelBooks = excelApp.Workbooks ' something on microsoft KB said to do this!

Dim excelBook As Excel.Workbook
Dim excelWorksheet As Excel.Worksheet

excelBook = excelBooks.Add(Filename) ' this opens the template into open workbook
excelWorksheet = excelBook.Worksheets(1)
excelApp.Visible = True ' making it visible to test if it opens
What your code seems to be doing here (or more specifically what you're trying to do) is to open many workbooks (using the excelBooks = excelApp.Workbooks) but in the end, you're only closing one of the books in an array of workbooks.

Try with this code then:

Code:
Dim excelBook As Excel.Workbook
Dim excelWorksheet As Excel.Worksheet

excelBook = excelBooks.Open(Filename) ' This will open the template
excelWorksheet = excelBook.Worksheets(1)
excelApp.Visible = True
This way, you're opening only 1 book for the application, and in doing so, are making everything close when you close the single workbook. The final bit of code will be as so:

Code:
excelBook.Close() 'Changed from excelBooks.Close()
excelApp.Quit()
Back to the drawing board I guess
__________________
FuzzyRichie
------------
1st year University of Ottawa student
Software Engineering with Security Option /Génie Informatique avec option Sécurité

Last edited by FuzzyRichie; 01-05-2008 at 04:32 PM. Reason: Too many repetition?
Reply With Quote
  #6  
Old 01-06-2008, 03:00 AM
amjl2000 amjl2000 is offline
Newcomer
 
Join Date: Jan 2008
Posts: 12
Default

Righty ho Mr Fuzzy! This unfortunately still makes no difference. For reference I have copied the whole code below.

I may yet resort to the practice of killing off any excel processes from the Task Manager...!


If you or anyone else can spot the vital mistake, then I am appreciative on any other suggestions! (If anyone has a chance to run the code, then that would be great - the code below is a simple form with a single button on it)

Many thanks as always
Alex


Code:
Imports Microsoft.Office.Interop

Public Class Form1
    Inherits System.Windows.Forms.Form



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        
        Dim Filename As String = "C:\Test\Template.xls"
        Dim newFilename As String = "C:\Test\NewFile.xls"

        Dim excelApp As Excel.Application
        excelApp = CreateObject("Excel.Application")

        Dim excelBook As Excel.Workbook
        Dim excelWorksheet As Excel.Worksheet

        excelBook = excelApp.Workbooks.Open(Filename) ' this opens the template!
        excelWorksheet = excelBook.Worksheets(1)
        excelApp.Visible = True


        With excelWorksheet
            .Cells(1, 1) = "Hello World"

            .Cells(1, 1) = "Hello World"
            .Cells(3, 1) = "Hello World"
            .Cells(2, 1) = "Hello World"
            .Cells(3, 1) = "Hello World"

            .Cells(1, 1) = "Hello World"

            .Cells(1, 1) = "Hello World"
            .Cells(1, 1) = "Hello World"
            .Cells(1, 1) = "Hello World"
            .Cells(1, 1) = "Hello World"
            .Cells(1, 1) = "Hello World"

            .Cells(1, 1) = "Hello World"


            ' copy more of the lines above here to 'break' code


        End With

        ' save file without prompting if overwriting.
        excelBook.Application.DisplayAlerts = False
        excelBook.SaveAs(newFilename)
        excelBook.Application.DisplayAlerts = True

        excelBook.Close()
        excelApp.Quit()

        ' following not needed (doesn't make a difference if included or not)
        'System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
        'System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook)
        'System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet)


        excelBook = Nothing
        excelWorksheet = Nothing
        excelApp = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()


    End Sub
End Class
Reply With Quote
  #7  
Old 01-06-2008, 05:33 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 Alex, hi Richie...


Alex, you might want to have a look at the Automating Office Programs with VB.NET tutorial. It discusses and details the issues regarding the proper release of the Excel application object that you are struggling with here.

Most of the issues, I think, in your code regard the cleanup section at the end and I will show some sample code that I think should work for you...

One other thing that I noticed though is that you are making use of the fact that the .Cells() property for Excel is actually a read/write property instead of being a read-only property returning a cell. This might not be affecting your ability to release your application object, but it is better in .NET if you make it explicit that you are attempting to change the value, and not the cell. So what I am suggesting is, instead of code that looks like the following:

Code:
With excelWorksheet .Cells(1, 1) = "Hello World"

That you instead use something like this:

Code:
With excelWorksheet .Cells(1, 1).Value = "Hello World"


Overall, I would try the following code, keeping in mind that I don't have VB.NET installed where I am right now, so I cannot actually test this:

Code:
Public Class Form1 Inherits System.Windows.Forms.Form Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim Filename As String = "C:\Test\Template.xls" Dim newFilename As String = "C:\Test\NewFile.xls" Dim excelApp As Excel.Application excelApp = CreateObject("Excel.Application") Dim excelBook As Excel.Workbook Dim excelWorksheet As Excel.Worksheet excelBook = excelApp.Workbooks.Open(Filename) ' this opens the template! excelWorksheet = excelBook.Worksheets(1) excelApp.Visible = True With excelWorksheet .Cells(1, 1).Value = "Hello World" .Cells(1, 1).Value = "Hello World" .Cells(3, 1).Value = "Hello World" .Cells(2, 1).Value = "Hello World" .Cells(3, 1).Value = "Hello World" .Cells(1, 1).Value = "Hello World" .Cells(1, 1).Value = "Hello World" .Cells(1, 1).Value = "Hello World" .Cells(1, 1).Value = "Hello World" .Cells(1, 1).Value = "Hello World" .Cells(1, 1).Value = "Hello World" .Cells(1, 1).Value = "Hello World" ' copy more of the lines above here to 'break' code End With ' save file without prompting if overwriting. excelBook.Application.DisplayAlerts = False excelBook.SaveAs (newFilename) excelBook.Application.DisplayAlerts = True ' Cleanup: GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() Marshal.FinalReleaseComObject(excelWorksheet) oWB.Close(SaveChanges:=False) Marshal.FinalReleaseComObject(excelBook) oApp.Quit() Marshal.FinalReleaseComObject(excelApp) End Sub End Class

I hope this helps!
Mike
__________________
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-06-2008, 08:02 AM
amjl2000 amjl2000 is offline
Newcomer
 
Join Date: Jan 2008
Posts: 12
Default

Hi Mike,
Thanks for your advice. Following on from your tips, I can offer the following results.

Firstly I have changed the data entry from Cells, to Range (as per your Guide) - I had read your guide and played around with it without much success yesterday.

By doing this, and changing the cleanup sequence, I didn't have any joy.

I then simplified my code further by removing the code to Saveas(newFile). After this, the code worked properly!
I then narrowed down this problem to being where I set the DisplayAlerts property. It seems that changing this property affects the cleanup somehow (i tried setting it to 'nothing' before the cleanup but it made no difference)
-edit- read on below, I think I have a reason for this (but not an explanation) -edit-

Thankfully, my 'proper' code deals with overwriting differently (I check if the newfile exists before performing any actions on the template file, then delete the old newfile before saving as the new newfile (!)).



What I now have is this:
When I click to perform the process, an Excel process starts, but remains in the Task Manager.
When I click to perform the process again, another Excel process starts, then soon after one of the processes disappears - so I always have one remaining process.

What I have found is that the remaining Excel Process is not being cleaned up properly by the GC.Collect. As there isn't a GC.Collect after the ReleaseCom this is perhaps not a surprise.
But I tried putting GC.Collect code after the ReleaseCom, but this didn't work. Looking at the Task Manager at the same time as the code running makes me believe that the process is still running at this point, and not in a position to be collected.
It is only on the next GC.Collect - which occurs when I click the button again - does the process get picked up. I would also guess that when the app closes, the Garbage is collected, and then this process would then be removed.

This same thing occurred with the test code. Setting the DisplayAlerts caused something not to close quickly enough (?) and Excel was not terminated properly. I added a second button to my project with the gc.collect code in, and sure enough, this made the process disappear from the Task Manager.

So, I think I do not have a problem any more!

If you do have any suggestions or explanations, then I would be eager to know of them - if you do not think it will go over my head! If so, it may be best I do not know!!

Many thanks for the help, both of you, couldn't have done it without you!
Cheers!
Alex
Reply With Quote
  #9  
Old 01-06-2008, 08:26 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

You would have to show all your code again in order for me to comment more properly... However, it is absolutely essential that you make your GC.Collect() calls *before* you commence the rest of your cleanup process. And if this is still not working, you can add a second set of GC.Collect() calls after your cleanup is complete, and this *might* help... But you absolutely must leave the GC.Collect() at the beginning of your cleanup routine in place.

Unfortunately, I do not have a clue why your calls to excelBook.Application.DisplayAlerts property should cause any trouble. Normally, changing a property value should not create an object or otherwise cause any trouble for your cleanup. And, in theory, if any objects were being created behind the scenes, your calls to GC.Collect() should take care of these...

I wish I could be of more help... If you want you could show your newer-version of your code?

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-06-2008, 09:33 AM
amjl2000 amjl2000 is offline
Newcomer
 
Join Date: Jan 2008
Posts: 12
Default

The code is below, and the cleanup should be as you have suggested.
There is a second button event just to do the GC.Collect.

There is also some commented-out code to check if the file already exists, this is a workaround to avoid attempting to overwrite within Excel.

So, the code below does not work properly: the Process starts, Excel pops up, saves under the newfile.xls, and the process should then disappear from view. I notice that the process is still taking about 5% of my CPU time at about a second after Excel closes. if I click on button2, then the process disappears.

If I take out the two lines around my saveas command (and remove the newfile, so I do not get a confirmation message in Excel), then the Excel process is only in Task Manager for a very short amount of time, and disappears at the same time (or so it seems) as the Excel window.

I'll try to remember to take the code into work tomorrow, to see how it runs there.

Thanks
Alex


Code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim Filename As String = "C:\Test\Template.xls"
        Dim newFilename As String = "C:\Test\NewFile.xls"

        Dim excelApp As Excel.Application
        excelApp = CreateObject("Excel.Application")

        Dim excelBook As Excel.Workbook
        Dim excelWorksheet As Excel.Worksheet

        excelBook = excelApp.Workbooks.Open(Filename) ' this opens the template!
        excelWorksheet = excelBook.Worksheets(1)
        excelApp.Visible = True

        Dim oRng1 As Excel.Range

        oRng1 = excelWorksheet.Range("A1")
        oRng1.Value = "Hello World"
        oRng1 = excelWorksheet.Range("A2")
        oRng1.Value = "Hello World"

        'save method A, check ourselves if file exists.
        'If IO.File.Exists(newFilename) Then
        '    Select Case MsgBox("Do you wish to overwrite? ", MsgBoxStyle.YesNo, "File Already Exists")
        '        Case MsgBoxResult.Yes
        '            IO.File.Delete(newFilename)
        '            excelBook.SaveAs(newFilename)
        '        Case MsgBoxResult.No
        '    End Select
        'Else
        '    excelBook.SaveAs(newFilename)
        'End If


        ' Save method B: save file without prompting if overwriting.
        excelBook.Application.DisplayAlerts = False
        excelBook.SaveAs(newFilename)
        excelBook.Application.DisplayAlerts = True


        oRng1 = Nothing
        ' Cleanup:
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()

        System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet)

        excelBook.Close(SaveChanges:=False)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook)

        excelApp.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub
Reply With Quote
  #11  
Old 01-06-2008, 09:53 AM
FuzzyRichie FuzzyRichie is offline
Newcomer
 
Join Date: Jan 2008
Posts: 13
Default

Quote:
Originally Posted by Mike_R View Post
You would have to show all your code again in order for me to comment more properly... However, it is absolutely essential that you make your GC.Collect() calls *before* you commence the rest of your cleanup process. And if this is still not working, you can add a second set of GC.Collect() calls after your cleanup is complete, and this *might* help... But you absolutely must leave the GC.Collect() at the beginning of your cleanup routine in place.

Unfortunately, I do not have a clue why your calls to excelBook.Application.DisplayAlerts property should cause any trouble. Normally, changing a property value should not create an object or otherwise cause any trouble for your cleanup. And, in theory, if any objects were being created behind the scenes, your calls to GC.Collect() should take care of these...

I wish I could be of more help... If you want you could show your newer-version of your code?

Mike
Couldn't you just make a method called UnloadExcel() and have it on a timer looping for every 2 seconds? Or loop until you get a False value (is there any GC command that returns False if there's nothing left to cleanup?) and then exit the program?

Just wondering as my program seems to work fine without the GC. And if not, well then I'd like to make it work
__________________
FuzzyRichie
------------
1st year University of Ottawa student
Software Engineering with Security Option /Génie Informatique avec option Sécurité
Reply With Quote
  #12  
Old 01-06-2008, 10:31 AM
amjl2000 amjl2000 is offline
Newcomer
 
Join Date: Jan 2008
Posts: 12
Default

yes Richie, I could do that, but I'm guessing that it's not the most elegant way of doing it, and I'm sure that there is a 'proper' way of doing it? At least I hope so!

I have found if I make the excel sheet visible, and remove the two lines:
excelBook.Close(SaveChanges:=False)
excelApp.Quit()
Then Excel remains open (as you would expect), but if shut down manually, then the process stops properly. I may stick with this method for my App, because there are various instances where I insert data into various spreadsheets, and some of them require user editing before a final save/print.

I would also put money on it being some quirky thing to do with my set-up. If I re-installed everything, then maybe it would be different!
Reply With Quote
  #13  
Old 01-07-2008, 02:13 AM
amjl2000 amjl2000 is offline
Newcomer
 
Join Date: Jan 2008
Posts: 12
Default

Well I have just tried at work (with .net2003, and office 2000), the effects are the same as when I was at home.

Oh well.
Reply With Quote
  #14  
Old 01-07-2008, 04:03 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

Hey Richie,

Quote:
Originally Posted by FuzzyRichie View Post
Couldn't you just make a method called UnloadExcel() and have it on a timer looping for every 2 seconds?
Well, you don't really need to do this. Calling GC.WaitForPendingFinalizers() after your call to GC.Collect() forces your code to wait until the garbage collection process is completed. Therefore there is no need for any looping, nor any timers...

Quote:
Originally Posted by FuzzyRichie View Post
Or loop until you get a False value (is there any GC command that returns False if there's nothing left to cleanup?) and then exit the program?
The GC command does not return any value, however the GC.WaitForPendingFinalizers() method will force your code to wait until it is done, at that point there is nothing left to collect. Also, Marshal.ReleaseComObject() and Marshal.FinalReleaseComObject() both return a number indicating how many live references remain after the release call is completed -- if the number is not zero then the object was not released. In theory, Marshal.FinalReleaseComObject() should never, ever return a value other than zero, however, Marshal.ReleaseComObject() only releases one reference count at a time and so if the initial count is > 1, can't return a nonzero result -- the code should therefore call Marshal.ReleaseComObject() repeatedly in the loop, until it returns a zero result. Calling Marshal.FinalReleaseComObject() is easier , though, because it effectively does this looping and releasing for you -- but it is only available in .NET versions 2.0 and above.

I hope this makes sense?
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #15  
Old 01-07-2008, 04:05 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

Hey Alex,

Don't give up yet...

I would start by changing your calls to ReleaseComObject() to instead call FinalReleaseComObject() -- or are you using .NET 1.0/1.1? If you are using .NET 1.0/1.1, there is a workaround -- so let us know.

Therefore I think you should be able to try the following code:

Code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim Filename As String = "C:\Test\Template.xls" Dim newFilename As String = "C:\Test\NewFile.xls" Dim excelApp As Excel.Application excelApp = CreateObject("Excel.Application") Dim excelBook As Excel.Workbook Dim excelWorksheet As Excel.Worksheet excelBook = excelApp.Workbooks.Open(Filename) ' this opens the template! excelWorksheet = excelBook.Worksheets(1) excelApp.Visible = True Dim oRng1 As Excel.Range oRng1 = excelWorksheet.Range("A1") oRng1.Value = "Hello World" oRng1 = excelWorksheet.Range("A2") oRng1.Value = "Hello World" 'save method A, check ourselves if file exists. 'If IO.File.Exists(newFilename) Then ' Select Case MsgBox("Do you wish to overwrite? ", MsgBoxStyle.YesNo, "File Already Exists") ' Case MsgBoxResult.Yes ' IO.File.Delete(newFilename) ' excelBook.SaveAs(newFilename) ' Case MsgBoxResult.No ' End Select 'Else ' excelBook.SaveAs(newFilename) 'End If ' Save method B: save file without prompting if overwriting. excelBook.Application.DisplayAlerts = False excelBook.SaveAs(newFilename) excelBook.Application.DisplayAlerts = True oRng1 = Nothing ' Cleanup: GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelWorksheet) excelBook.Close(SaveChanges:=False) System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelBook) excelApp.Quit() System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp) GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() End Sub

Give it a try!
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #16  
Old 01-07-2008, 06:20 AM
amjl2000 amjl2000 is offline
Newcomer
 
Join Date: Jan 2008
Posts: 12
Default

Hi Mike

I do not seem to have the FinalReleaseComObject!
I can confirm that I have V2.0 Framework (on my work machine at least)

Do I need to do something else in my project to get the FinalReleaseComObject command?

(I created the project at home, so would that affect this, if I only had V1.1 there?)

Regards
Alex
Reply With Quote
  #17  
Old 01-07-2008, 07:15 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 amjl2000 View Post
(I created the project at home, so would that affect this, if I only had V1.1 there?)
Yes, this is my guess. You may need to upgrade your project to .NET 2.0 (usually Visual Studio 2005 would ask if you want to do this when you first open it), or you can change the references manually.

Worst case, you could start a new project within Visual Studio 2005 and then copy paste in your existing code. If the project is not too large yet, this is probably the easiest way to go.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #18  
Old 01-07-2008, 07:37 AM
amjl2000 amjl2000 is offline
Newcomer
 
Join Date: Jan 2008
Posts: 12
Default

I am currently using .net 2003 - am I right in saying that this does stuff using framework 1.1, and it is .net 2005 that uses framework v2.0..

If so, I can create the test code in .net 2005 at work, but not at home (...at least not yet)

(I am using 2003 because most of our software at work was created in 2003, and at present there is no plan to upgrade it to 2005.)
Alex
Reply With Quote
  #19  
Old 01-07-2008, 11:32 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

Ok, in that case you'll have to call Marshal.ReleaseComObject() in a loop, until the result is zero references.
Something like this:

Code:
While Marshal.ReleaseComObject(obj) > 0 End While

This is equivalent to what Marshal.FinalReleaseComObject() is doing behind the scenes...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #20  
Old 01-07-2008, 11:39 AM
amjl2000 amjl2000 is offline
Newcomer
 
Join Date: Jan 2008
Posts: 12
Default

OK, so I just put the test code into .net 2005, and lo and behold it works.

It even worked with just a single gc.collect and wait before releasing, although I would keep the gc.collect after the release to be sure.

So it seems that the issue is with .net 2003 then? Or the framework V1.1?

Mike, you say you have a workaround for the FinalReleaseComObject in .net2003? I would like to see it if that's OK.

Hold on, just found it in another of your posts:

Code:
        While System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet) > 0
        End While

        excelBook.Close(SaveChanges:=False)
        While System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook) > 0
        End While

        excelApp.Quit()
        While System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp) > 0
        End While
This seems to work for me in 2003!

Also, one quick question. If I was to pull my code across to .net 2005, which will be a bit of a pain, does it mean that if I was to try to run the software on a different computer (and I don't mean a full scale deployment, just copying the bin folder from the project and running the executable) then will that computer have to have framework V2 on it?

Many thanks (again!)
Alex
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
Another Automated Excel Closing problem
Another Automated Excel Closing problem
Another Automated Excel Closing problem Another Automated Excel Closing problem
Another Automated Excel Closing problem
Another Automated Excel Closing problem
Another Automated Excel Closing problem Another Automated Excel Closing problem Another Automated Excel Closing problem Another Automated Excel Closing problem Another Automated Excel Closing problem Another Automated Excel Closing problem Another Automated Excel Closing problem
Another Automated Excel Closing problem
Another Automated Excel Closing problem
 
Another Automated Excel Closing problem
Another Automated Excel Closing problem
 
-->