Automatic MS Access forms generation from Excel documents
Project Description

During my MSc degree studies, I decided to get my first job in IT field. I started with an internship in a small 3-month project for Inter-tel company.

INTER-TEL used MS Excel files as main documents to store forms with information about their services. Depending on the type of service the forms looked slightly different, but also they could have different fields in the same type of service by removing unused fields and/or adding a new type of information.

An idea was to create an application which reads MS Excel files with forms, moves all data to the database and recreates MS Excel forms in MS Access by automatic generation of forms using VBA.

The solution to the problem was made in 3 major steps:

  • Step #1 - reading files
    This part was made by my colleague. He created an application in C# that opens MS Excel in a view which allows us to read a file in the easiest way and then moves data to the database. All forms were put in one directory, so he created algorithms that read all new files every couple of minutes and then move them to a different location.
  • Step #2 - database
    I designed the whole database for this task. My colleague suggested that we should use Firebird DB as we did not know MySQL at that time.
    The requirement was to build a database which could be expanded and developed easily in the future. The information from the document had to be divided and put in several of different tables: one for a major document data, one for distinguished parts of a document, the last one - for files names and their values in detected part stored as a text. Unfortunately what we lost in Step #1 and #2 was information about the layout of forms.
  • Step #3 - automatic forms generation
    Another part of my job was to create VBA and MS Access interface to automatic forms generation.
    That part was a little tricky. The main assumption was that an application doesn't know what original form looks like. The question was: "how to create a form that will be similar to the original one without knowing its layout?". Here, good database designing skills came in handy. Knowing that data stored in DB was separated according to original layout division, it was possible to rebuild forms so they look similar to original ones.
    The script was using information about the common look of original forms to put major fields in place. Other fields were put in the end of a section in their order with 100% width of a form.

Project Details