Close Referenced Workbook if it's No Longer Referenced

[Ryan H]

Let me give you some background information first. I have a workbook (Quote
Generator v3.xls) that references an add-in workbook (QG Add-In v3.xla). The
Quote Generator v3 workbook is password protected thus opened as read only
and the sales team uses it to produce product quotes. When the user saves a
quote they have to rename the Quote Generator v3.xls workbook (because it is
read only). For example, user saves the quote as CompanyABC Quote.xls onto
the server. The sales person may have several quotes open that reference the
QG Add-In v3.xla workbook and they may have their own other workbooks open
that don't reference anything.

My question is this. If the sales person closes all the quote workbooks
that reference the add-in workbook the add-in workbook remains open, why?

If nothing is referencing the add in workbook shouldn't it close
automatically?

Is there a way to have the add-in workbook close when no other workbooks are
referencing it?

Thanks in Advance!

 

[Chip Pearson]

The add-in will remain loaded even when there are not references to
it. Add-ins don't do any kind of reference count, such as does COM,
so until you unload the add-in either via the Add-Ins dialog or via
code, the add-in will remain loaded. The add-in itself has no
knowledge that it is (or is not) being referenced by some workbook.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Ryan H]

Well since the add-in is considered "blind" to what workbooks are referencing
it, is there code that can count the workbooks that are currently referencing
the add-in? So if the count is 0 could I engineer so sort of code to close
the add-in? Hmmm

If this can't be done. Is there a way to make edits to the add-in workbook
(that is located on the server) while other workbooks are referencing it?

My problem is I update the add-in workbook when a user brings a bug to my
attention. If the user leaves the Excel application open and the add-in
workbook is not closed, Excel considers it as Read Only and I can't overwrite
the file. Is there a way to overwrite the .xla file when its being
referenced? Or do you have any other suggestions?

I basically have to depend on the sales team to close out Excel completely
in order to do any updates.

Thanks in advance,

 

[Neal Zimm]

Hi Ryan,
I await Chip's answer too, 'cuz I'm going to have the same general
problem in an addin I'm developing.

What I do know from hands on experience, and this is a very generic
answer, is that some very big applications have to come down from time to
time for maintenance. (Like the authorization system @ American Express
where I used to work.)

My thoughts are, undeveloped as yet, is at a salesman's workbook open
event to check if the addin is open (and you would be the one to close it to
do your maint). If addin is closed, issue a msg "System will be backup in ??
minutes".

I am guessing at this, but to keep it closed you might have to rename the
addin while working on it, to keep if from being opened when a sales person
opens his/her workbook.

Neal

 

[Chip Pearson]

I don't think that you can do what you want to do. Excel will not
allow you to close a workbook (or xla) when another workbook has a
reference to that workbook or xla. Excel does some sort of reference
counting internally, but there is no programmatic access to that.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com