Auto-Installing .XLA first time a spreadsheet is opened?

[PeteCresswell]

Got some code in an Excel workbook.

User plans to save multiple copies of workbook.

The Good-Right-And-Holy-Path seems pretty clear: move said code to
a .XLA so any changes to same will be transparent across copies of the
workbook.

But I'm in Philadelphia PA, and now we've got the issue of user XYZ,
who opens the workbook for the first time - in London at 0400 EST.

I'd rather not have to engage in any hand holding and have the
workbook automagically create a link to my .XLA code repository.

Not a religious issue... but definately a nice-to-have.

Suggestions?

 

[john]

a setup utility to install your AddIn may be answer. Have a look at this
article & see if offers what you are looking for.
http://www.jkp-ads.com/articles/DistributeMacro10.htm

 

[Charles Williams]

You could add some Workbook_Open code to the workbook that checks the Addins
collection or the Workbooks collection and if your XLA is not installed or
open then it installs it (assuming that the London user somehow has access
to it).

If the XLA is on a network share there are some advantages to having a small
stub loader XLA (which will almost never change) which looks for the latest
version of the XLA and opens it (if you open an XLA it functions as an
installed XLA except that its not in the Addins collection etc).
There is an example of this in my auto-reversioning addin loader which is on
my downloads page
http://www.DecisionModels.com/downloads.htm


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[PeteCresswell]

There is an example of this in my auto-reversioning addin loader
which is on my downloads pagehttp://www.DecisionModels.com/downloads.htm

Charles

That certainly looks like the ticket.

But no good deed goes unpunished.

Can you think of any reason that the .XLA document that I migrated all
my code to should be rejected by Excel as "Not a valid add-in"?

I implementd your AddIn loader... and it worked....sort of....
problem being that in, for instance, WorkBook_Open where I replaced
all the code with a single call to a routine in my .XLA, Excel says
"Compile error: Sub or function not defined." when the .XLS that's
referencing my .XLA code is opened.

Both documents are open at this point and the .XLA code is public -
and even compiles.... but when I hop over to the .XLS code and try to
compile it, the same "...not defined..." error pops.

This seems consistant to me with the root problem being something
wrong with my .XLA - i.e. even though Excel is opening it, it's not
recognizing it as a .XLA and is not making it's routines available to
the .XLS.

Sound reasonable?

If so, can anybody offer up any common mistakes that would make
something that's named .XLA and can be opened by Excel not a valid Add-
In?

 

[Charles Williams]

Well it would not be a valid XLA if you just changed the file extension from
..XLS to .XLA: you have to Save As an xla (or change the workbook.isaddin
property then save as an XLA).

Not sure why you need a Reference/Link from the user workbook to the XLA
anyway?
I thought the idea was to move all the code from the user workbook to the
XLA ...

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com