I have a workbook that contains some common worksheets, forms and macros to be used by two or more other Excel applications. It is nice that Excel automatically opens the reference workbook when the referencing workbook is opened (although I would like it if it were opened minimized or invisible). However, it is a pain to have to close this referenced workbook after closing the invoking application.

When I try to close the referenced workbook in the Workbook_BeforeClose event handler, I get a message that the referenced workbook cannot be closed because it is referenced by an open application ... namely, the one I am trying to close!

How can I automate this process? Most of the time only one of the referencing applications/workbooks would be opened at one time, however, this is not guaranteed. Is there some way I can get the "common" (i.e. referenced) workbook to "wakeup" and close itself when it is no longer referenced?

Welcome to the Board!

Would this work for you:Hope that helps,

Smitty

I'm not sure what you thought that might do. I certainly have no interest in closing all workbooks but myself (and if I did, I think the problem I described would prevent it). My referencing app knows exactly what needs to be closed, but it Excel won't let it do it (see original post). My referenced app doesn't know when it is no longer needed. It just sits there holding routines that are called when they are needed. If my referencing app called it to close itself, it couldn't because it is still being referenced. When it is no longer being referenced, there is nobody (except a manual process) to call it to close or to close it. It's like a deadlock situation. I don't want to run an independent timer routine to keep checking if I'm the only app open. There must be a better way to do this.

I offered a solution to what I thought might help with your issue.

Since that's "obviously" what you did not want, why don't you post the code that you have.

It might be easier to figure out what you want from there.

Smitty

I'm sorry, Smitty. I do appreciate your trying to help, really. However, it looked like your code would just try to close every workbook that was open, except itself (the active one). It did not seem to correspond to the problem I described. It didn't seem to show me a way to close the workbook I was trying to close.

Here is the code I'm trying to execute. It is in the "ThisWorkbook" code module (i.e., an Excel object, not a macro module):
"Material Item List.xls" is a workbook that holds the material items list itself, some supporting code to maintain the list and, most importantly, code, forms and work tables (sheets) to display a pick list and return selected materials items to the calling application. Since I need this service for more than one applet, I have broken it out into its own workbook that can be shared by any applications that need it.

The user should normally not have to see the "Material Item List.xls" workbook, except when maintaining the list. That's why I would like it to automatically close when the calling application closes, just as it automatically opens when the calling application opens. However, Excel won't let that happen ... it puts up the error message I described in my first post (something like "... the referenced workbook cannot be closed because it is referenced by an open application.")

I hope you can help me figure out how to get around this obstacle. Thanks again (and sorry if I phrased my previous response badly).

Try Microsoft Knowledge Base Article 213547 http://support.microsoft.com/default...d=kb;EL;213547

While this article explains how to successfully open and close a workbook and its referenced workbook, it does not explain how to close a refered workbook before closing the refering workbook. In fact, it seems that this is impossible. Perhaps you could have a third party workbook that will open and close the refering and refered workbooks. Sorry I don't have a more elegant solution!

Thanks, msallmen, I came across that KB article while researching this problem. However, I cannot find any referencing objects in my subroutine that reference the subroutine workbook.

I had to add a reference to the subroutine workbook to the project for the referring workbook in order to be able to call the rouines in the other workbook, but this is not something that I can programmatically reset, nor would I want to, because how would I call the routines the next time I opened the referring workbook?

As I showed in my previous post, I am trying to close the referenced workbook directly with a dynamic or built-in object in the WorkBooks collection. There has to be some object to execute the Close method from. I can't understand how they could design this so that you can open a workbook like this (actually, it is opened for you), but not be able to close it!

I can think of 2 solutions depending on the circumstances or preferences:-

1. Don't reference the file with the data, just open it. If there is code that you require in the data sheet then put it (or a copy) in the calling workbook. This will allow the calling workbook to close the data workbook.

or

2. Move all the code to a common workbook an create a xla addin file. Set a reference to the xla file from both data workbook and calling workbook. The xla file will automatically open and be available when either workbook (or both) is opened. Again as in 1. nether workbook themselves are reference directly and can be closed.


xla files can be used like dll files with the advantage that they can be maintained (debugged !) for multiple spreadsheets using the same code.

Hope this helps (and makes sense!).