MS Excel / Mapics XA6 Shop Floor Spreadsheet Automation Project Backgrounder

The project was to update and automate Shop Floor Scheduling MS Excel Spreadsheets used by the plant scheduler. The current system involved a few downloads of DB2 data.  A bunch of cut and pasting.  Some traffic lighting of cells. Some deleting of rows.  A little more cut and pasting.  You get the picture. The Scheduler spent hours each week preparing a schedule that the shop floor could use. The company had developed these spreadsheet as part of its Lean Manufacturing initiative but did not have the programming skills to automate the process.

I created a VBA(Visual Basic for Applications) procedure that ran a CL(Control Language) program on the AS400. After the scheduling program ran it downloaded the data into MS Excel using ADO.NET. The information was then formatted for the user.  This process took only a few minutes whereas before it took hours to create all the necessary information.

Technologies used on the project:

• MS Excel 2003/2007
• VBA(Visual Basic for Applications)
• DB2 Data Access using ADO.NET
• Mapics XA 6 - Modules: PDM,PCC,IM,MRP