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.)

 

[Tim]

Dave,

Has been a problem for me, so maybe there is some network setup component in
there somewhere ?

Tim

 

[Dave Peterson]

Or maybe it's just my bad memory.

Dave,

Has been a problem for me, so maybe there is some network setup component in
there somewhere ?

Tim

 

[Tim]

Well, we can't be expected to remember *everything*...

Cheers
Tim

 

[JodieM]

Sorry guys but I'm still quite confused.

First some background.

Our users are not capable of referencing the .xla file themselves or
doing anything that requires any input from them other than opening
the file and have it all work. They are NOT technical people and are
not good with computers. So the first option, unfortunately is not
going to help in this situation

The macros that I'm referencing in the .xla will be used in both
templates AND the hundreds of worksheets that will be based on those
templates.

Yes the .xla does have UDF's. At the moment there are no menus but
that will probably change in the future, so I need that option too.

The only reason I even started on this .xla path is because I read
somewhere that I could do it. I have it all working with the Word
Templates through a global.dot file and now I'm just struggling with
the .xla verson. I could put the macro's inside the templates but that
means the macro's will then be diseminated to hundreds of documents
and that's just a bit unweildy.

I don't have a problem with the locking of the file on the server
because I can just update it at night if it is locked by someone using
it. There won't need to be urgent updates that will require 100+ users
to exit excel.

So now for my questions.

Why does it have to be on the C: drive for the UDF's to work?

If it's on the C: Drive, why cant it be in C:\Documents and Settings\
%UserName%\Application Data\Microsoft\AddIns - why would it need to be
in a specific C: drive folder?

Why is the open .xla code that you suggested different from this
referencing code that I tried that did not work?

Private Sub CreateReference()
With Application.AddIns.Add(Filename:="C:\Program Files\Microsoft
Office\Office\Library\ExcelReportFunctions.xla", copyfile:=False)
.Installed = True
Application.VBE.ActiveVBProject.References.AddFromFile
Filename:=.FullName
End With
End Sub
' Auto-run at worksheet open to retrieve data
' ===========================================
Private Sub Workbook_Open()
CreateReference
result = retrieveData() ' This is the function in my XLA
End Sub

Do you have some example code to Open the .xla or is it as simple as
workbook.open? (I will try it out later today).

Why would it be better to have code copy the .xla to the user's temp
folder rather than having a global login script to do it?

And if all this is the case, why doesn't it actually work when any
other user opens the worksheet even if they have the .xla copied to
their C: drive?

Sorry to be a pain asking all these questions but I just don't quite
have an answer that can help yet.

Thanks

 

[Dave Peterson]

Put a formula that contains a UDF from your addin.

Then click on tools|Links. You'll see that the path to that addin doesn't
contain ..\%username%\.., it actually contains ..\jodiem\.. (the name is
essentially hard wired into the path).

So that when you share a workbook with a formula that contains a UDF, then excel
is gonna look for that exact path. Excel won't just go off and fix things the
way you want.

One way around it is to make sure that the path is spelled exactly the same
way--no changes in drive, folder, or filename. That's why I suggested
C:\jodiemutils\ as a path. I'm assuming that every user will have a C: drive
and can make a folder named jodiemutils on that drive.

You could also put it in a common network folder. But make sure that they map
to that drive using the same letter (or connect via the UNC path).

I'm not sure why adding a reference--even via a program--would be better than
putting the addin in a nice spot.

 

[JodieM]

Thanks Dave, I'm sure I have tried that (our server's path is the R:
\drive) but it did not work - the next time I opened the workbook the
reference to the .xla was lost. I'm at a conference for a few days so
I will try it again when I get back and report back.

 

[JodieM]

I tried referencing the .xla on the R:\ Drive but I just had no luck.
The references would be lost on re-opening the file and I just could
not get the Functions to work at all.

So I gave up and replaced the functions with macro's and created a
macro to open the .xla on open of the worksheet. It's a bit of a
kludge work around because the path of the .xla is hard coded into
every copy of the document that ever exists, but it works.