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 wrote:

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.AddFrom File
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