Issues with Referencing .XLA file on a Server

[JodieM]

Hi,
I have a set of macros that are used for company standard documents.
The Macro's are in an .xla file that I need to have reside on the
server as 100+ people will be using the documents that reference these
macros.

I am having trouble with how to correcly refernce the .xla so that it
will either always reference the .xla on the server (I don't mind if
it's hard coded) or that it will correctly reference the .xla on the
individual user's C:\ drive (excel addins) folder if the .xla is
copied there via a login script.

The .xla consists of macros and functions.
1.) A macro to change the logo to a new logo - it pops up a user form
and then changes the logo to the newly selected logo
2.) Functions for document properties (eg http://www.cpearson.com/Excel/docprop.htm)

I have tried numerouse examples posted around the groups to try and
get the macros to work with no luck.

Attempt 1 - Copy the .xla file to the user's addins folder on the C:
\drive.
When other users open the workbooks that reference the macros and
functions they get a hard coded reference to the .xla file on my PC. I
have seen posts and web sites saying to do it this way, and I would
have no problem doing it this way, but I just can't seem to get it to
work.

Attempt 2 - Leave the .xla on the server and create a reference to it.
I've tried creating the reference manually via the VBA interface, I've
tried setting the reference through code on workbook open (as per
http://groups.google.com.au/group/m...2?lnk=st&q=excel+.xla+on+server&rnum=32&hl=en)
and I've tried just referencing the .xla throught he Addins dialog (as
per
http://groups.google.com.au/group/m...9?lnk=st&q=excel+.xla+on+server&rnum=18&hl=en)

Every time I close the workbook and then re-open it the reference is
lost (no matter which way I do it).

One post I read said the .xla needs to be in the same folder as the
workbook. this is not possible as thousands of workbooks will be
created and saved all over the server, and they will still always need
to be able to reference the .xla.

Can anyone help with this and suggest how I set this up so it always
works.

Thanks

 

[Dave Peterson]

How about a third choice?

Tell your users to put the addin in a dedicated folder on their C: drive.

Maybe:
c:\JodieMUtils

Then tell them to use tools|Addins to browse to this folder to install it.

 

[Dave Peterson]

ps. By putting the addin in that common folder, you'll avoid problems with
formulas in the worksheets that use your UDFs.

But if you're creating a new menu item or a toolbar, you may find it easier to
create that toolbar/menu item each time the workbook opens.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Tim]

As Dave notes, having a common local location on C: is useful if your add-in
has UDF's which are used in worksheet formulas.

If you don't have these then using a single location has the advantage of
being able to update a single file when changes need to be made. However,
this is not as straightforward as it may seem, because once anyone has that
add-in open, you won't be able to update the xla.

Are the workbooks referencing the xla being created from a template ? If
that's the case then you might consider having a workbook_open procedure
just open the xla as suggested by Dave. If you need to be able to update
the xla as required, then have the code first copy the server-based version
to the user's temp folder, and open it from there: then the server copy
won't get locked. Or host the "main" copy on a web location.

What works best for you may depend a lot on your supporting IT
infrastructure.

Tim

 

[Dave Peterson]

I'm not sure that second paragraph is correct.

IIRC, I used to updata an addin stored on a network drive and I never had any
trouble if others had it open.

(But it's been a while and I can't test it anymore.)