Interesting.

I have this quite complex Excel add-in that happens to - among other things, update worksheets off a SQL Server database (using ADO commands) and, when new features are released or improved, the add-in detects the new version and prompts the user whether they want to upgrade now or later.

Here's my algo:

Add-ins are saved locally on each users' machines; the latest version is always overwritten from the dev machine onto a server that all users can access (read).

The add-in's version number is hard-coded as private constants (major, minor, revision) and accessed with [clsAppVersion].major|minor|revision (string read-only properties); a method like GetAppVersion() returns the concatenation of the version number components. [clsAddIn] "has-a" [clsAppVersion].

The latest version number is stored as an application parameter (string, like "1.3.76") in a parameter table in the SQL Server database; the add-in initializes and reads all parameters, including the latest version number (methods in [clsAppVersion] break down its major/minor/revision components)

At start-up (in a method like [clsAddIn].[InitApp] but after loading the parameter values), the hard-coded version number is compared against the database-hosted latest version parameter value (in a method like [clsAppVersion].[GetVersionStatus] which can return values like 0-VersionOk, 1-RevisionChange, 2-MinorChange and 3-MajorChange.

If the version check returns 0, we can choose different paths depending on the version status, but in the end...

The add-in we intend to replace is currently installed (let alone running). Windows won't let that replacement happen.

If we close Excel we close the add-in and no code beyond "Excel.Application.Quit" will run.

Our add-in is only "aware" of the instance of Excel it's running in. The update will fail if another instance remained opened with the add-in running.

Your auto-update code shall do this:
Ensure that no dirty workbook is opened
Warn the user about Excel about to shut out
Sends an email to the user (some HTML documentation about the new version and any changes made)
Launch a batch file
Close Excel

The batch file shall do this:
Receive %1 and %2 as current and latest version numbers (for ECHO)
Wait for user to press a key (actually wait for Excel to close)
Copy a backup of the original add-in file (appending ".oldversion.%1" to the file extension)
Delete the add-in file
If delete fails, an instance of Excel remained open: prompt user to press a key and try again.
Copy the latest version of the add-in onto the user's AddIns folder
If copy fails, restore backup
Wait for a key to be pressed
Launch Excel - the add-in running is the latest version


The users' add-in logs version upgrades into a log table (which also happens to be logging any unhandled run-time errors) in the database, and all users are registered and have their own options and preferences. That's how you know which version each user is running locally.

Training the users on this kind of self-upgrade is basically "well click yes and then press enter until Excel reopens". They don't need to know about the deeply hidden Excel AddIns folder, nor about where the latest version is located. Your add-in and the upgrade batch do.

To deploy a new version you copy your dev version onto the shared location, overwriting the previous "latest version". THEN (read: AFTER THIS), you can increment the "latest version" parameter value in the SQL database: any user opening up Excel from this point on will launch the self-upgrade process.

I hate how many programmers think Excel and databases are totally opposed concepts. Excel isn't a database tool and storing data is not its purpose. But there's nothing like Excel for using and analysing data. Excel and databases are meant to work hand in hand, not one against another.

And no matter how experienced of a programmer you are, business is business and healthy business is growing business, with growing needs and homegrown solutions that follow. It's not the programmer's job to question whether or not the new features are needed, and it doesn't mean the original build was bad if new features need to be added afterwards.

Your add-in, like any other program, is built badly if adding new features is a pain in the neck.

On Sunday, June 10, 2007 10:44 PM Naya wrote:

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


On Monday, June 11, 2007 12:42 AM Hali wrote:

Nayan,

Place the .xla in the
C:\document and
settings\username\application\microsoft\excel\xlst art\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

--

Regards,

Halim


"Nayan" wrote:


On Monday, June 11, 2007 12:49 PM Naya wrote:

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

"Halim" wrote:


On Monday, June 11, 2007 2:16 PM gimme_this_gimme_tha wrote:

So that we are on the same page ...

Addins do not go in the start up directory, or the local directory,
they go in the add-ins directory.


On Monday, June 11, 2007 2:41 PM Randy Harmelink wrote:

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.

On Jun 10, 7:44 pm, Nayan wrote:


On Monday, June 11, 2007 4:55 PM Randy Harmelink wrote:

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.

On Jun 11, 11:16 am, "
wrote:


On Monday, June 11, 2007 7:51 PM G wrote:

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


On Monday, June 11, 2007 8:39 PM Naya wrote:

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
"Randy Harmelink" wrote:


On Monday, June 11, 2007 8:41 PM Naya wrote:

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" wrote:


On Monday, June 11, 2007 9:57 PM G wrote:

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" wrote:


On Tuesday, June 12, 2007 10:38 PM Naya wrote:

Thanks Garry,

I will take a shot at this.

-Nayan



"GS" wrote:


On Tuesday, June 12, 2007 11:46 PM gimme_this_gimme_tha wrote:

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.


On Wednesday, June 13, 2007 1:44 PM gimme_this_gimme_tha wrote:

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.


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.


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.



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


On Wednesday, June 13, 2007 5:06 PM Randy Harmelink wrote:

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.

On Jun 11, 5:41 pm, Nayan wrote:


On Wednesday, June 13, 2007 5:31 PM gimme_this_gimme_tha wrote:

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 .


On Wednesday, June 13, 2007 8:06 PM G wrote:

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 **


" wrote:

**
**
My thoughts on this a
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"?

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

**
**
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.
**
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.


On Friday, June 15, 2007 1:05 PM Naya wrote:

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


"GS" wrote:


On Friday, June 15, 2007 9:18 PM gimme_this_gimme_tha wrote:

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


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.



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


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.




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


Submitted via EggHeadCafe - Software Developer Portal of Choice
Lucene.Net Indexing Searching Entry Level Tutorial
http://www.eggheadcafe.com/tutorials...-tutorial.aspx