Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a common XLA Library file, stored on a network drive :)
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a common XLA Library file, stored on a network drive :)
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. -- jmoffat ------------------------------------------------------------------------ jmoffat's Profile: http://www.excelforum.com/member.php...fo&userid=1151 View this thread: http://www.excelforum.com/showthread...hreadid=379559 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a common XLA Library file, stored on a network drive :)
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a common XLA Library file, stored on a network drive :)
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 = False 'To remove current link AddIns.Add Filename:= _ "\\servername\AddinDirectory\Y*ourAddin.XLA" AddIns("Your Addin Title").Installed = 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. |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.printing,microsoft.public.excel.setup,microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.printing,microsoft.public.excel.setup,microsoft.public.excel.misc
|
|||
|
|||
Calling a common XLA Library file, stored on a network drive :)
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 |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.printing,microsoft.public.excel.setup,microsoft.public.excel.misc
|
|||
|
|||
Calling a common XLA Library file, stored on a network drive :)
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 |
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel.printing,microsoft.public.excel.setup,microsoft.public.excel.misc
|
|||
|
|||
Calling a common XLA Library file, stored on a network drive :)
Many Thanks ! for your replies...
Im off to have another play with it. :) |
#9
Posted to microsoft.public.excel.programming,microsoft.public.excel.printing,microsoft.public.excel.setup,microsoft.public.excel.misc
|
|||
|
|||
Calling a common XLA Library file, stored on a network drive :)
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. 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 |
#10
Posted to microsoft.public.excel.programming,microsoft.public.excel.printing,microsoft.public.excel.setup,microsoft.public.excel.misc
|
|||
|
|||
Calling a common XLA Library file, stored on a network drive :)
"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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2003 file converted to .xlsm file when save to network drive | Excel Discussion (Misc queries) | |||
how can I check the current owner of the file stored in a common D | Excel Discussion (Misc queries) | |||
Calling a common XLA Library file, stored on a network drive :) | Excel Discussion (Misc queries) | |||
Calling a common XLA Library file, stored on a network drive :) | Setting up and Configuration of Excel | |||
Need Macro1 to call Macro2 stored on Network drive | Excel Programming |