How to split an excel workbook in code and data

[zigzagdna]

Subject: How to split an excel workbook in code and data

I have a complex excel file (.xlsm). It has several complex sheets and complex macros. This workbook (excel file) will be used by many different userswho will enter data in the excel file and run macros.

What I want is to split this excel file into two – “data” and “code”; so if any changes are required, they are made in one place “code”and this “code” excel file is included in “data”, so different users can enter different data, but “code” is same. Appreciate your expert opinion how to do it. I thought of following:
1. In one excel file “code” have all the macros. In the second excel file “data” have all the worksheets. I think this is doable.
2. In second approach, have all the Marcos as well as work sheets in one “code” excel file and data file initially is blank. I do not know if I include this “code” excel file in “data” file whether all the worksheets will show up. Advantage of 2 over 1, if there are any changes made in worksheet such as title, color changes; they will be available to all theusers (similar to macro). Not sure 2 can be done.

 

[Ben McClave]

Hi,

There is a great resource at http://www.cpearson.com/excel/vbe.aspx. Adapting a few of the macros from that site might do the trick.

For example, you could write a "copy" macro to delete modules and/or copy them between workbooks. That would allow you to maintain a "code" module separate from the "data" workbook. The "data" workbook would have a copy of the "code" module. As changes are made to the master "code" module, you could then run your "copy" macro to remove the module from the "data" workbookand export a copy of the current "code" module to it.

Good luck,
Ben

 

[bart.smissaert]

Subject: How to split an excel workbook in code and data

I have a complex excel file (.xlsm). It has several complex sheets and complex macros. This workbook (excel file) will be used by many different users who will enter data in the excel file and run macros.

What I want is to split this excel file into two – “data” and  “code”; so if any changes are required, they are made in one place “code” and this “code”  excel file is included in “data”, so different users can enter different data, but “code” is same. Appreciate your expert opinion how to do it. I thought of following:
1.      In one excel file “code” have all the macros. In the second excel file “data” have all the worksheets. I think this is doable.
2.      In second approach, have all the Marcos as well as work sheets in one “code” excel file and data file initially is blank. I do notknow if I include this “code” excel file in “data” file whether all the worksheets will show up. Advantage of 2 over 1, if there are any changes  made in worksheet such as title, color changes; they will be available to all the users (similar to macro). Not sure 2 can be done.

The standard way to do this is with an add-in, so a .xla file. Have a
look at that.

RBS