Automation in code generation: Lesson 2 – Reading information from an Excel file.

Auxiliary Excel spreadsheets are commonly used in code generation because of their functionalities, such as sequential copies of cells and sorting out of information, among others. The spreadsheets can be set up according to the user’s needs.

Fig. 2.1: Auxiliary spreadsheet

After the spreadsheet has been generated, the information can be read in several different ways. In this article, we recommend you use one of the following two suggestions:

1. Save the spreadsheet in .csv format. Then, use FileSystemObject’s methods to read the file via scripts. To do so, follow the procedures from KB-12658: Reading/Writing TXT files with E3. The procedures from that article also apply to .csv files.

or

2. Use Microsoft Access to import the spreadsheet from Excel and generate a new table in the database. The following wizard can be used to help in this procedure:

After this, set up a Database and create a query in the application, pointing to the imported table.

Now, you will only need to use ADORecordSet’s properties and methods to manipulate the records in the table. To do so, we recommend reading the article Basic VBScript for Elipse E3: Lesson 7 – GetADORecordSet.