Updates and Modification to Existing Project

**Pherion** · 07-22-2009, 05:32 PM

I am about finished automating a workbook that will be used by several departments in my company. Each department will have different starting information entered into the spreadsheet, and will have their own "master" sheet saved.

I am looking for a way to update each of these "master" sheets when I make a change to the macros, or add additional functionality. I would like to avoid having to re-enter all of the starting information (replace their master with my updated one) when this happens.

Thanks in advance for any assistance.

**shg** · 07-22-2009, 06:42 PM

You could put the code in an add-in on a network share.

**Pherion** · 07-23-2009, 05:51 PM

I am having dificulty locating a description of how a Network Share and Add-In work. Would you be able to expand the idea some?

I am really looking for something that can "merge" my updates into the alreay existing workbooks.

**Pherion** · 07-24-2009, 02:23 PM

Ok, I have a solution:

I can export the UserForms and the Modules to a seperate directory, then just import them into the individual spreadsheets, thereby updating the macros without losing the infomation contained in the spreadsheet itself.

Only problem. The import process will not overwrite existing code. So I need to delete all the existing code before I can import the new code. Not exactly what I was looking for, but it will work!

**shg** · 07-24-2009, 02:26 PM

That's a really error-prone way to do it.

My thought was you could create an add-in (see http://www.fontstuff.com/VBA/vbatut03.htm) and save it read-only on a network drive accessible to all. I don't think people having the add-in installed in an active Excel session would lock the file, but you'd have to check. Then just update the add-in as necessary.

**Pherion** · 07-24-2009, 05:46 PM

That sounds good, but let me make sure I understand what is going on.

The Add-In will be a file that only contains my macro data, and will load when the user launches Excel (even if they do not launch the particular sheet that the macros were written for).

In order to setup the user to use the Add-In I would have to set their individual Excel settings to load the specific Add-In (on a network drive).

To update all users, I would only need to update the single Add-In that is accessed by them all.

Is that correct?

**shg** · 07-25-2009, 01:11 AM

The add-in contains the macros, not the data.

The users would install the add-in, just like they do, for example, the Analysis ToolPak.

**Pherion** · 07-27-2009, 05:50 PM

This sounds good. I'll have to fool around with it when I get a chance. Thanks!

**Pherion** · 07-28-2009, 01:11 PM

I saved a copy of my spreadsheet under a differnt name and removed all of the macros from it. I then went back to my origonal spreadsheet, and saved it as an Add-In. I am running into a few problems attempting to make the Add-In work under the test spreadsheet (which has no macros of its own).

1. When I load the Add-In through Excel, it forces me to copy it to my local disk, meaning that I cant automaticlay update the add-in on everyone's system. They would actualy have to re-load the Add-In to updated it.

2. Even after the Add-In is loaded, the macros do not show up.

Any sugesstions?