Extra Excel "Instances"

[MWE]

I am running Office2000. I have a Word application that calls a subroutine that:



opens an Excel file
reads some stuff
closes the Excel file
exits
I have discovered that an extra Excel instance or process remains open after the above sub exits. I assumed that it was Personal.xls. So I added another close statement in the sub to explicitly close Personal.xls The statement does not trigger an error, but there is still an unexplained Excel instance after the sub exits.

But it gets even stranger (or not). The extra Excel instance disappears when the Word application closes.

Can anyone explain this?

Thanks


UPDATE: I looked at the xls file opened by the sub and noticed that it did reference Personal.xls (and did not need to). So I unchecked that reference, saved the file, went back to the Word appl and did a little testing:



the explict close of Personal.xls now triggered an error (as it should)
the spurious instance of Excel was still there after the sub closed but while the Word appl was still open
the spurious instance of Excel disappeared after the Word appl closed.

[Jacob Hilderbrand]

Are you quitting the Excel App and not just closing the Excel file?

[MWE]

Are you quitting the Excel App and not just closing the Excel file?
That's a good question. Since I never create an Excel object, I would think that I do not have to quit Excel. Does the Open Workbook action automatically create and Excel object?

[matthewspatrick]

Jake is spot on.

Typically, you will see code like this:


Dim xlApp As Object
Dim xlWb As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\somefile.xls")

'do some stuff

xlWb.Close
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub




What is your current code?

Patrick

[MWE]

Jake is spot on.

Typically, you will see code like this:


Dim xlApp As Object
Dim xlWb As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\somefile.xls")

'do some stuff

xlWb.Close
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub




What is your current code?

Patrick
Thanks for the reply. I do not have the statements to create an Excel object and later quit it They are not necessary if all you want to do is open the workbook read a few things and close it. That is why I asked the question if an Excel object is automatically created by just opening a workbook. If so, I can probably just delete the Excel object (that I did not create in the first place:devil:)

[Jacob Hilderbrand]

How are you opening the workbook? This code is in Word right?

[matthewspatrick]

That is why I asked the question if an Excel object is automatically created by just opening a workbook. If so, I can probably just delete the Excel object (that I did not create in the first place:devil:)

Yes, if you are opening the workbook then you are forcing implicit creation of an Excel.Application object.

This is why whenever I try to automate AppX from AppY, I always explicitly create an instance of AppX first, and then destroy the instance when I no longer need it.

Patrick

[TonyJollans]

I do not have the statements to create an Excel object and later quit it They are not necessary if all you want to do is open the workbook read a few things and close it.Nonsense!

You may not have an explicit create statement but if you want to read a workbook you must use an application which can do that via automation; in theory it doesn't have to be Excel but I'm not aware of any other app with the capacity. You must have a statement which somehow gets hold of an instance of said app, creating it if necessary (GetObject probably).

If you create an Excel object it is your responsibility to destroy it. One problem with doing what I presume you are doing is that you probably don't know whether you have created an object or not.

[MWE]

Nonsense!

You may not have an explicit create statement but if you want to read a workbook you must use an application which can do that via automation; in theory it doesn't have to be Excel but I'm not aware of any other app with the capacity. You must have a statement which somehow gets hold of an instance of said app, creating it if necessary (GetObject probably).

If you create an Excel object it is your responsibility to destroy it. One problem with doing what I presume you are doing is that you probably don't know whether you have created an object or not.
Sorry, but you are wrong. You do not need the GetObject or CreateObject or anything like that as long as you are not doing operations for which you really need, say, Excel vs just needing Excel's objects. I have dozen of applications that use the objects from other applications with no GetObject or CreateObject statements. As long as the calling appl has the correct link libraries (references), it all works fine. In this particular case the parent appl is Word. In its references list is all the normal stuff plus the Excel dll.
Word simply does a Workbook.Open FileName:=stuff; reads the relevant data; closes the workbook and that's it.

Having said that, there are times when I create Excel (or something else) objects, but only when I really need to actively do things that Excel does vs passively sucking out data.

What may be occuring here is that when the Workbook.Open statement fires, an instance of Excel is automatically created "in the background". That is what this thread is all about. As I recollect, if an Excel instance is explicitily created, it shows in the Applications list and on the taskbar. With the method outlined above, it does not; it only shows in the process list.

Adding the create obj and quit obj code is pretty trivial and I can certainly do that. And now that it appears (??) that the parent appl may be doing the creation anyway, it would be better to do the operations explicitly.

[matthewspatrick]

Sorry, but you are wrong. You do not need the GetObject or CreateObject or anything like that as long as you are not doing operations for which you really need, say, Excel vs just needing Excel's objects.

If you create an Excel object from another app (which you did, by opening the workbook), you implicitly create an Excel.Application object. So, do you need to explicitly create the Excel.Application object? No, because it will be implicitly created anyway. But IMHO, the best practice is to explicitly create and then explicitly destroy it.


I have dozen of applications that use the objects from other applications with no GetObject or CreateObject statements. As long as the calling appl has the correct link libraries (references), it all works fine.

It works fine, except that it may be leaving behind extra instances of Excel running in the background, which is usually harmless, but you should prevent it if you can.


What may be occuring here is that when the Workbook.Open statement fires, an instance of Excel is automatically created "in the background". That is what this thread is all about. As I recollect, if an Excel instance is explicitily created, it shows in the Applications list and on the taskbar. With the method outlined above, it does not; it only shows in the process list.

Yes, that is precisely what is happening. BTW, as I understand it, creating an instance of Excel will always cause it to show up on the process list, but it will only show up in the application list of you make the application object visible. But I may be mistaken.


Adding the create obj and quit obj code is pretty trivial and I can certainly do that. And now that it appears (??) that the parent appl may be doing the creation anyway, it would be better to do the operations explicitly.

I would agree :thumb

Patrick

[TonyJollans]

Sorry, but you are wrong. You do not need the GetObject or CreateObject or anything like that as long as you are not doing operations for which you really need, say, Excel vs just needing Excel's objects. I have dozen of applications that use the objects from other applications with no GetObject or CreateObject statements. As long as the calling appl has the correct link libraries (references), it all works fine. In this particular case the parent appl is Word. In its references list is all the normal stuff plus the Excel dll.I didn't realise you could do it like that, but the basic principle (if you want to read a workbook you must use an application which can do that via automation) is the same even if the code is slightly different.


Word simply does a Workbook.Open FileName:=stuff; reads the relevant data; closes the workbook and that's it.No. Word does not do that - Word / VBA is using an instance of Excel (which it may have created) to do the open and all the rest of it.


What may be occuring here is that when the Workbook.Open statement fires, an instance of Excel is automatically created "in the background".Yes.


As I recollect, if an Excel instance is explicitily created, it shows in the Applications list and on the taskbar. With the method outlined above, it does not; it only shows in the process list. Excel objects created via Automation are not by default visible and do not appear in the Applications list, whether implicitly or explicitly created.


You are (sometimes) creating an instance of Excel and when you do, you ought to take responsibility for it. VBA Housekeepig will take care of it if you don't but, personally, I think it's bad practice to let that happen. One of the problems is that you don't know whether you have created a new instance or not.

[MWE]

I think we have all learned some interesting bits from this, me more than you. Had I realized that an Excel instance was being created impliciitly, I would have added the code to explicitly create and then quit it.

Strangely, my experience is that there are some things you can do without the explicit Excel object (passive kinds of things) and some things that create errors. I will have to go back and look at some examples of each and sort out why I explicitly create/quit objects in some applications and do not in others. There may be more to this thread than has been revealed so far.

The particular sub that does not have the create/quit in it has run pretty well for several years being called from virtually every MS appl (Access, Excel, PowerPoint, Project and Word). It is mostly called from other Excel appls, so the "problem" does not come up very often. I guess it is a good thing that VBA or something else was cleaning up after me or there would be hundreds of spurious instances of Excel on my system :devil:

[TonyJollans]

There seems to be some general disagreement (around the web, not just here) about just how much housekeeping VBA does and how much you can or should trust it. If you have just noticed something and don't actually have a problem then I wouldn't worry too much about it.

I have learnt something from this thread - which is why I am here. I knew implicit instantiation happened and that it could cause problems - and does, sometimes, on upgrading to 2003 because of changes (generally for the better) behind the scenes - but I hadn't realised you could take advantage of it the way you are doing. Thank you.

[MWE]

There seems to be some general disagreement (around the web, not just here) about just how much housekeeping VBA does and how much you can or should trust it. If you have just noticed something and don't actually have a problem then I wouldn't worry too much about it.

I have learnt something from this thread - which is why I am here. I knew implicit instantiation happened and that it could cause problems - and does, sometimes, on upgrading to 2003 because of changes (generally for the better) behind the scenes - but I hadn't realised you could take advantage of it the way you are doing. Thank you. {I am still having problems with the Reply to Thread window, so I am embedding html to make the post readable}

Thanks for all the help from all who contributed to this thread. Unfortunately, my problem has not been resolved. I revised my code to explictly create the Excel object and explicitly quit the Excel object. Based on testing and explicit display statements, the code is being executed. However, the spurious instances of Excel are still there. In particular:
1. the procedure is called from Word
2. the procedure creates an Excel object
3. a workbook is opened and stuff read from it
4. the workbook is closed
5. the Excel object is quit
6. all objects are set to nothing
7. the procedure exits

Everything works correctly at the application level, but, if I then use TaskManager to look at active processes, a copy of Excel is floating around. As before, when I exit Word, the spurious copy of Excel disappears.

The relevant code is shown below (I can not guarantee that it is replicating correctly given the problems with the forum):

Dim xlAppl As Object
Dim xlBook As Object

Set xlAppl = CreateObject("Excel.Application&quot)
Set xlBook = xlAppl.Workbooks.Open (ApplPath & "\" & ApplName, UpdateLinks = False)

xlBook.Close savechanges:=False
Set xlBook = Nothing
xlAppl.Quit
Set xlAppl = Nothing

Any thoughts on what is happening here?

[TonyJollans]

Is it possible you are using any other Excel Application objects which may trigger implicit instantiation?

If you remove the reference to Excel and make sure you have Option Explicit, the compiler should flag them as undeclared variables.

[MWE]

Is it possible you are using any other Excel Application objects which may trigger implicit instantiation?

If you remove the reference to Excel and make sure you have Option Explicit, the compiler should flag them as undeclared variables. Tony: thanks.

You have triggered a thought that may explain the phenomena. :think: I have Option Explicit set and have no compiler errors because I explicitly added the MS Excel obj library to the references list. Thus objects are "resolved" at compile rather than at run; I actually could have typed/Dimed xlBook as Workbook rather than as object. I wonder if there is an implicit Excel instance from the references and an explicit from the CreateObject.

Easy to test.

[MWE]

Further to this baffling problem ...

I played around with referencing the Excel library directly and not. That did not seem to solve the problem. But I have narrowed it down a bit.

The Word parent calls a proc (MakeMenu) that interacts with a target workbook. In particular, the target workbook contains information used to create a temporary pull down menu in the Word parent (I know that the temp property does not work in Word2000). If MakeMenu creates the Excel object, opens the target workbook, munges around, saves a few things, closes the workbook and quits the excel object, there are no spurious instances of Excel. But if the code that actually creates the menu for the Word parent is executed, the spurious copy of Excel is there after the Excel object is explicitly quit.

Any idea why the menu creation results in the spurious instance of Excel?

[TonyJollans]

Difficult to tell - what about pasting the code?

[fumei]

Code would be good....
If MakeMenu creates the Excel object, opens the target workbook, munges around, saves a few things, closes the workbook and quits the excel object, there are no spurious instances of Excel. But if the code that actually creates the menu for the Word parent is executed, the spurious copy of Excel is there after the Excel object is explicitly quit.
Could you clarify this a bit? Is the first example of MakeMenu NOT making a menu? But the second example - which you do not explicitly state is MakeMenu - DOES make a menu? And are the parameters of that menu directly dependent on what the procedure gets from Excel?

[MWE]

Code would be good....Could you clarify this a bit? Is the first example of MakeMenu NOT making a menu? But the second example - which you do not explicitly state is MakeMenu - DOES make a menu? And are the parameters of that menu directly dependent on what the procedure gets from Excel? MakeMenu is the VBA procedure that is called by the Word parent (actually any MS parent) to create menus. It creates an Excel object, opens a target spreadsheet, reads data from the spreadsheet, creates one or more menus from that data, closes the spreadsheet and quits the Excel object:

Case 1: MakeMenu is called "normally" by the Word parent. Correct menus are created but an Excel instance or process is running in the background. Based on MsgBox displays, the create code and quit code are executing properly. The extra Excel instance disappears when the Word parent is closed.

Case 2: MakeMenu is modified such that execution bypasses the menu building steps. When called by the Word parent, MakeMenu still creates the Excel object, opens the workbook, etc., closes the workbook and quits the Excel object. There is no menu created (as expected) and no copy of Excel in the background

[fumei]

Hmmmm. Thanks. That is kind of strange. The only thing apparently different is that in Case 1 data is taken from Excel and used in Word. Case 2 does not take data.

Why would VBA care once it has got that data from Excel and passed it to Word? I am assuming it is passed as numeric and/or string variables, rather than object variables. If there are object variables somehow passed I could see a dependence.

Say you have an object in the instance of Excel, and then pass its value - even as a string, or a number - does Word require the Application instance that made the object to still exist?

Grasping here....

[MWE]

Hmmmm. Thanks. That is kind of strange. The only thing apparently different is that in Case 1 data is taken from Excel and used in Word. Case 2 does not take data.

Why would VBA care once it has got that data from Excel and passed it to Word? I am assuming it is passed as numeric and/or string variables, rather than object variables. If there are object variables somehow passed I could see a dependence.

Say you have an object in the instance of Excel, and then pass its value - even as a string, or a number - does Word require the Application instance that made the object to still exist?

Grasping here....

You are correct that in Case 1 data is taken from Excel and used in Word. But in other tests that I have run, I have taken data from Excel, used it in Word, modified data in the spreadsheet, etc., and every closed out as it should, i.e., no Excel instances int he background. The only difference I could detect was the Menu creation. I was almost to the point of believing that this was one more example of problems with Word's menu creation capability (it is a bit different from, say, Excel).

But as of 10 minutes ago, I am back to square zero :banghead: After running Case 1 and Case 2 a half dozen times each using several different Word parents and several different target workbooks, and getting consistent results, I was absolutely convinced that the problem was menu creation. And then I updated some virus signature files and installed a new version of WinPatrol, booting the system several times in the process ... and you guessed it -- the problem of extra Excel instances is gone. The problem has been consistently "there" for a week; probably for much longer, but I only noticed it a week ago. Now it is gone. As sure as the sun will rise tomorrow, that problem will return, but for now ... :banghead::banghead:

Thanks for your time and patience. :thumb

[fumei]

Whoooo boy....