Data Load Macros

AutoSoft Systems

2 Round Hill Court

East Greenwich, RI 02818

401.885.3631

401.884.5653 Fax

401.996.3631 Cell

AMDG

Decision Ready Information!

autosoft@aol.com

Commercial & custom multi-user computer software for a variety of applications including performance metrics, statistical analysis, data extraction and merger from multiple large databases, computer simulation and management information systems. Founded in 1982.

We know Excel! Whenever possible, Excel is the user interface, VBA is the programming language, ODBC connects to the database of your choice.

Pharmaceutical & Biotech

Construction Management

AMDG

There are times when mass changes have to be made to the AXIOM/Progress data. AXIOM provides very limited mass change tools to perform these changes. One example of a need for a mass change would be during the implementation phase. A typical implementation in the wire and cable industry could have as many as 100,000 parts that have to be loaded into AXIOM. All WIPS and finished goods will also have detailed routines that have to be loaded. It is foolhardy to attempt to load this data directly into the AXIOM/Progress tables, bypassing the human interface and all associated business rules. There is only one option, type this massive amount of data into AXIOM by hand, consuming a tremendous amount of person-hours and running the risk of multiple data entry errors.

SMART's Data Load Macro Methodology was developed over the last 5 years out of necessity because of AXIOM's limitation. Dr. Serdakowski's first involvement in AXIOM was to assist C&M in loading 70,000 parts and associated routines into AXIOM during C&M's startup. He inherited some cryptic Excel/VBA code that used the AppActivate and SendKeys commands to simulate typing information into AXIOM. This initial code was very limited and fragile, crashing at the slightest provocation and/or entering data into the wrong AXIOM fields, doing more harm than good.

The current Data Load Macro Methodology overcomes all foreseeable interface problems and results in reliable data entry. The example presented here adds a new part to a series of 1,760 routings in order to solve a problem with fluctuating Copper prices. The part number is "ADJT-COPPER" and the amount to add to each routing equals the amount of copper (in pounds) in the finished product. We view the Excel Workbook that contains the Data Load Macro:

As with all SMART products, cells with a yellow background and dark red font are for user entry. [B3] (= worksheet cell B3) contains the first row of data to be entered into AXIOM today. [B5] is the last row. [B27] is the part number to be entered. [B6] is the pause time between keystroke operations. We have found that this is very important to periodically pause during these data load macro operations. Blindly sending keystrokes into AXIOM (or any application) assumes that the receiving application can receive the information at the same speed that one is transmitting it. Because of network traffic issues and AXIOM processing time, one has to pause between keystrokes to assure that AXIOM has "fully digested" the sent information and is ready to receive more. This pause time is variable and needs to be established for the existing network and AXIOM traffic and the complexity of the macro.

The macro provides user feedback with start time, current time, predicted end time and predicted time remaining (blue background cells). Some of these data load macros run for days without problems.

The "Open Loop" fires once to initiate the macro. The "Main Loop" fires once for each row of data. The "Close Loop" fires once to terminate the macro.

Column B represents the keystroke sequence that is sent to AXIOM using the SendKeys command. Column C if not blank specifies how many times the keystroke in Column B is to be repeated. Column D is not blank specifies the name of the AXIOM screen that should be present at the time that the keystroke in Column B is being sent. If the proper AXIOM screen is NOT present, the macro then knows that AXIOM has lost its place and the macro stops loading data, and can send an alert in the form of a text message, audible alarm or email if so desired. Column E if not blank is the value that should be present in the active cell of the current AXIOM screen. The macro is able to compare the specified value to the value found in AXIOM. If the values do not match, then the macro halts and sends an alert.

The data to be loaded is stored on the "Main" spreadsheet:

The data that is transferred to the Macro sheet is located in columns A:E. The data is transferred to the tan colored cells on the Macro sheet. Column F tells the user which records have already been processed.

The Visual Basic code for this macro contains less than 60 lines and is easy to understand, modify and maintain. As with all SMART products, this code is included. One could easily envision how multiple clustered loops and conditional branching could be incorporated in this methodology if required by the data load task at hand.

AutoSoft Systems | 2 Round Hill Court, East Greenwich, Rhode Island, USA 02818 | 401.885.3631 | Fax: 401.884.5653 | Mobile: 401.996.3631

This web page was last updated at 02/21/2014 07:41 AM and is written in EXCEL!

AXIOM is a Trademark of Consona - USYS is a Trademark of Zumbach - AutoSoft Systems is not affiliated with Consona nor Zumbach