[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

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
0
p-plater
Asked:
p-plater
  • 5
  • 4
  • 4
  • +2
2 Solutions
 
broro183Commented:
hi P-Plater,

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
0
 
p-platerAuthor Commented:
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
0
 
dlmilleCommented:
Why not just copy the latest addin file from the server to the users' machine, and not change the add-in name?  It could be done at boot time as part of their login script.

Also, why does the add-in need to be on their local machine?  Most of mine sit on the server, itself, and are read-only.  Why even distribute copies, if everyone has server access?

Lots of ifs,ands, and buts, but sometimes simplicity works!

Dave
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
broro183Commented:
Ahh okay, well check out the DDoE link, in particular, there's a point where Harold Staff states:

"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
0
 
broro183Commented:
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
0
 
dlmilleCommented:
Your suggestion to keep it on the server - I'm agreeing with.  Perhaps what p-plater may NOT know, is that add-ins can be stored on the server, read-only.

Let's see how this lands, but I think we agree on the server hosting the add-in, unless there are cases where that's not possible - maintenance can become a chore and there's a whole knowledgebase to be tapped on maintaining addins that is avoided in the read-only add-in case.

PS - p-plater - you can also copy the latest version of the addin (should the server be or not be available, having checks for that) and write it where its supposed to be on the local machine, if absolutely necessary, if you make the local copy read-only!

Cheers,

Dave
0
 
Rory ArchibaldCommented:
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
0
 
broro183Commented:
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
0
 
p-platerAuthor Commented:
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
0
 
Rory ArchibaldCommented:
Did you make it read-only?
0
 
p-platerAuthor Commented:
How?
By Right Clicking and selecting Read Only?
0
 
Rory ArchibaldCommented:
Yes - or use code like in Ken's article.
0
 
dlmilleCommented:
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
0
 
p-platerAuthor Commented:
How can I code the local addin to fetch a new copy?
0
 
Rory ArchibaldCommented:
The simplest method is to run it from the server and not use local copies at all.
0
 
dlmilleCommented:
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
0
 
matttclarkCommented:
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
0
 
p-platerAuthor Commented:
Still working on it
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now