Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Referencing .XLA file on a Server
Hi,
I have a set of macros that are used for company standard documents. The Macro's are in an .xla file that I need to have reside on the server as 100+ people will be using the documents that reference these macros. I am having trouble with how to correcly refernce the .xla so that it will either always reference the .xla on the server (I don't mind if it's hard coded) or that it will correctly reference the .xla on the individual user's C:\ drive (excel addins) folder if the .xla is copied there via a login script. The .xla consists of macros and functions. 1.) A macro to change the logo to a new logo - it pops up a user form and then changes the logo to the newly selected logo 2.) Functions for document properties (eg http://www.cpearson.com/Excel/docprop.htm) I have tried numerouse examples posted around the groups to try and get the macros to work with no luck. Attempt 1 - Copy the .xla file to the user's addins folder on the C: \drive. When other users open the workbooks that reference the macros and functions they get a hard coded reference to the .xla file on my PC. I have seen posts and web sites saying to do it this way, and I would have no problem doing it this way, but I just can't seem to get it to work. Attempt 2 - Leave the .xla on the server and create a reference to it. I've tried creating the reference manually via the VBA interface, I've tried setting the reference through code on workbook open (as per http://groups.google.com.au/group/mi...nu m=32&hl=en) and I've tried just referencing the .xla throught he Addins dialog (as per http://groups.google.com.au/group/mi...nu m=18&hl=en) Every time I close the workbook and then re-open it the reference is lost (no matter which way I do it). One post I read said the .xla needs to be in the same folder as the workbook. this is not possible as thousands of workbooks will be created and saved all over the server, and they will still always need to be able to reference the .xla. Can anyone help with this and suggest how I set this up so it always works. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Referencing .XLA file on a Server
How about a third choice?
Tell your users to put the addin in a dedicated folder on their C: drive. Maybe: c:\JodieMUtils Then tell them to use tools|Addins to browse to this folder to install it. JodieM wrote: Hi, I have a set of macros that are used for company standard documents. The Macro's are in an .xla file that I need to have reside on the server as 100+ people will be using the documents that reference these macros. I am having trouble with how to correcly refernce the .xla so that it will either always reference the .xla on the server (I don't mind if it's hard coded) or that it will correctly reference the .xla on the individual user's C:\ drive (excel addins) folder if the .xla is copied there via a login script. The .xla consists of macros and functions. 1.) A macro to change the logo to a new logo - it pops up a user form and then changes the logo to the newly selected logo 2.) Functions for document properties (eg http://www.cpearson.com/Excel/docprop.htm) I have tried numerouse examples posted around the groups to try and get the macros to work with no luck. Attempt 1 - Copy the .xla file to the user's addins folder on the C: \drive. When other users open the workbooks that reference the macros and functions they get a hard coded reference to the .xla file on my PC. I have seen posts and web sites saying to do it this way, and I would have no problem doing it this way, but I just can't seem to get it to work. Attempt 2 - Leave the .xla on the server and create a reference to it. I've tried creating the reference manually via the VBA interface, I've tried setting the reference through code on workbook open (as per http://groups.google.com.au/group/mi...nu m=32&hl=en) and I've tried just referencing the .xla throught he Addins dialog (as per http://groups.google.com.au/group/mi...nu m=18&hl=en) Every time I close the workbook and then re-open it the reference is lost (no matter which way I do it). One post I read said the .xla needs to be in the same folder as the workbook. this is not possible as thousands of workbooks will be created and saved all over the server, and they will still always need to be able to reference the .xla. Can anyone help with this and suggest how I set this up so it always works. Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Referencing .XLA file on a Server
ps. By putting the addin in that common folder, you'll avoid problems with
formulas in the worksheets that use your UDFs. But if you're creating a new menu item or a toolbar, you may find it easier to create that toolbar/menu item each time the workbook opens. For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) JodieM wrote: Hi, I have a set of macros that are used for company standard documents. The Macro's are in an .xla file that I need to have reside on the server as 100+ people will be using the documents that reference these macros. I am having trouble with how to correcly refernce the .xla so that it will either always reference the .xla on the server (I don't mind if it's hard coded) or that it will correctly reference the .xla on the individual user's C:\ drive (excel addins) folder if the .xla is copied there via a login script. The .xla consists of macros and functions. 1.) A macro to change the logo to a new logo - it pops up a user form and then changes the logo to the newly selected logo 2.) Functions for document properties (eg http://www.cpearson.com/Excel/docprop.htm) I have tried numerouse examples posted around the groups to try and get the macros to work with no luck. Attempt 1 - Copy the .xla file to the user's addins folder on the C: \drive. When other users open the workbooks that reference the macros and functions they get a hard coded reference to the .xla file on my PC. I have seen posts and web sites saying to do it this way, and I would have no problem doing it this way, but I just can't seem to get it to work. Attempt 2 - Leave the .xla on the server and create a reference to it. I've tried creating the reference manually via the VBA interface, I've tried setting the reference through code on workbook open (as per http://groups.google.com.au/group/mi...nu m=32&hl=en) and I've tried just referencing the .xla throught he Addins dialog (as per http://groups.google.com.au/group/mi...nu m=18&hl=en) Every time I close the workbook and then re-open it the reference is lost (no matter which way I do it). One post I read said the .xla needs to be in the same folder as the workbook. this is not possible as thousands of workbooks will be created and saved all over the server, and they will still always need to be able to reference the .xla. Can anyone help with this and suggest how I set this up so it always works. Thanks -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Referencing .XLA file on a Server
As Dave notes, having a common local location on C: is useful if your add-in
has UDF's which are used in worksheet formulas. If you don't have these then using a single location has the advantage of being able to update a single file when changes need to be made. However, this is not as straightforward as it may seem, because once anyone has that add-in open, you won't be able to update the xla. Are the workbooks referencing the xla being created from a template ? If that's the case then you might consider having a workbook_open procedure just open the xla as suggested by Dave. If you need to be able to update the xla as required, then have the code first copy the server-based version to the user's temp folder, and open it from the then the server copy won't get locked. Or host the "main" copy on a web location. What works best for you may depend a lot on your supporting IT infrastructure. Tim "JodieM" wrote in message ups.com... Hi, I have a set of macros that are used for company standard documents. The Macro's are in an .xla file that I need to have reside on the server as 100+ people will be using the documents that reference these macros. I am having trouble with how to correcly refernce the .xla so that it will either always reference the .xla on the server (I don't mind if it's hard coded) or that it will correctly reference the .xla on the individual user's C:\ drive (excel addins) folder if the .xla is copied there via a login script. The .xla consists of macros and functions. 1.) A macro to change the logo to a new logo - it pops up a user form and then changes the logo to the newly selected logo 2.) Functions for document properties (eg http://www.cpearson.com/Excel/docprop.htm) I have tried numerouse examples posted around the groups to try and get the macros to work with no luck. Attempt 1 - Copy the .xla file to the user's addins folder on the C: \drive. When other users open the workbooks that reference the macros and functions they get a hard coded reference to the .xla file on my PC. I have seen posts and web sites saying to do it this way, and I would have no problem doing it this way, but I just can't seem to get it to work. Attempt 2 - Leave the .xla on the server and create a reference to it. I've tried creating the reference manually via the VBA interface, I've tried setting the reference through code on workbook open (as per http://groups.google.com.au/group/mi...nu m=32&hl=en) and I've tried just referencing the .xla throught he Addins dialog (as per http://groups.google.com.au/group/mi...nu m=18&hl=en) Every time I close the workbook and then re-open it the reference is lost (no matter which way I do it). One post I read said the .xla needs to be in the same folder as the workbook. this is not possible as thousands of workbooks will be created and saved all over the server, and they will still always need to be able to reference the .xla. Can anyone help with this and suggest how I set this up so it always works. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Referencing .XLA file on a Server
I'm not sure that second paragraph is correct.
IIRC, I used to updata an addin stored on a network drive and I never had any trouble if others had it open. (But it's been a while and I can't test it anymore.) Tim wrote: As Dave notes, having a common local location on C: is useful if your add-in has UDF's which are used in worksheet formulas. If you don't have these then using a single location has the advantage of being able to update a single file when changes need to be made. However, this is not as straightforward as it may seem, because once anyone has that add-in open, you won't be able to update the xla. Are the workbooks referencing the xla being created from a template ? If that's the case then you might consider having a workbook_open procedure just open the xla as suggested by Dave. If you need to be able to update the xla as required, then have the code first copy the server-based version to the user's temp folder, and open it from the then the server copy won't get locked. Or host the "main" copy on a web location. What works best for you may depend a lot on your supporting IT infrastructure. Tim "JodieM" wrote in message ups.com... Hi, I have a set of macros that are used for company standard documents. The Macro's are in an .xla file that I need to have reside on the server as 100+ people will be using the documents that reference these macros. I am having trouble with how to correcly refernce the .xla so that it will either always reference the .xla on the server (I don't mind if it's hard coded) or that it will correctly reference the .xla on the individual user's C:\ drive (excel addins) folder if the .xla is copied there via a login script. The .xla consists of macros and functions. 1.) A macro to change the logo to a new logo - it pops up a user form and then changes the logo to the newly selected logo 2.) Functions for document properties (eg http://www.cpearson.com/Excel/docprop.htm) I have tried numerouse examples posted around the groups to try and get the macros to work with no luck. Attempt 1 - Copy the .xla file to the user's addins folder on the C: \drive. When other users open the workbooks that reference the macros and functions they get a hard coded reference to the .xla file on my PC. I have seen posts and web sites saying to do it this way, and I would have no problem doing it this way, but I just can't seem to get it to work. Attempt 2 - Leave the .xla on the server and create a reference to it. I've tried creating the reference manually via the VBA interface, I've tried setting the reference through code on workbook open (as per http://groups.google.com.au/group/mi...nu m=32&hl=en) and I've tried just referencing the .xla throught he Addins dialog (as per http://groups.google.com.au/group/mi...nu m=18&hl=en) Every time I close the workbook and then re-open it the reference is lost (no matter which way I do it). One post I read said the .xla needs to be in the same folder as the workbook. this is not possible as thousands of workbooks will be created and saved all over the server, and they will still always need to be able to reference the .xla. Can anyone help with this and suggest how I set this up so it always works. Thanks -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Referencing .XLA file on a Server
Dave,
Has been a problem for me, so maybe there is some network setup component in there somewhere ? Tim "Dave Peterson" wrote in message ... I'm not sure that second paragraph is correct. IIRC, I used to updata an addin stored on a network drive and I never had any trouble if others had it open. (But it's been a while and I can't test it anymore.) Tim wrote: As Dave notes, having a common local location on C: is useful if your add-in has UDF's which are used in worksheet formulas. If you don't have these then using a single location has the advantage of being able to update a single file when changes need to be made. However, this is not as straightforward as it may seem, because once anyone has that add-in open, you won't be able to update the xla. Are the workbooks referencing the xla being created from a template ? If that's the case then you might consider having a workbook_open procedure just open the xla as suggested by Dave. If you need to be able to update the xla as required, then have the code first copy the server-based version to the user's temp folder, and open it from the then the server copy won't get locked. Or host the "main" copy on a web location. What works best for you may depend a lot on your supporting IT infrastructure. Tim "JodieM" wrote in message ups.com... Hi, I have a set of macros that are used for company standard documents. The Macro's are in an .xla file that I need to have reside on the server as 100+ people will be using the documents that reference these macros. I am having trouble with how to correcly refernce the .xla so that it will either always reference the .xla on the server (I don't mind if it's hard coded) or that it will correctly reference the .xla on the individual user's C:\ drive (excel addins) folder if the .xla is copied there via a login script. The .xla consists of macros and functions. 1.) A macro to change the logo to a new logo - it pops up a user form and then changes the logo to the newly selected logo 2.) Functions for document properties (eg http://www.cpearson.com/Excel/docprop.htm) I have tried numerouse examples posted around the groups to try and get the macros to work with no luck. Attempt 1 - Copy the .xla file to the user's addins folder on the C: \drive. When other users open the workbooks that reference the macros and functions they get a hard coded reference to the .xla file on my PC. I have seen posts and web sites saying to do it this way, and I would have no problem doing it this way, but I just can't seem to get it to work. Attempt 2 - Leave the .xla on the server and create a reference to it. I've tried creating the reference manually via the VBA interface, I've tried setting the reference through code on workbook open (as per http://groups.google.com.au/group/mi...nu m=32&hl=en) and I've tried just referencing the .xla throught he Addins dialog (as per http://groups.google.com.au/group/mi...nu m=18&hl=en) Every time I close the workbook and then re-open it the reference is lost (no matter which way I do it). One post I read said the .xla needs to be in the same folder as the workbook. this is not possible as thousands of workbooks will be created and saved all over the server, and they will still always need to be able to reference the .xla. Can anyone help with this and suggest how I set this up so it always works. Thanks -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Referencing .XLA file on a Server
Or maybe it's just my bad memory.
Tim wrote: Dave, Has been a problem for me, so maybe there is some network setup component in there somewhere ? Tim "Dave Peterson" wrote in message ... I'm not sure that second paragraph is correct. IIRC, I used to updata an addin stored on a network drive and I never had any trouble if others had it open. (But it's been a while and I can't test it anymore.) Tim wrote: As Dave notes, having a common local location on C: is useful if your add-in has UDF's which are used in worksheet formulas. If you don't have these then using a single location has the advantage of being able to update a single file when changes need to be made. However, this is not as straightforward as it may seem, because once anyone has that add-in open, you won't be able to update the xla. Are the workbooks referencing the xla being created from a template ? If that's the case then you might consider having a workbook_open procedure just open the xla as suggested by Dave. If you need to be able to update the xla as required, then have the code first copy the server-based version to the user's temp folder, and open it from the then the server copy won't get locked. Or host the "main" copy on a web location. What works best for you may depend a lot on your supporting IT infrastructure. Tim "JodieM" wrote in message ups.com... Hi, I have a set of macros that are used for company standard documents. The Macro's are in an .xla file that I need to have reside on the server as 100+ people will be using the documents that reference these macros. I am having trouble with how to correcly refernce the .xla so that it will either always reference the .xla on the server (I don't mind if it's hard coded) or that it will correctly reference the .xla on the individual user's C:\ drive (excel addins) folder if the .xla is copied there via a login script. The .xla consists of macros and functions. 1.) A macro to change the logo to a new logo - it pops up a user form and then changes the logo to the newly selected logo 2.) Functions for document properties (eg http://www.cpearson.com/Excel/docprop.htm) I have tried numerouse examples posted around the groups to try and get the macros to work with no luck. Attempt 1 - Copy the .xla file to the user's addins folder on the C: \drive. When other users open the workbooks that reference the macros and functions they get a hard coded reference to the .xla file on my PC. I have seen posts and web sites saying to do it this way, and I would have no problem doing it this way, but I just can't seem to get it to work. Attempt 2 - Leave the .xla on the server and create a reference to it. I've tried creating the reference manually via the VBA interface, I've tried setting the reference through code on workbook open (as per http://groups.google.com.au/group/mi...nu m=32&hl=en) and I've tried just referencing the .xla throught he Addins dialog (as per http://groups.google.com.au/group/mi...nu m=18&hl=en) Every time I close the workbook and then re-open it the reference is lost (no matter which way I do it). One post I read said the .xla needs to be in the same folder as the workbook. this is not possible as thousands of workbooks will be created and saved all over the server, and they will still always need to be able to reference the .xla. Can anyone help with this and suggest how I set this up so it always works. Thanks -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Referencing .XLA file on a Server
Well, we can't be expected to remember *everything*...
Cheers Tim "Dave Peterson" wrote in message ... Or maybe it's just my bad memory. Tim wrote: Dave, Has been a problem for me, so maybe there is some network setup component in there somewhere ? Tim "Dave Peterson" wrote in message ... I'm not sure that second paragraph is correct. IIRC, I used to updata an addin stored on a network drive and I never had any trouble if others had it open. (But it's been a while and I can't test it anymore.) Tim wrote: |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Referencing .XLA file on a Server
Sorry guys but I'm still quite confused.
First some background. Our users are not capable of referencing the .xla file themselves or doing anything that requires any input from them other than opening the file and have it all work. They are NOT technical people and are not good with computers. So the first option, unfortunately is not going to help in this situation The macros that I'm referencing in the .xla will be used in both templates AND the hundreds of worksheets that will be based on those templates. Yes the .xla does have UDF's. At the moment there are no menus but that will probably change in the future, so I need that option too. The only reason I even started on this .xla path is because I read somewhere that I could do it. I have it all working with the Word Templates through a global.dot file and now I'm just struggling with the .xla verson. I could put the macro's inside the templates but that means the macro's will then be diseminated to hundreds of documents and that's just a bit unweildy. I don't have a problem with the locking of the file on the server because I can just update it at night if it is locked by someone using it. There won't need to be urgent updates that will require 100+ users to exit excel. So now for my questions. Why does it have to be on the C: drive for the UDF's to work? If it's on the C: Drive, why cant it be in C:\Documents and Settings\ %UserName%\Application Data\Microsoft\AddIns - why would it need to be in a specific C: drive folder? Why is the open .xla code that you suggested different from this referencing code that I tried that did not work? Private Sub CreateReference() With Application.AddIns.Add(Filename:="C:\Program Files\Microsoft Office\Office\Library\ExcelReportFunctions.xla", copyfile:=False) .Installed = True Application.VBE.ActiveVBProject.References.AddFrom File Filename:=.FullName End With End Sub ' Auto-run at worksheet open to retrieve data ' =========================================== Private Sub Workbook_Open() CreateReference result = retrieveData() ' This is the function in my XLA End Sub Do you have some example code to Open the .xla or is it as simple as workbook.open? (I will try it out later today). Why would it be better to have code copy the .xla to the user's temp folder rather than having a global login script to do it? And if all this is the case, why doesn't it actually work when any other user opens the worksheet even if they have the .xla copied to their C: drive? Sorry to be a pain asking all these questions but I just don't quite have an answer that can help yet. Thanks |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Referencing .XLA file on a Server
Put a formula that contains a UDF from your addin.
Then click on tools|Links. You'll see that the path to that addin doesn't contain ..\%username%\.., it actually contains ..\jodiem\.. (the name is essentially hard wired into the path). So that when you share a workbook with a formula that contains a UDF, then excel is gonna look for that exact path. Excel won't just go off and fix things the way you want. One way around it is to make sure that the path is spelled exactly the same way--no changes in drive, folder, or filename. That's why I suggested C:\jodiemutils\ as a path. I'm assuming that every user will have a C: drive and can make a folder named jodiemutils on that drive. You could also put it in a common network folder. But make sure that they map to that drive using the same letter (or connect via the UNC path). I'm not sure why adding a reference--even via a program--would be better than putting the addin in a nice spot. JodieM wrote: Sorry guys but I'm still quite confused. First some background. Our users are not capable of referencing the .xla file themselves or doing anything that requires any input from them other than opening the file and have it all work. They are NOT technical people and are not good with computers. So the first option, unfortunately is not going to help in this situation The macros that I'm referencing in the .xla will be used in both templates AND the hundreds of worksheets that will be based on those templates. Yes the .xla does have UDF's. At the moment there are no menus but that will probably change in the future, so I need that option too. The only reason I even started on this .xla path is because I read somewhere that I could do it. I have it all working with the Word Templates through a global.dot file and now I'm just struggling with the .xla verson. I could put the macro's inside the templates but that means the macro's will then be diseminated to hundreds of documents and that's just a bit unweildy. I don't have a problem with the locking of the file on the server because I can just update it at night if it is locked by someone using it. There won't need to be urgent updates that will require 100+ users to exit excel. So now for my questions. Why does it have to be on the C: drive for the UDF's to work? If it's on the C: Drive, why cant it be in C:\Documents and Settings\ %UserName%\Application Data\Microsoft\AddIns - why would it need to be in a specific C: drive folder? Why is the open .xla code that you suggested different from this referencing code that I tried that did not work? Private Sub CreateReference() With Application.AddIns.Add(Filename:="C:\Program Files\Microsoft Office\Office\Library\ExcelReportFunctions.xla", copyfile:=False) .Installed = True Application.VBE.ActiveVBProject.References.AddFrom File Filename:=.FullName End With End Sub ' Auto-run at worksheet open to retrieve data ' =========================================== Private Sub Workbook_Open() CreateReference result = retrieveData() ' This is the function in my XLA End Sub Do you have some example code to Open the .xla or is it as simple as workbook.open? (I will try it out later today). Why would it be better to have code copy the .xla to the user's temp folder rather than having a global login script to do it? And if all this is the case, why doesn't it actually work when any other user opens the worksheet even if they have the .xla copied to their C: drive? Sorry to be a pain asking all these questions but I just don't quite have an answer that can help yet. Thanks -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Referencing .XLA file on a Server
Thanks Dave, I'm sure I have tried that (our server's path is the R:
\drive) but it did not work - the next time I opened the workbook the reference to the .xla was lost. I'm at a conference for a few days so I will try it again when I get back and report back. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Referencing .XLA file on a Server
I tried referencing the .xla on the R:\ Drive but I just had no luck.
The references would be lost on re-opening the file and I just could not get the Functions to work at all. So I gave up and replaced the functions with macro's and created a macro to open the .xla on open of the worksheet. It's a bit of a kludge work around because the path of the .xla is hard coded into every copy of the document that ever exists, but it works. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shared File Issues | Excel Worksheet Functions | |||
Issues referencing range between workbooks | Excel Discussion (Misc queries) | |||
Shared file issues | Excel Programming | |||
adodb connecting to a server issues | Excel Programming | |||
Issues in Opening a file | Excel Worksheet Functions |