A Case of VBA Application in Transaction Processing

Plenty of transactional work is continual, odd and time-consuming. Using the VBA technique can simplify the work procedure and liberate the workforce. A case of fetching required information from Word files and filling into the sheet of an Excel file is discussed. A general method of using embedded VBA codes and a mapping table is proved to be efficient, easy to implement and meet the demand. Introduction There is some transactional work which is no technical difficulty but time-consuming and odd in our daily work [1]. Using proper tools and methods, these affairs can be completed in a short while and the error can be decreasing to zero with a very little additional work [2,3]. A lot of successful cases using VBA to solve the odd and time-consuming routine affairs is reported [4,5,6]. Using embedded VBA program in Word or Excel documents [7,8], plenty of odd affairs work come to be easy which liberate the operators and improve the efficiency [9]. An association is newly built. The membership should be audit. A hundred of application lists are applied. Each applicant submits a form in a Word file. The information of the applicants should be summarized together as a new sheet. Using embedded VBA in an Excel file and programming a little additional code to open each Word file and fetch the information from the table and summarize as a new Excel file [10]. Affair Description In order to be a membership of the association, the applicants are demanded to fill a form which includes personal information and other technique specialty. Each applicant should submit the information in a Word file. Hundreds of applicant’s files have been received and should be summarized together. Transaction Processing with VBA Each item is saved in the table cell of a Word file. Locate the position of the required cell in the table. Establish a mapping table between the demanded information and the location in the Word-file’s table. Dynamically modify the mapping table to get different cells’ contents. Personal Information. Each applicant fill the form which will be saved as a Word file. A part of applicant’s information is shown in Table 1. Mapping Table. In order to summarize the applicants’ information, the Word files of the applicants should be traversal accessed. Fetch the required information from the file and fill into the sheet of the Excel file. Establish a mapping table between the required information and the cell’s location in the Word file. Modify the mapping table to get different information. An example of the mapping table is shown in Table 2. VBA Implement. In an Excel file using the embedded VBA codes to implement the traversal access of the Word files, fetch the required information form the table of the Word files and fill the sheet. 2nd International Conference on Materials Science, Machinery and Energy Engineering (MSMEE 2017) Copyright © 2017, the Authors. Published by Atlantis Press. This is an open access article under the CC BY-NC license (http://creativecommons.org/licenses/by-nc/4.0/). Advances in Engineering Research, volume 123