Updating Macro's in Multiple Workbooks

[Guest]

I have about 50+ workbooks each with 4000+ lines of code in them and I am
still developing the code so updating the code in all 50+ wookbooks when I
want to apply a change in a major pain in the you know what!

I have a few idea's but wanted to know what the guru's here might have to say.

Currently - I update the code in one master woorkbook, then I have written
code that will import all the worksheet data from the "other" open workbook
into the master. I then "File Save As" the master to a new name to replace
the "old" workbook. The problem with this is I have to do it 50+ times.

Idea 1 - I move all the macro's to another workbook and put it in my excel
startup folder. Each time excel is run I should have access to all my macros
right? Then if I update the macro's I only need to do it once right? The only
catch I can think of is if I want to change the worksheet(s) format as well.
Is it possible to specify the path to the "macro workbook" in the workbook
that will use them?

Idea 2. - Is there some way of automating or scripting my current situation.

Idea 3. - Is there some way of the workbooks checking a "master" workbook
and updating themselves at startup?

Idea 4. - A variation of 3, some code that I could include in the workbooks
that if the user pressed a button it would go and check for an update and
either do 2 or 3 above.

Idea 5. - Something I have missed?

 

[Norman Jones]

Hi Trfor,

Put the code into a separate workbook and save the workbook as an addin.
Intall the addin (Excel Menus | Tools ! Addins | Check the Addin).

 

[Guest]

Norman,

Thankyou for the reply. I have not used this feature myself so I have a
couple of questions:

1. The menu option has a browse button and by default it asks for xla or
xll, I presume I just select . xls and use my current master workbook?

2. I presume this will mean my master workbook will get loaded everytime I
start excel? OR can this be loaded/unload when I open the workbook that will
use the macro's?

3. These macro's are going to be used by multiple people, this I presume
means everyone else will also have to add the "Addin"? Can this be automated
for them in some way or at least checked that it is there when they open the
workbook?

 

[Guest]

Norman,

Sorry one more question, what is the difference between an Add-in and using
Add Reference in the editor?

 

[Norman Jones]

Hi Tefor,

Thankyou for the reply. I have not used this feature myself

A standard addin is a normal workbook that is saved as an addin and is
treated differently by Excel.

As a starting point, see:

How to create an add-in file in Excel 2000
http://support.microsoft.com/default.aspx?kbid=211563.

See also Jan Karel Pieters's excelent tutorial at:

http://www.jkp-ads.com/Articles/DistributeMacro00.htm

If this addin is to be used by multiple users, then put it on a network
drive or a shared location that would always have the same pathname, e.g.,
put it
into:
C:\Documents and Settings\All Users\Documents

The addin may be distributed with instructions on how to install.
Alternatively, a commercially availailable installation package, such as
InstallShield, may be used to automate the task. Alternatively again, see
Jan Karel Pietrse's notes on creating a setup utlity at:

http://www.jkp-ads.com/Articles/DistributeMacro10.htm

If you want to see such a setup utility in action, download either of Jan
Karel's superb Name Manager or Autosafe addins, which are freely avilable
at:

http://www.jkp-ads.com/Download.htm

 

[Guest]

Norman,

A very comprehensive reply, many thanks. While waiting for a reply I managed
to get the basics up and running, this is the icing on the cake, many thanks.