Automaticaly update a addin
Hello
I have a Excel Addin Utility on each work station and a Master file on a network server.
The Addin on the local machine is called from a .vbs File that is called from a short cut in a accounting system.
When the Addin is called I need it to check if the Local Version is the latest and if not Either give the user an option to update or just update the local Addin file with the Master file from the Network Server.
Thanks
I have a Excel Addin Utility on each work station and a Master file on a network server.
The Addin on the local machine is called from a .vbs File that is called from a short cut in a accounting system.
When the Addin is called I need it to check if the Local Version is the latest and if not Either give the user an option to update or just update the local Addin file with the Master file from the Network Server.
Thanks
ASKER
The addins are on local machines as otherwise I have to much trouble getting everyone to close Excel when I want to edit the code
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ahh okay, well check out the DDoE link, in particular, there's a point where Harold Staff states:
I'm off to bed now & probably won't get another chance to look at this until after work tomorrow. I hope someone else can help you in the mean time... unless of course, you solve it by yourself with the help of my links ;-)
Rob
"Nice thing then is; you can code it to open read-only, for safety, code is not to be fixed and saved by users at rare runtime errors. And you can set something whatever that prevents it from opening at user machines at times when you want to maintain and fix the addin. The “file is in use” problem is pretty volatile when it comes to Excel addins, and I for one do not want to do maintenance coding at 4am."
I'm off to bed now & probably won't get another chance to look at this until after work tomorrow. I hope someone else can help you in the mean time... unless of course, you solve it by yourself with the help of my links ;-)
Rob
hmmm, my connection seems to be hiccuping!
Dave, I didn't see your post but I agree - go for simplicity. It's all yours for a few hours :-)
Rob
Dave, I didn't see your post but I agree - go for simplicity. It's all yours for a few hours :-)
Rob
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Not for points:
Regarding the server location of the add-in, which I totally agree with, I recommend reading this: http://www.excelguru.ca/node/45
Regarding the server location of the add-in, which I totally agree with, I recommend reading this: http://www.excelguru.ca/node/45
hi everyone,
Rory that's a great link!
Yep, the helpers are all in agreement & now the OP can weigh up the pros & cons of the different options and fire away with any further questions.
Dave, btw, my previous "hmmm" (#36953086) was not directed at you, it was purely because I think my connection was playing up - I'm sure that I hit refresh before posting my comment (#36953076) but I wasn't able to see your comment (#36953008) until after mine submitted (#36953076). With more than 20 minutes between them there is no way that they should have passed each other in cyberspace!
Rob
Rory that's a great link!
Yep, the helpers are all in agreement & now the OP can weigh up the pros & cons of the different options and fire away with any further questions.
Dave, btw, my previous "hmmm" (#36953086) was not directed at you, it was purely because I think my connection was playing up - I'm sure that I hit refresh before posting my comment (#36953076) but I wasn't able to see your comment (#36953008) until after mine submitted (#36953076). With more than 20 minutes between them there is no way that they should have passed each other in cyberspace!
Rob
ASKER
Hello
I currently have the Addin located on the Network Server BUT when I want to Update/Edit the Code I get the "file is in use" Error.
How do I get arround that?
Also Dave How do I make the local machine copy the file at boot time as part of their login script.
Thanks
I currently have the Addin located on the Network Server BUT when I want to Update/Edit the Code I get the "file is in use" Error.
How do I get arround that?
Also Dave How do I make the local machine copy the file at boot time as part of their login script.
Thanks
Did you make it read-only?
ASKER
How?
By Right Clicking and selecting Read Only?
By Right Clicking and selecting Read Only?
Yes - or use code like in Ken's article.
I think we all agree, that the add-in should be read-only whether deployed on the local machine, or on the server. As a result, the add-in itself can have code to fetch a new copy of itself and plant it in the right location - should be no need to include in boot login script. Let me know if there are issues with that, but it should work.
Windows 7 still has autoexec.bat, so you can add the logic to copy it over from there, if need be.
Dave
Windows 7 still has autoexec.bat, so you can add the logic to copy it over from there, if need be.
Dave
ASKER
How can I code the local addin to fetch a new copy?
The simplest method is to run it from the server and not use local copies at all.
I agree with rorya. Here's some rope :)
>>How can I code the local addin to fetch a new copy?
I think the question is perhaps, WHEN is the right event to fetch a new copy?
As part of the add-in initialization process, just have it do the work (e.g., before or after it creates the add-in menu, for example, if your addin has menus - the auto_open process or workbook_open event?)
Repeat - keep it on the server (mantra from my first post, echoed first by Rob and by rorya). However, there's at least one instance you might need it locally - if the add-in is needed to machines that are sometimes not connected to the network/server, and having it in an offline file perhaps is unwieldy to manage...
Dave
>>How can I code the local addin to fetch a new copy?
I think the question is perhaps, WHEN is the right event to fetch a new copy?
As part of the add-in initialization process, just have it do the work (e.g., before or after it creates the add-in menu, for example, if your addin has menus - the auto_open process or workbook_open event?)
Repeat - keep it on the server (mantra from my first post, echoed first by Rob and by rorya). However, there's at least one instance you might need it locally - if the add-in is needed to machines that are sometimes not connected to the network/server, and having it in an offline file perhaps is unwieldy to manage...
Dave
Hello,
A lot of good comments and info here. I would like to share the way I handle a similar situation in case it may be of any use ( I have not read all the links so my apologies if covered in one of those). FYI our team is on XP with Excel 2003.
Instead of an add in, I use a file called 'menumaster.xls' with all the utilities we use. Basically I use it like a shared personal.xls file. It loads from a network folder just like a personal.xls workbook would because I placed a shortcut to it in my \xlstart folder.
Then in the VBE I make all my mods to the file. When done, I have a module to make read only copies in the same directory for the users. In my case I make a separate read only file copy for each user (johnmenu.xls, marymenu.xls, etc) although you could use just one copy. My intent was to eventually have customized menus for each user (about 8 right now).
The key is the copy needs to be read only so a) you can update anytime even while they have excel open and b) no file open warnings if they open a second or third instance of excel which we always do. The file also needs to have all worksheets hidden so it loads like a personal.xls file would. Lastly, having the shortcut in the \xlstart folder prevents macro warnings.
Each user needs to be setup one time with shortcut to the read only file on the network. If you have a lot of users, probably easier to do the single shared file setup through a vb script.
I have about 40+ macros that are used through this many of them as parts of a combination. The nice thing is I can update on the fly, for instance the other day I had just created a new report module for one of the team to extract/filter data and create/copy to a new workbook. She was on the phone with me asking for minor changes that I was done almost as soon as we hung up. I saved my file, hit the copy button to copy my file to the read only copy, texted her to close and re-open excel and viola' she had the updates.
To get my menu structure similar to a custom add-in, I use the John Walkenbach's awesome free Menu Maker as the base for the file. This allows me to quickly add, update, delete items that show up in a custom menu bar.
http://spreadsheetpage.com/index.php/file/menu_maker/
I realize this goes beyond the question but hoepfully may provide some helpful ideas on how to get what you need.
regards,
matt
A lot of good comments and info here. I would like to share the way I handle a similar situation in case it may be of any use ( I have not read all the links so my apologies if covered in one of those). FYI our team is on XP with Excel 2003.
Instead of an add in, I use a file called 'menumaster.xls' with all the utilities we use. Basically I use it like a shared personal.xls file. It loads from a network folder just like a personal.xls workbook would because I placed a shortcut to it in my \xlstart folder.
Then in the VBE I make all my mods to the file. When done, I have a module to make read only copies in the same directory for the users. In my case I make a separate read only file copy for each user (johnmenu.xls, marymenu.xls, etc) although you could use just one copy. My intent was to eventually have customized menus for each user (about 8 right now).
The key is the copy needs to be read only so a) you can update anytime even while they have excel open and b) no file open warnings if they open a second or third instance of excel which we always do. The file also needs to have all worksheets hidden so it loads like a personal.xls file would. Lastly, having the shortcut in the \xlstart folder prevents macro warnings.
Each user needs to be setup one time with shortcut to the read only file on the network. If you have a lot of users, probably easier to do the single shared file setup through a vb script.
I have about 40+ macros that are used through this many of them as parts of a combination. The nice thing is I can update on the fly, for instance the other day I had just created a new report module for one of the team to extract/filter data and create/copy to a new workbook. She was on the phone with me asking for minor changes that I was done almost as soon as we hung up. I saved my file, hit the copy button to copy my file to the read only copy, texted her to close and re-open excel and viola' she had the updates.
To get my menu structure similar to a custom add-in, I use the John Walkenbach's awesome free Menu Maker as the base for the file. This allows me to quickly add, update, delete items that show up in a custom menu bar.
http://spreadsheetpage.com/index.php/file/menu_maker/
I realize this goes beyond the question but hoepfully may provide some helpful ideas on how to get what you need.
regards,
matt
ASKER
Still working on it
I'm off to bed soon & haven't actually coded in VBS (or in VBA to update addins) but I just came across these links which may help you find an answer or an alternative approach by yourself:
http://www.dailydoseofexcel.com/archives/2009/07/29/conditionally-load-excel-add-ins/
which refers to Charles Williams' "Addin Loader: http://www.decisionmodels.com/Downloads/AddLoaderV2.zip which is included on http://www.decisionmodels.com/downloads.htm#addload
The discussion on DDoE (dailydoseofexcel) gives a number of alternatives & provides some challenges, for example, do the addins need to be on Local machines?
Although the Addin Loader is in VBA, I'm sure you would be able to change it (or use the concepts) to run from VBS.
hth
Rob