The project goal is to consolidate different files of the same microsoft word document (attached form) into a single consolidated spreadsheet.
The scenario will be two tools and two kind of target users:
- Microsoft word form (attached) to be filled by approx 100 users twice a year;
- Microsoft xls with a macro that would fetch all the filled forms at a specific moment and consolidate data into a single new tab/spreadsheet; this xls will be used
by no more than 4 users;
For mainteinability, the consolidation logic must be located at the spreadsheet and not a various msword forms (spread for more than 100 people). It must comprises of:
1. Provide a button to start the consolidation;
2. when clicked, asked user to inform a directory to scan ms word forms documents;
3. when submited, create a new work-sheet at the same spreadsheet having the current date (DDMMYYY_HHMM) into its name;
3. the algorithm must scan this directory and all subdirectories and, for each ms word form found it must bring data to the target worksheet following the rules:
a. each form will fill in a new line at the worksheet;
b. each field (only form fields) will be a column at the spreadsheet;
c. column name must be consistent with form information (e.g. 'shorten project name' or 'requesting area manager - personnel id', 'IRR - yes', 'IRR - no' , ' IRR
- Score' - if better for control you can provide appropriate field name into the ms word form (I just worried about the information not the technical details - it´s
up to you);
d. form fields:
- text type: text must be filled at corresponding target worksheet cell;
- selection list: value (not label) must be filled at corresponding target worksheet cell;
- checkbox: must be replaced by a select with yes/no and 1/0 must be filled at corresponding cell;
I just found out that users of ms word form don~t have a uniform version of office. So the form input fields must be replaced to support office 2010 up. The form reengineering is also in scope.
large Text input must have capability to support line break as text area.