Advice on updating templates

[bolony21]

Hi,

I have developed a range of Excel tools which are combined together into a core sheet periodically.

Multiple users input into the child templates and there can be 50+ of these live at any one time.

The child templates must all be exactly the same so that when the combination takes place it does not throw up errors.

------------------------------------------------------------------------

My question is around the updating/maintenance of these child templates.

Currently if I update a function/find a bug/make improvements, I need to spend hours over the weekend to individually enter into each live child template and update manually.

Obviously, this is not ideal and was looking for some advice on how you would accomplish this task. Am I overlooking a very simple way of making changes en-mass to multiple workbooks?

Thanks in advance.

 

[Rijnsent]

Hi Bolony,
depending on what update you're making you could try to script your update with VBA. So say you'd need to update the templates by inserting a row below row 5 and changing the formula in D16 to =A1, you could run a script that loops through the template files, opens them, makes those changes and saves the files again. In order to do that more efficiently:

• in every template file, add a named range with the version number (not in a sheet, just go to names to define e.g. "version_number" as =2020022401 ->date + 2 digit number), which you can update when you've updated it.
• test your fixes/improvements on one local copy of the current version of the template file
• if that works, run a loop for all the files

The tricky bit is making the code for the update, but after that you're at least sure that you haven't made any manual mistakes. Warning: do test your code before you deploy, macros are also a great way to mass-mess-up your sheets .
Cheers,
Koen