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.

[jmoffat]

You could try putting a piece of code into the auto_opn subroutine [or open event in the workbook]

somthing along the lines of:

sub auto_open
xla_name="Z:\my_routines\functionlib.xla"
workbooks(xla_name).open
end sub

Unfortunately everyone would need to map your network drive to the same letter.

Alternatively, if the network drive has a web server or ftp server running then you could use:

xla_name="https://ababserver.domain.com/my_routines/functionlib.xla"

I haven't used xla's myself but this works with .xls files so maybe it may be of some use.

[BAC]

In the workbook_open event

AddIns("Your Addin Title").Installed = False 'To remove current link
AddIns.Add Filename:= _
"\\servername\AddinDirectory\YourAddin.XLA"
AddIns("Your Addin Title").Installed = True

End Sub

Then everytime the user opens the file the open event will reconnect to the
latest version of the .xla which you will keep at:
"\\servername\AddinDirectory\YourAddin.XLA"

BTW-> I got this code by using the macro recorder to connect to a .XLA on
one of our servers..That macrorecorder is a truly great tool..

BAC
"eXcellence" wrote:

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

[eXcellence]

All,

Many Thanks for the brain food... ive had a play, the closest ive got
to getting this working is using BACs script.

AddIns("Your Addin Title").Installed =3D False 'To remove current link
AddIns.Add Filename:=3D _
"\\servername\AddinDirectory\Y=ADourAddin.XLA"
AddIns("Your Addin Title").Installed =3D True


But, i get a "subscript out of range" on line #1. i comment it out. so
it goes to line #2. where it prompts me to add and overwrite the
existing file. Thats a good thing. it means the network version is
being copied over the local version.
Then i get a subscript error again on line #3.

The "Your Addin Title" name seems to be the correct. in the XLA under
properties/project name, ive called it "ExcelSiteLibrary3".
if i add this manually and tick it in references, the name is the same
"ExcelSiteLibrary3".

So the Step #1 and #3 or unticking and ticking the reference name in
theory should work.

Any more food for thought.
Again Many Thanks for all your help

Arty.

[eXcellence]

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\Excel_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

[Bob Phillips]

Have you tried?

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

--

HTH

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


"eXcellence" <[email protected]> wrote in message
news:[email protected]...
> 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\Excel_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
>

[DM Unseen]

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

[eXcellence]

Many Thanks ! for your replies...

Im off to have another play with it.

[eXcellence]

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

[Bob Phillips]

"eXcellence" <[email protected]> wrote in message
news:[email protected]...
> 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.