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 :)
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. -- HTH Bob Phillips "eXcellence" wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a common XLA Library file, stored on a network drive :)
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 : 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a common XLA Library file, stored on a network drive :)
"keepITcool" wrote in message ft.com... Bob, I respectfully disagree. Then let's respectfully discuss <bg 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a common XLA Library file, stored on a network drive :)
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\E xcel\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\E xcel\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\E xcel\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 : "keepITcool" wrote in message ft.com... Bob, I respectfully disagree. Then let's respectfully discuss <bg 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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 | |||
Calling a common XLA Library file, stored on a network drive :) | Excel Programming | |||
Need Macro1 to call Macro2 stored on Network drive | Excel Programming |