The first project at MAS was to fix a situation typically
experienced by many MS Office users. This is where Excel has been used
extensively and new user requirements exceed what could be done in a
spreadsheet. The typical upgrade path is to port the data into a
database. MAS manufactures custom microwave communication units. Each unit
consists of a series of circuit boards. Details of a units component
boards were held in an Excel spreadsheet. One spreadsheet per unit. The problem
they had was when a component board was returned to the factory they didn't
know which one of 2000 spreadsheets held technical details of the unit from
which the board came. The searching task is simply done when all the
data is in a database, as well as providing other functionality. Porting the
data was a technical challenge as the data's structure in the spreadsheets was
vastly different to a normalized database structure. On top of that the
2000 spreadsheets were in four different formats. The answer was to use DDE
communications to make Excel speak to Access. The automated program
ported the data without error in 2 hours. This was a task that would have
otherwise taken a team of data entry operators months to complete.
The Management were so impressed they invited me to address another data management
problem they were experiencing. They had an international sales team of 30 travelling the globe
and selling the communications units. To speed up delivery times the factory needed to
know the quantity and type of orders so they could in turn order the sub components
to build the units. On top of that the salespeople had to lug around an eastlight folder
listing all the parts manufactured or supplied by MAS. The rapidly changing nature of
electronics meant this parts catalog needed constant updating. These tasks were made more
difficult by the sales persons location, which was often on the other side of the world.
The sales people were also advising that time spent preparing quotations in Excel spreadsheets
was reducing their effectiveness. The answer was an electronic parts book that could
be updated via E-mail and also paste an items details onto a quotation. Once the quotation reached a degree of commitment from
the customer, details could be forwarded to the Head Office as an email attachment. I
built this application in Visual Basic and a MAPI control to interface to the MS Mail
application. Crystal Reports was used to produce a printed version of the quote.
For an animated illustration of a small sample of the application please see below...