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

Hiya Forumittes,

Progress!!!

I can now call and update a Library (xla) file from the network and
install it when the workbook opens.

But << there is always a but...

1) I call and install the Library file.

Private Sub Workbook_Open()

Application.DisplayAlerts = False

AddIns("Excel_Library3").Installed = False 'To remove current link
AddIns.Add Filename:= _

"G:\Users\Development_Applications\Site_Library\Ex cel_Library3.xla"
AddIns("Excel_Library3").Installed = True
Application.DisplayAlerts = True

If AddIns("Excel_Library3").Installed = True Then
MsgBox "add-in is installed"
Else
MsgBox "add-in is not installed"
End If
End Sub


* Good so far... i get a message saying "add-in is installed"

2) for testing i have put a button on the sheet just to call a function
in the library.

Private Sub CommandButton1_Click()
MsgBox (get_date())
MsgBox (get_time())
End Sub

* I push the button and i get an error "Sub or Function not defined"

* i stop the program and look at the references, the library is NOT
ticked.
* if i tick it, and type in "get_" (then press Ctl_Space, to do a word
completion the function "get_date and get_time" are visible.

* i run the program again. and it fails.

----
This is the code in the library, all PUBIC stuff, so im at a loss.

Public Function Get_Time() As Date
Get_Time = Time
End Function
Public Function Get_Date() As Date
Get_Date = Date
End Function

-------

Im soooo close, i recon, i need to force the TICK somehow, but i
thought this piece of code did that:

AddIns("Excel_Library3").Installed = True

Have you tried?

MsgBox application.run("Excel_Library3.xla!get_date()")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"eXcellence" wrote in message
ups.com...
Hiya Forumittes,

Progress!!!

I can now call and update a Library (xla) file from the network and
install it when the workbook opens.

But << there is always a but...

1) I call and install the Library file.

Private Sub Workbook_Open()

Application.DisplayAlerts = False

AddIns("Excel_Library3").Installed = False 'To remove current link
AddIns.Add Filename:= _

"G:\Users\Development_Applications\Site_Library\Ex cel_Library3.xla"
AddIns("Excel_Library3").Installed = True
Application.DisplayAlerts = True

If AddIns("Excel_Library3").Installed = True Then
MsgBox "add-in is installed"
Else
MsgBox "add-in is not installed"
End If
End Sub


* Good so far... i get a message saying "add-in is installed"

2) for testing i have put a button on the sheet just to call a function
in the library.

Private Sub CommandButton1_Click()
MsgBox (get_date())
MsgBox (get_time())
End Sub

* I push the button and i get an error "Sub or Function not defined"

* i stop the program and look at the references, the library is NOT
ticked.
* if i tick it, and type in "get_" (then press Ctl_Space, to do a word
completion the function "get_date and get_time" are visible.

* i run the program again. and it fails.

----
This is the code in the library, all PUBIC stuff, so im at a loss.

Public Function Get_Time() As Date
Get_Time = Time
End Function
Public Function Get_Date() As Date
Get_Date = Date
End Function

-------

Im soooo close, i recon, i need to force the TICK somehow, but i
thought this piece of code did that:

AddIns("Excel_Library3").Installed = True

eXc.

In my experience you either have an XLA for installation (in Addins
list) OR you use it as a library, *but not both*.
Since it is a library that only gets used by other XL files, my
suggestion is *not* to install it, just reference it manually from all
XL files that you want to use it for. For this you need your XLA to be
accessible form just 1 place (a network share) in readonly mode (see
keepITcool's mail or just make excel file readonly). To prevent Button
Macro reference errors pls first manually reference your XLA, and
*then* start developing your XLS files. I suspect you have buttons not
referencing your latest addin, but an older/other version of it. This
happens when you first develop and afterwards create an addin. The
reason is that all Sheet and Toolbar buttons contain their own file
reference, and that overrides the VBA reference at all times.
To keep Button references and library references in sinc. you need to
first link your XLA with the VBA reference, and then for all buttons
enter just the procedure name as macro name (you cannot select XLA
macro's form the list).


DM Unseen

Many Thanks ! for your replies...

Im off to have another play with it.

:)

YAY !!!

Many Thanks Bob .. I tried your call

Have you tried?
MsgBox application.run("Excel_Library=AD3.xla!get_date()" )

But i needed to remove the ().

so MsgBox application.run("Excel_Library=AD3.xla!get_date")
works like a charm.

I will leave the code that installs the newest version of the XLA from
the network though, as it keeps the version that its calling up to
date.

Many Many application.run("Thanks.xla!Heaps")

eXcellence

"eXcellence" wrote in message
oups.com...
YAY !!!

Many Thanks Bob .. I tried your call

Have you tried?
MsgBox application.run("Excel_Library*3.xla!get_date()")

But i needed to remove the ().

so MsgBox application.run("Excel_Library*3.xla!get_date")
works like a charm.

Many Many application.run("Thanks.xla!Heaps")

LOL. Glad it worked for you.