I have a spreadsheet that has a number of calls to functions that are
contained in an add-in. My users start up the spreadsheet by browsing to the
directory and double-clicking the .xls file. They fill in financial
information, then save the file under a different name. So the original file
is really just a read-only template. What I want to happen is that whenever
they open the template file, or any of the modified files, it will check to
see if the addin is loaded and, if not, load it. For various reasons I don't
really want the add-in loaded permanently; just when one of these
spreadsheets is opened.

I've tried putting the code in the workbook_open event of the spreadsheet
that seems to load the add-in when necessary, but the functions still don't
work (#Name). It works fine if I load the add-in first, then open the
spreadsheet, but this is not agreeable to my users.

Any ideas? Is there a different event that I should use? Or perhaps a
different strategy altogether?

in your beforeclose event, you can change all you formula to text strings.

(pseudocode)
Cells.Replace What:="=", With:="ZZZ="

then save the workbook.

on opening, in the workbook_Open event, load the addin, then convert the
formulas back.

The downside is that this forces a save of the workbook whether the users
want to save or not - so you might include a prompt.

--
Regards,
Tom Ogilvy

"KitenutDave" wrote in message
...
I have a spreadsheet that has a number of calls to functions that are
contained in an add-in. My users start up the spreadsheet by browsing to
the
directory and double-clicking the .xls file. They fill in financial
information, then save the file under a different name. So the original
file
is really just a read-only template. What I want to happen is that
whenever
they open the template file, or any of the modified files, it will check
to
see if the addin is loaded and, if not, load it. For various reasons I
don't
really want the add-in loaded permanently; just when one of these
spreadsheets is opened.

I've tried putting the code in the workbook_open event of the spreadsheet
that seems to load the add-in when necessary, but the functions still
don't
work (#Name). It works fine if I load the add-in first, then open the
spreadsheet, but this is not agreeable to my users.

Any ideas? Is there a different event that I should use? Or perhaps a
different strategy altogether?

Thanks, Tom. Your response gave me an idea. In the workbook_open code, after
loading the add-in, I put a new formula containing a function call in an
empty cell, and it seems to have "refreshed" all the other function calls.
Then I reset the dummy cell to empty.

Don't know why, but it seems to work.
Thanks again.

"Tom Ogilvy" wrote:

in your beforeclose event, you can change all you formula to text strings.

(pseudocode)
Cells.Replace What:="=", With:="ZZZ="

then save the workbook.

on opening, in the workbook_Open event, load the addin, then convert the
formulas back.

The downside is that this forces a save of the workbook whether the users
want to save or not - so you might include a prompt.

--
Regards,
Tom Ogilvy

"KitenutDave" wrote in message
...
I have a spreadsheet that has a number of calls to functions that are
contained in an add-in. My users start up the spreadsheet by browsing to
the
directory and double-clicking the .xls file. They fill in financial
information, then save the file under a different name. So the original
file
is really just a read-only template. What I want to happen is that
whenever
they open the template file, or any of the modified files, it will check
to
see if the addin is loaded and, if not, load it. For various reasons I
don't
really want the add-in loaded permanently; just when one of these
spreadsheets is opened.

I've tried putting the code in the workbook_open event of the spreadsheet
that seems to load the add-in when necessary, but the functions still
don't
work (#Name). It works fine if I load the add-in first, then open the
spreadsheet, but this is not agreeable to my users.

Any ideas? Is there a different event that I should use? Or perhaps a
different strategy altogether?

Well, that causes a recalculate I would think. I assumed you never wanted
to the the #Name errors, but since that isn't the case, then

Application.CalculateFull
after loading the adding

should work in place of what you describe. (assume xl2000 or later)

--
Regards,
Tom Ogilvy

"KitenutDave" wrote in message
...
Thanks, Tom. Your response gave me an idea. In the workbook_open code,
after
loading the add-in, I put a new formula containing a function call in an
empty cell, and it seems to have "refreshed" all the other function calls.
Then I reset the dummy cell to empty.

Don't know why, but it seems to work.
Thanks again.

"Tom Ogilvy" wrote:

in your beforeclose event, you can change all you formula to text
strings.

(pseudocode)
Cells.Replace What:="=", With:="ZZZ="

then save the workbook.

on opening, in the workbook_Open event, load the addin, then convert the
formulas back.

The downside is that this forces a save of the workbook whether the
users
want to save or not - so you might include a prompt.

--
Regards,
Tom Ogilvy

"KitenutDave" wrote in message
...
I have a spreadsheet that has a number of calls to functions that are
contained in an add-in. My users start up the spreadsheet by browsing
to
the
directory and double-clicking the .xls file. They fill in financial
information, then save the file under a different name. So the
original
file
is really just a read-only template. What I want to happen is that
whenever
they open the template file, or any of the modified files, it will
check
to
see if the addin is loaded and, if not, load it. For various reasons I
don't
really want the add-in loaded permanently; just when one of these
spreadsheets is opened.

I've tried putting the code in the workbook_open event of the
spreadsheet
that seems to load the add-in when necessary, but the functions still
don't
work (#Name). It works fine if I load the add-in first, then open the
spreadsheet, but this is not agreeable to my users.

Any ideas? Is there a different event that I should use? Or perhaps a
different strategy altogether?

You're right; CalculateFull works the same as the other code, and is easier,
but I just found an interesting "feature". Both of these solutions work only
if the add-in is in the same directory as the spreadsheet that uses it, which
will not be the case in our production environment. Any thoughts on that one?

"Tom Ogilvy" wrote:

Well, that causes a recalculate I would think. I assumed you never wanted
to the the #Name errors, but since that isn't the case, then

Application.CalculateFull
after loading the adding

should work in place of what you describe. (assume xl2000 or later)

--
Regards,
Tom Ogilvy

"KitenutDave" wrote in message
...
Thanks, Tom. Your response gave me an idea. In the workbook_open code,
after
loading the add-in, I put a new formula containing a function call in an
empty cell, and it seems to have "refreshed" all the other function calls.
Then I reset the dummy cell to empty.

Don't know why, but it seems to work.
Thanks again.

"Tom Ogilvy" wrote:

in your beforeclose event, you can change all you formula to text
strings.

(pseudocode)
Cells.Replace What:="=", With:="ZZZ="

then save the workbook.

on opening, in the workbook_Open event, load the addin, then convert the
formulas back.

The downside is that this forces a save of the workbook whether the
users
want to save or not - so you might include a prompt.

--
Regards,
Tom Ogilvy

"KitenutDave" wrote in message
...
I have a spreadsheet that has a number of calls to functions that are
contained in an add-in. My users start up the spreadsheet by browsing
to
the
directory and double-clicking the .xls file. They fill in financial
information, then save the file under a different name. So the
original
file
is really just a read-only template. What I want to happen is that
whenever
they open the template file, or any of the modified files, it will
check
to
see if the addin is loaded and, if not, load it. For various reasons I
don't
really want the add-in loaded permanently; just when one of these
spreadsheets is opened.

I've tried putting the code in the workbook_open event of the
spreadsheet
that seems to load the add-in when necessary, but the functions still
don't
work (#Name). It works fine if I load the add-in first, then open the
spreadsheet, but this is not agreeable to my users.

Any ideas? Is there a different event that I should use? Or perhaps a
different strategy altogether?

I'm still having problems with this. In essence, I need to ensure that if a
user opens a spreadsheet by double-clicking on it in Explorer, that it's
requesite addins are loaded and available to the formulas.

As the spreadsheet loads, it seems to check the formulas for "foreign"
references before loading the addin. (The code to load the addin is in the
worksheet_open event). When it doesn't understand the references in the
formulas because the addin isn't there, it pops up the message about the
spreadsheet containing links to another spreadsheet, and do I want to update
the links. All the formulas that reference the addin now have the path to the
spreadsheet's directory appended to them instead of the path to the addin's
directory.

For example, if the original formula was "=CalculateResult(parm1, parm2)",
now the formula would read "=g:\dir1\dir2\Addin.xla!CalculateResult(parm1 ,
parm2)". Ok, except that the directory is wrong - g:\dir1\dir2 is the path to
the spreadsheet, but not the path to the addin.

Am I going about this the right way? Am I making it harder than it needs to
be?

"KitenutDave" wrote:

I have a spreadsheet that has a number of calls to functions that are
contained in an add-in. My users start up the spreadsheet by browsing to the
directory and double-clicking the .xls file. They fill in financial
information, then save the file under a different name. So the original file
is really just a read-only template. What I want to happen is that whenever
they open the template file, or any of the modified files, it will check to
see if the addin is loaded and, if not, load it. For various reasons I don't
really want the add-in loaded permanently; just when one of these
spreadsheets is opened.

I've tried putting the code in the workbook_open event of the spreadsheet
that seems to load the add-in when necessary, but the functions still don't
work (#Name). It works fine if I load the add-in first, then open the
spreadsheet, but this is not agreeable to my users.

Any ideas? Is there a different event that I should use? Or perhaps a
different strategy altogether?