Calling a common XLA Library file, stored on a network drive :)

[eXcellence]

Hi Forumites,


Im new to this forum, dont ya just love google!.
--->>


This should be an easy one for you.


I have a test XLA file, that i want to add more useful functions to, so

i can use this site wide.


For example:
-----
Public Function Get_Time() As Date
Get_Time = Time
End Function
Public Function Get_Date() As Date
Get_Date = Date
End Function
Function Trunc_String(InString As String, Chars As Long) As String
Trunc_String = Left(InString, Chars)
End Function


Public Function test_fun()
MsgBox ("fff")


End Function
-----


I want to be able to create Excel files that use these functions. then
give the Excel XLS file to the users. These are usually tools for
formatting/extracting data etc.


How can i ensure that when i give the file to a user, it will pick up
the latest XLA file. Id also like to put the XLA file on a network
drive, so we can all use the one library file.


Usually you need to add the XLA file to the references too, but i dont
(or cant rely) on the users to do this. i need the XLA file to be
called from the code in some way, so the user doesnt need to worry
about it.


Any suggestions please


Arty.

 

[Bob Phillips]

xla files should be installed on the client desktop, no need for references
then. If you want to ensure they have the latest version you could have an
test in the add-in open that goes to the network and see if a locally stored
version number is the same as the network stored master, if not warn the
user to download the latest version.

 

[keepITcool]

Bob, I respectfully disagree.


xlas can be installed in a shared network drive
without problems. I point to maintain.


PROVIDED
they do not contain tables and need to be saved.
(because then you run into multiuser or readonly issues.)


Re References:
I think OP means links not references.
IF an xla contains udf functions the files using those functions
will have a link to the xla.

And you DO have linking issues on a local machine too,
if you move the xla to different directory, or rename them.
btw: C+ compiled xll files do not have this problem.

For VBA addins however this can be solved with an application level
handler. the NEW xla must be loaded, but then it will check opened
workbooks and workbook_open events: Verify that links (if any) do not
point to "old xla", if so modify to point to current.

Note You only need this for existing xla's that you want to move
can be fairly easily accomplished.
and IMO is far less involved then a massive scan on the network.


an example from 1 of my addins:
'CLASSMODULE NAMED CAppEvents
Option Explicit
Option Compare Text

Dim WithEvents xlApp As Application

Private Sub Class_Initialize()
RelinkAll
Set xlApp = Application
End Sub

Private Sub Class_Terminate()
Set xlApp = Nothing
End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
Relink Wb
End Sub

Public Sub RelinkAll()
Dim Wb As Workbook
For Each Wb In Workbooks
Call Relink(Wb)
Next
End Sub

Public Sub Relink(Optional ByVal Wb As Workbook)
Dim lk As Variant
If IsEmpty(Wb.LinkSources(xlLinkTypeExcelLinks)) Then Exit Sub
For Each lk In Wb.LinkSources(xlLinkTypeExcelLinks)
If lk Like "*" & ThisWorkbook.Name And lk <> ThisWorkbook.FullName
Then
Wb.ChangeLink lk, ThisWorkbook.FullName, xlLinkTypeExcelLinks
End If
Next
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

 

[Bob Phillips]

Bob, I respectfully disagree.

xlas can be installed in a shared network drive
without problems. I point to maintain.


PROVIDED
they do not contain tables and need to be saved.
(because then you run into multiuser or readonly issues.)

I agree that the file can be stored on a network drive (I do it myself
frequently, so I should have made that point). There are 2 issues with this
that need to be addressed though
1) the addin still needs to be installed on the desktop (which was my real
point), either the user does it, or some desktop administrator does (I
personally see no point in loading xla's as normal Excel files)
2) the network drive has to be available (things get better, but they aren't
always there)

If you do store the xla on a network drive, you remove the version issue of
course, but you still need to address thge 2 points I mention above, but
this probably makes it worthwhile all things considered.

Your point about links is well made, but as you point out, this applies to
local as well as to network files.

I don't think we are in disagreement, I was just incomplete in my answer,
for which you have clarified.

Regards

Bob

 

[keepITcool]

re 1:
no difference for local or network:
the addins need to be activated.

a difference between directories:
inside the UserLibraryPath and LibraryPath
they are automatically shown in the addins list.
outside these paths they need to be "browsed to".

admin can easily add a line to registry in
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Add-in Manager
and browsing is taken care of. he does need to do it for all users.

if he wants a "silent" install, with addin not visible
in the browse list, he needs to add an open line in
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options
no point maybe.. but users cannot deactivate, only unload via vbe.

if a shared drive is not available you'll have lot's more to worry
about. prerequisite: a stable network. dont map but use UNC.

there's some resiliency options too.. that many people dont know about
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options

Value Name: NetworkResiliency
Value Type: DWord
Value Data: 0 or 1


As you know programmatic addin install/uninstall is troublesome.
and errorhandling is non-existent.
also why is there no method to install addin as readonly?
(except a manual edit of the OPEN line to add /R

... life ain't perfect and neither is excel..


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :