Search not helping with Orphaned Excel in task Mngr

[Fleet_204]

Hi Folks,

I have spent much of the afternoon searching the forum and thinking I had solved my issue with Excel processes not terminating when referenced from my VB6 application. I have not.

While I understand the concepts laid out in si_the_geek's thread on Excel here (http://vbforums.com/showthread.php?t=391665) as well as eer3's answers at http://vbforums.com/showthread.php?t=68080, I still get the problem when I bring a workbook into the mix.

Here is a project created from scratch, referencing Excel as outlined in si's post:

Dim oXLApp As Excel.Application         'Declare the object variables
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
 
 
Private Sub Command1_Click()
  Set oXLApp = New Excel.Application    'Create a new instance of Excel
  Set oXLBook = oXLApp.Workbooks.Add    'Add a new workbook
  Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
End Sub
 
Private Sub Command2_Click()
  Set oXLSheet = Nothing             'disconnect from the Worksheet
  oXLBook.Close SaveChanges:=False   'Close (and disconnect from) the Workbook
  Set oXLBook = Nothing
  oXLApp.Quit                        'Close (and disconnect from) Excel
  Set oXLApp = Nothing
End Sub

This is a literal cut and paste of the thread, yet after clicking Command2, Excel.exe remains in the task manager. After re-reading si's thread and the one eer3 posted to, I created another new project, this time without referencing Excel in VB6:

Dim oXLApp As Object
Dim oXLBook As Object
 
Private Sub Command1_Click()
    Set oXLApp = CreateObject("Excel.Application")
    oXLApp.Visible = True  
End Sub
 
Private Sub Command2_Click()
    oXLApp.Visible = False
    oXLApp.quit
    Set oXLApp = Nothing
End Sub
 
Private Sub Command3_Click()
    Set oXLBook = oXLApp.workbooks.Add
End Sub
 
Private Sub Command4_Click()
    oXLBook.Close
    Set oXLBook = Nothing
End Sub

In this case, I broke out the creation of the application and the addition of the workbook. Running command 1 and 2 had the desired results. Excel disappeared from the task manager. However, as soon as command 3 and 4 were brought in to the mix (i.e. 1_Create Excel 3_add a workbook 4_ close the workbook 2_close Excel), the problem re-occurred. Obviously the problem is when I bring the work book into the equation (which is confirmed by commenting out workbook and worksheet references n the first code example).

So what am I doing wrong here? These are cut and pastes out of threads where others are claiming their problems were solved. Are these solutions out dated? I run XP home SP2 with VB6 and Office XP (Excel 10). Thanks for any help

[Static]

that first code should work fine!???

before you run it... go into the task manager and make sure NO excel is running.. kill any that are.

then try it.
onl I would change the command 2 code a little:

Private Sub Command2_Click()
  oXLBook.Close SaveChanges:=False   'Close (and disconnect from) the Workbook
  oXLApp.Quit                        'Close (and disconnect from) Excel
  Set oXLSheet = Nothing             'disconnect from the Worksheet
  Set oXLBook = Nothing
  Set oXLApp = Nothing
End Sub

[si_the_geek]

I see no problems either I'm afraid, the code (in both cases) seems perfectly valid.

The only potential issue I can see is a "user error" one, whereby you could press Command1 twice, thus creating two instances of Excel (the first of which will no longer be 'connected' to your program).


I agree with Static that you should ensure that no previous processes are running.. it is surprising how often that is the problem! I disagree with his reworking of Command2_click tho, as I have seen (a long time ago) that the workbook may not close properly if there is still a object variable refering to the sheet.

What I would change however is in your second set of code, remove the line "oXLApp.Visible = False" from Command2_click.. if there is a problem, you should see a message - but only if the app is visible.

[TheBionicOrange]

.. if there is a problem, you should see a message - but only if the app is visible.

I would suggest Displayalerts is set to true whilst debugging too.

[si_the_geek]

Agreed - but it should be by default anyway.

[TheBionicOrange]

agreed ... but I haven't seen all of his code so he may turn it off somewhere and not have realised.

[Static]

.... I disagree with his reworking of Command2_click tho, as I have seen (a long time ago) that the workbook may not close properly if there is still a object variable refering to the sheet.....

Really? hmm good to know. never had that happen, but i will "correct" my ways to ensure it doesnt

[Fleet_204]

That is what is very strange about this. There is no excel running prior to execution (task manager sorted alphabetically and double checked). It just seems to occur as soon as the work book comes in to play... like it is being orphaned the moment it is created.

Obviously, the issue is unique to my environment. Other than the my specs mentioned in my first post, the only other unusual circumstance on my computer is that I originally installed Office 97 to run some proprietary software that requires Access 97. Now that I take a look, I see that I in fact have Excel 97 and Excel XP installed on this PC. I wonder if that is contributing to the issue?

I will have to try this on another computer...

[Static]

instead of using create object

go to project > references and select Microsoft x.0 Object Library
(Do you have both 8.0 and 10.0? (8.0 i think is 97,9.0 is 2000, 10 is XP, 11 is 2003 right? )

[Fleet_204]

The first example of code uses tha Excel 10 reference. It is the only reference available to me even though I have Excel 97 (which is 8), so yea, that did not work either.

[si_the_geek]

I've used code like this on a PC that had 2 versions of Office installed, and it worked fine..

I do have a theory about what is causing it tho, but for that we'll need to do a little test. Try this variation of your code, and see what happens:

Dim oXLApp As Object
Dim oXLBook As Object
Dim oXLBook2 As Object
 
Private Sub Command1_Click()
    Set oXLApp = CreateObject("Excel.Application")
    oXLApp.Visible = True  
End Sub
 
Private Sub Command2_Click()
    oXLApp.quit
    Set oXLApp = Nothing
End Sub
 
Private Sub Command3_Click()
    Set oXLBook = oXLApp.workbooks.Add
    Set oXLBook2 = oXLApp.workbooks.Add
End Sub
 
Private Sub Command4_Click()
    oXLBook.Close  SaveChanges:= False
    Set oXLBook = Nothing
    oXLBook2.Close  SaveChanges:= False
    Set oXLBook2 = Nothing
End Sub

[Fleet_204]

In the end the results were mostly the same. The two workbooks created (I entered some data manually and flipped between the two of them) and they both closed. After command2, the sheet vanished, but the Excel.exe remained in the task manager. There was only one excel the whole time.

This time, I looked at the Memory usage in the created Excel.exe is system tray. It always starts out in the upper 17k / lower 18k K range and every step of the way, it grows. With one workbook, it jumped to 19,400, then 19,500 after closing the workbook, and then 19,600 after closing the application. With two sheets, this simply inflated the memory usage, but it always grew.

What is odd is that if I release the app without closing it, and then close it manually, it shuts down just fine. This works if I manually exit with the workbook still up or closed first with code. In either case, the excel.exe goes away. Unfortunately, manually closing sheets is not an option in my application.

I just tried several combinations of automated and manual operations using command1 to create the sheet, and modifying command2 to release the application as required. This is what occurred, keeping in mind every instance began with clicking command 1 to start the app:

> manually created a workbook by clicking the New icon (paper with the folded corner) in excel, then closed the workbook and used command2 to close excel. Excell.exe remained in TM.

> commented out oXLApp.quit and clicked command2 to attempt a release prior to opening a workbook manually- Excel shut down on screen and in TM.

> manually created workbook, then clicked command2 with oXLApp.quit still commented out. Manually quit excel and it was gone in the TM.

I tried a few more scenarios with trying to capture ActiveWorkBook and other forms of release, but the common thread seems to be that the only way I can get Excel to go away is to release everything to be closed manually. Any attempts to automate closing with any form of created work book orphans Excel in the TM.

[si_the_geek]

This is really annoying... I can't think of any reason why it would act like that, and no matter what I do, I can't re-create the situation myself.

I've asked a couple of other people to take a look, so hopefully we'll work it out.

[RobDog888]

Got the PM Si

Ok, after readng the thread I have a couple of questions/suggestions.

First, since you using a blank new workbook, do you have any personal macros that execute when Excel opens? Second, the posted code examples are all valid and should work. Are you only using the posted code and nothing additional? this is code in a VB 6 Project and not behind Excel's VBA IDE?

In your first post, second code examle, if you click command1 and then 2 it works correctly but if you click command1, command 3, command4 and then command2 will it work ok?

Perhaps placing some Debug.Print statements in command 3 and command4 to print to the immediate window with the number of workbooks open and / or sheets in the book(s).

Use the early binding for the testing to make sure you working with the desired version. Once we get it working with one we can test the other.

'Add a reference to MS Excel 10.0 Object Library
Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook
 
Private Sub Command1_Click()
    Set oXLApp = New Excel.Application
    oXLApp.Visible = True  
End Sub
 
Private Sub Command2_Click()
    Debug.Print "#2 - oXLApp.WorkBooks.Count Before: " & oXLApp.WorkBooks.Count
    'oXLApp.Visible = False 'No need to complicate things until its resolved 
    oXLApp.Quit
    Set oXLApp = Nothing
End Sub
 
Private Sub Command3_Click()
    Debug.Print "#3 - oXLApp.WorkBooks.Count Before: " & oXLApp.WorkBooks.Count
    Set oXLBook = oXLApp.Workbooks.Add
    Debug.Print "#3 - oXLApp.WorkBooks.Count After: " & oXLApp.WorkBooks.Count
End Sub
 
Private Sub Command4_Click()
    Debug.Print "#4 - oXLApp.WorkBooks.Count Before: " & oXLApp.WorkBooks.Count
    oXLBook.Close
    Set oXLBook = Nothing
    Debug.Print "#4 - oXLApp.WorkBooks.Count After: " & oXLApp.WorkBooks.Count
End Sub

Press Alt+G in the VB 6 IDE to open the Immediate window.

I was just taking a break from painting a bedroom so I may not be back for a while (unless its just too hot to do more).

[DKenny]

Si, Just got your PM this morning.

The one thing I can think of is that there may somehow be a referenece to either oXLBook or oXLBook2 floating around. I can't see why there would be, but you never know.
Anyway, it may help, in command2, to check for existance of either of these objects and remove the references if they exist.

Private Sub Command2_Click()
    
    If Not oXLBook Is Nothing Then
        oXLBook.Close SaveChanges:=False
        Set oXLBook = Nothing
    End If
    
    If Not oXLBook2 Is Nothing Then
        oXLBook2.Close SaveChanges:=False
        Set oXLBook2 = Nothing
    End If
    
    oXLApp.Quit
    Set oXLApp = Nothing
End Sub

[RobDog888]

Yes, that is what I was thinking too as the need for Debug.Print statements. I cant think of anything else that would cause a hold on resources since its a new book being created and and not a pre-existing one.

We will have to wait for a few questions to be answered that I asked but the print statements should tell us more.

[Fleet_204]

OK guys, here's the update:

do you have any personal macros that execute when Excel opens?

None that I have purposely put in. I am not 100% sure how to check if others (including a malicious script) have done so, but I can tell you that when I look at the VBA code in the background of a freshly opened spreadsheet, there is nothing in any of the windows (and I have done some work with macros in Excel, so I know where to find that code).

Are you only using the posted code and nothing additional? this is code in a VB 6 Project and not behind Excel's VBA IDE?

In all cases, I have created new VB6 projects within the VB6 IDE. Each code example is its own form and project. In the case where GetObject is used, there is no reference to Excel 10.0, but where New Excel.Application is used, I have made that reference. There is no further code used, as I know this is perplexing enough and I wanted to keep things simple until the issue was discovered. Oddly enough, even though I have Excel 97 and Excel XP installed, there is no reference to Excel 8.0 in my VB6 IDE... just 10.0.

In your first post, second code examle, if you click command1 and then 2 it works correctly

yes...

but if you click command1, command 3, command4 and then command2 will it work ok?

Hitting Command 1 > 3 > 4 > 2 does not eliminate EXCEL.EXE from the task manager, in spite of the correct code! The only way I can get it to go away is to release the application and shut it down manually... or not create a workbook... then code will shut it down.

Debug.Print statements in command 3 and command4

Unfortunately, it's exactly what working code would produce:

#3 - oXLApp.WorkBooks.Count Before: 0
#3 - oXLApp.WorkBooks.Count After: 1
#4 - oXLApp.WorkBooks.Count Before: 1
#4 - oXLApp.WorkBooks.Count After: 0
#2 - oXLApp.WorkBooks.Count Before: 0

... and EXCEL.EXE is still in the TM.

I have now created a 3rd program combining the code suggestions of Rob and DK as follows:

'Add a reference to MS Excel 10.0 Object Library  <-- DONE
Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook
Dim oXLBook2 As Excel.Workbook  'Object for DKenny's code
 
Private Sub Command1_Click()
    Set oXLApp = New Excel.Application
    oXLApp.Visible = True
End Sub
 
Private Sub Command2_Click()
    Debug.Print "#2 - oXLApp.WorkBooks.Count Before: " & oXLApp.WorkBooks.Count
'    oXLApp.Quit
'    Set oXLApp = Nothing
    If Not oXLBook Is Nothing Then          'Dkenny start
        oXLBook.Close SaveChanges:=False
        Set oXLBook = Nothing
    End If
    If Not oXLBook2 Is Nothing Then
        oXLBook2.Close SaveChanges:=False
        Set oXLBook2 = Nothing
    End If
    oXLApp.Quit
    Set oXLApp = Nothing                    'Dkenny end
End Sub
 
Private Sub Command3_Click()
    Debug.Print "#3 - oXLApp.WorkBooks.Count Before: " & oXLApp.WorkBooks.Count
    Set oXLBook = oXLApp.WorkBooks.Add
    Debug.Print "#3 - oXLApp.WorkBooks.Count After: " & oXLApp.WorkBooks.Count
End Sub
 
Private Sub Command4_Click()
    Debug.Print "#4 - oXLApp.WorkBooks.Count Before: " & oXLApp.WorkBooks.Count
    oXLBook.Close
    Set oXLBook = Nothing
    Debug.Print "#4 - oXLApp.WorkBooks.Count After: " & oXLApp.WorkBooks.Count
End Sub

Even with DK's 'just in case' code, Excel is still orphaned in the TM.

[RobDog888]

One other thing to check is to do an Office Update and an Detect and Repair. Just to make sure Excel is not really the problem. Not having a reference to Excel 97 (8.0) is a bit strange and made me suggest the update and repair option.

[Fleet_204]

I have made executables of my first two programs and on the two other PCs I have run them on, both work normal... EXCEL.EXE shuts down as expected. I will see if I can find any notable differences with the other PCs. In the mean time, I will try office update. I have a full day tomorrow, so it may take me a bit to try this out.

[RobDog888]

No worries. At least it sounds like your making progress. If its running correctly on other systems then it probably is your system and as long as you code it correctly, Excel should give the users to issues.

[Fleet_204]

I visited Windows update and registered for Microsoft update. This patched up Office XP to SP3 as well as a bunch of other individual updates. I also downloaded SP6 for VB 6 and installed that (note: during this, there was a problem with MSRDO20.DLL... I think it said it could not be opened).

I think I am as updated as I can be, but recompiling new .exe's and running from the IDE still has EXCEL in the TM. In spite of the comment that it would not make a difference, I uninstalled the 97 version of Excel, which as stated, didn't matter.

The fact that I can run these sample applications on other PC's and have them running correctly has me satisfied to the level where I am confident that the code is correct... it is just something freaky with my PC. That said, I am willing to continue with any kind of troubleshooting if anyone still has suggestions and is, like me, interested in knowing why this could happen!

In the mean time, I guess I will whip up a program to pick up these orphaned instances after I use the code on my PC... would GetObject pick up the instance of EXCEL running in the background? Perhaps there is code that already exists that someone could point me to?