Deployment of Excel addin (.xla file)

[Nayan]

Hello,

I need a process to install updated addin files (about 5 of them ) from
server to local machine each time excel ( version 2003) is started. This is
for about 30 users.

Objective is to have addin files from the server always copied to excel
start up path so that each user has the latest addin on their machine. if the
copying process fails then roll back the entire copy.

I am thinking of running a batch file (.BAT) during Excel start up ( I have
seen this process in place) but does not know how to start a batch file from
excel start up such that it copies all the addins and each addin should load
during same start up creating custom menus.

Any suggestions /alternate solutions are welcome.

Thanks in advance

Nayan

 

[Halim]

Nayan,

Place the .xla in the
C:\document and
settings\username\application\microsoft\excel\xlstart\blabla.xla

that xla must has code that installing or copying another file to it's path.

for custom menu see in www.jkp-ads.com

 

[Nayan]

Halim,
Thanks for the reply. I already have the addins in start up path.
I need to copy updated addins to this startup path using an external
deploment process.

Are you suggesting to create an addin that copies other addins from the
server?

Thanks again

Nayan

 

[gimme_this_gimme_that]

So that we're on the same page ...

Addins don't go in the start up directory, or the local directory,
they go in the add-ins directory.

 

[Randy Harmelink]

Question -- Do you plan to share workbooks that use any add-in
formulas? If so, you'll want all machines to have the add-in in the
same location. That's because when you save a workbook that uses a
formula from an add-in, the location of the add-in is saved with the
function. If the workbook is then opened on another machine where the
add-in is located elsewhere, you get various warning messages and
dialogs that need to be completed in order to update the links. How
many difficulties this causes will also depend on the version of EXCEL
you're running. The add-in loading process when opening a workbook
varies from version to version.

 

[Randy Harmelink]

Actually, they can be put almost anywhere you choose. You just have
to tell EXCEL where to find it if you want to use it (i.e. "Browse").
For personal use, the add-ins directory is usually the best choice, so
they are all in one spot. But there can be reasons not to put them
there.

 

[GS]

Hi Nayan,

One possible solution:
What we do here is store the add-ins on the network server and have
everyone's Addins Manager point to it there. You will have to make sure that
Excel doesn't try to copy the file to the user's local addins folder.
Otherwise, this does make updating a lot simpler.

hth
Garry

 

[Nayan]

Randy,

Thanks for the reply. I completely agree with you that addins can be stored
any where you need them.

I have several solutions developed where addin was copied to a custom
location.

In this case add in is copied at the same location for all the user as the
deployment is done by a batch file running from windows startup. The same
batch file runs for all the users.

Requirement here is to eliminate forced rebooting of user machine to obtain
latest version of addin on their mahcine.

Thanks,

Nayan

 

[Nayan]

Garry,

Thanks for the reply.

Can I replace the addins on server while Addins Manager from all user
machine point to the server?

We update this addins VBA code frequently and need to provide updated addins
to users.

Thanks

Nayan

 

[GS]

Hi Nayan,

Yes! All users will be using the same addin file from the server location.
You don't have to distribute anything to anyone's local machine. You may not
be able to replace/overwrite the file while other users have it open, but
that's only a matter of time management. Just schedule the update for when
users aren't going to have the file open. (You can not replace/overwrite
files while they are "in use" while distributing either)

You should have it located locally on your own machine so you can make
revisions as/when it's convenient for you to do so. Then copy it to the
server when your ready for users to use the revised version.

If the addin uses a specific workbook (other than the addin) or any other
files for anything, they may have to be shared if more than one user will
have it open at the same time.

This assumes the addin is a .XLA file, and all related files are stored in
the same folder. Note that users must have access permission to the network
folder.

hth
Garry
---

 

[Nayan]

Thanks Garry,

I will take a shot at this.

-Nayan

 

[gimme_this_gimme_that]

I recommend against using a solution that stores addins in a public
place.

I also recommend against using a solution where an installation script
goes into user's computers and stores addins on their computers
without their knowing it.

I advise a system where the latest addin is stored on a shared drive
and users are trained to install it in Excel's addin directory.

I know you are considering one of the two solutions I recommend
against.

I'm warning you now ... the road to hell starts out with good
intensions.

Here goes:

1. You shouldn't deviate from the default way Excel looks for addins
which allow users to find them in Excel's menu and attach and unattach
them.

2. A user might make changes to the public addin that require saving,
and you don't want users overwriting stuff. And you don't want to
force upon the users the requirement that they can't save the addin
because that may restrict functionality that you might later want to
build into the addin.

3. A bad release will screw up every user. A single hosed release
could result in your getting a reputation as sloppy developer. Not
only that, but you could lose your job if somehow users can use Excel.

4. You end up with a deployment system where no one knows for certain
if they recieved the latest release. What happens if someone turns
off their computer and they didn't get the latest release?

I recommend that you train users on how to install add-ins onto their
own computers and that you notify them by email when new changes are
available. This gives users the opportunity to save the old add in and
try the new one before staying with it. It keeps users informed that
you are improving the addin code.

It may be a hassle training the users, but in the long run then end up
learning a little about VBA, addins, and will better appreciate your
work.

 

[GS]

Wow! I think your overstating things a bit here!

While you advise against storing the addins in a "public place", you do
advise storing them on a "shared drive". Since a public place is somewhere
everyone has access to, and a shared drive is somewhere everyone has access
to, ..???

<<I also recommend against using a solution where an installation script
goes into user's computers and stores addins on their computers without their
knowing it.>>
Software deployment on corporate networks is usually done with a script via
Active Directory, in compliance with Domain Group Policy. Since the computers
likely belong to the company, and the company dictates Group Policy, ..users
don't usually have much say in what gets installed on their workstations.

<<1. You shouldn't deviate from the default way Excel looks for addins which
allow users to find them in Excel's menu and attach and unattach them.>>
How Excel manages addins is: according to how they're listed in the Addins
Manager key of the Registry. When they're installed, the listed addin gets
moved to the Options key and is assigned an "OPEN" key according to the
installed order. ie: OPEN, OPEN1, OPEN2, etc. How they're listed is: the full
path and filename of the addin, so it doesn't matter where they're located.
(That's why there's a "Browse..." button in the Addins Manager dialog)
Storing addins on the server and having everyone point their Addins Manager
to that location ensures that everyone has the current version whenever they
start Excel because Excel's Addin Manager looks for it there.

You suggest users have access to the VBA Project, and thereby might make
changes to the addin. I didn't get that impression from the OPs post. In most
cases, company-wide addins are usually locked.

I agree that a bad release reflects poorly on the developer. However, there
is much to be said about user feedback, which is typically why addins get
revised. Changes to the task criteria are another reason, but not as common
as user feedback and suggestion input.

It is my hope and intention that you find this info helpful!

Regards,
Garry
---

 

[gimme_this_gimme_that]

Yes. This was confusing.

While you advise against storing the addins in a "public place", you do
advise storing them on a "shared drive". Since a public place is somewhere
everyone has access to, and a shared drive is somewhere everyone has access
to, ..???

I meant that the addins shouldn't be loaded from a common directory
from within Excel.

Addins should be loaded from the user's Addin Directory.

Everyone should be on board with where to find the latest release.

Software deployment on corporate networks is usually done with a script via
Active Directory, in compliance with Domain Group Policy. Since the computers
likely belong to the company, and the company dictates Group Policy, ..users
don't usually have much say in what gets installed on their workstations.

What you say is true for "field users". But its not true for business
analysts who need addins.
Business Analysts can usually admin their own machines.

installed order. ie: OPEN, OPEN1, OPEN2, etc. How they're listed is: the full
path and filename of the addin, so it doesn't matter where they're located.

I understand. My recommendation is to store addins in the addin
directory. Always.

You suggest users have access to the VBA Project, and thereby might make
changes to the addin. I didn't get that impression from the OPs post. In most
cases, company-wide addins are usually locked.

If the addin is stored locally and the business analyst knows what
he's doing then what's the problem.
Otherwise you addin developer gets into the business of having to
satisfy dozens of users whose machines probably vary.

I agree that a bad release reflects poorly on the developer. However, there
is much to be said about user feedback, which is typically why addins get
revised. Changes to the task criteria are another reason, but not as common
as user feedback and suggestion input.

What's your point? Who would argue that user feedback isn't important?

 

[Randy Harmelink]

An alternative solution you may want to take a look at -- "Updating An
add-in Through the Internet":

http://www.jkp-ads.com/articles/updateanaddin.asp

There may be some techniques there that would be of use.

 

[gimme_this_gimme_that]

If you're going down that road, why not put your addin VB in an XML
file and read the XML off the web?

I fathom you'd end up with code much simpler than that posted
http://www.jkp-ads.com/articles/updateanaddin.asp .

 

[GS]

I meant no offence. While most of your content may be valid in the context of
your situation or experience, I didn't see how it relates to the OP's
situation.

Read (below in your reply) the lines marked **

Yes. This was confusing.

**
I meant that the addins shouldn't be loaded from a common directory
from within Excel.

Addins should be loaded from the user's Addin Directory.

Everyone should be on board with where to find the latest release.

**
My thoughts on this are:
1. What if the user doesn't update the addin according to the company
schedule?
2. Storing it on the server ensures all users have the current version.
3. Excel doesn't care where the addin is located!
4. Who made this rule that "Addins should be loaded from the user's Addin
Directory"?

**
What you say is true for "field users". But its not true for business
analysts who need addins.
Business Analysts can usually admin their own machines.

**
The OP suggests that this is for "field users"!

I understand. My recommendation is to store addins in the addin
directory. Always.
**
If the addin is stored locally and the business analyst knows what
he's doing then what's the problem.
Otherwise you addin developer gets into the business of having to
satisfy dozens of users whose machines probably vary.

**
Assuming the OPs users are business analysts AND assuming the company
doesn't care about the frequent updates the OP states are required, AND the
company doesn't care about the time it'll take to make sure everyone has the
current version installed.., then there's no problem. My point was to address
the OP's needs "as stated", not propagate my own agenda.

What's your point? Who would argue that user feedback isn't important?

**
My point is that feedback from users about a bad release should encourage
the developer to smarten up some so as to thoroughly test his project before
releasing it.

 

[Nayan]

Thanks a lot to all the people with lot of replies and their ideas.

I will add some of my requirement/thoughts.

1) The user group does not care weather they have old or new addin.

2) This group works with online data crunching and they are just concern
that when they start excel they should "magically" have latest application
(addins) and they don't want to do any extra bit to download and install
these files as addins at every updates.

3) The group size is almost 50 and growing so if I have to train them or
support them for maintaining addins manually I will be a full time "install
addin" support personal instead of a Developer!!!!

4) Not to mention the frequency of updating addins is also increasing with
lots of new bussiness and audit rules.

This drives me to think of a solution where there is as minimum or no
interaction with users to maintain these addins.

Hope I am not offending anyones suggestion here.

Thanks,

Nayan

 

[gimme_this_gimme_that]

1) The user group does not care weather they have old or new addin.

How ridiculous. Do they care if their results are correct or not?

2) This group works with online data crunching and they are just concern
that when they start excel they should "magically" have latest application
(addins) and they don't want to do any extra bit to download and install
these files as addins at every updates.

So you should put the magic data in a database, not in an addin.
User's shouldn't have to load a new addin everytime the data changes,
even if you do it for them.

Putting the data into a database also makes it so that changes to the
data can be reflected to users in real time.

If you don't have the skill to program using database, I guess you
have a plan - but it's not a good plan.

3) The group size is almost 50 and growing so if I have to train them or
support them for maintaining addins manually I will be a full time "install
addin" support personal instead of a Developer!!!!

If you build the addin correctly the first time they'll only have to
install it once.

4) Not to mention the frequency of updating addins is also increasing with
lots of new bussiness and audit rules.

Frequency is an agrument in favor of storing the data in a
database ...

NOW you're in the busines of making new addins everytime something in
the business changes.

You're also in the business of confirming the users didn't turn of
their machines when you distributed those addins.

NOW you're responsible if someone doesn't have the latest data.

This drives me to think of a solution where there is as minimum or no
interaction with users to maintain these addins.

Hope I am not offending anyones suggestion here.

Why should anyone be offended if you're not up to a programming task
that you can't handle?