We need a MS Excel VBA application that consolidates several excel files into one single file and generate some statistic graphics based on the data contained in the imported files.
MS Excel 2010
Only one file no external executable, ActiveX or dll in order to easily distribute and run on any workstation that has MS Excell installed without any further installation.
Each Sheet shall show a company logo
Sample input excel file will be provided to programmer selected.
Structure of the Application:
1. Sheet 1: Opening file in Excel shows a welcome sheet with login facility and link to admin section.
2. Sheet 2: Admin sheet only visible and accessible if admin is logged in
3. Sheet 3: File Import sheet
4. Sheet4-…: Data of imported files. (As many as required one for each sheet in every file)
5. Sheet (Following the data sheets): Statistical graphics, one per data sheet.
6. Sheet (Last): All graphics onto one page
Admin section (Sheet2)
Password protected offering following functionalities:
- Master password to enter Admin section is placed somewhere in the code and can’t be changed by the user.
- User management to define user access and user rights ‘Admin’ and (‘Read only’ or ‘read write’).
- Data source location (Drive / Path) input field with path browse button.
- Input fields for comment or description that will be shown on logging sheet. (Sheet 1)
Login facility (Sheet1)
Only user defined by the admin can view data sheets, import data or print reports.
Show description edited in admin area.
Data import section (Scheet3)
A combo box shows all available subdirectory located under ‘Data source location’ (See Admin section)
Once a file location is selected the contained file shall be listed on the sheet and an import button shall be activated. There will be up to 10 files in one directory. The file of only directory will be imported at once.
Before new data are imported previously imported data sheets shall be deleted as well as all reports sheets.
A button (import button) to initiate importing excels files contained in the selected subdirectory. The sheets in each imported file shall be stored into one local sheet; the sheets shall be named using filename and sheet name of source file. All data shall be copied not referenced.
Normally report shall be generated automatically after import is done, however a button ‘Generate Reports’ shall be available in order to generate report again.
Before generating new reports all existing reports sheets shall be removed.
Report generation Subtask of data import
Report generation shall be done automatically after importing source files.
Each data sheet shall have a report sheet.
The report sheet shall be named same as the corresponding data sheet and ‘_report’ as additional suffix.
For logged in user with writing permission:
Each report Sheet shall have an additional editable comment input area.
One Overview report shall be available too showing and all other reports including they’re comments onto one page
Each report sheet shall have a print button to print the Report graphic and the comment