Using Excel File to Load Test Parameters to an ATEasy Program

Knowledge Base Article # Q200233

Read Prior Article Read Next Article
Summary The article show how to read Microsoft Excel file that has test parameters such as min max and to modify the program tests requirements dynamically.
The article show how to read Microsoft Excel file that has test parameters such as min, max values and to modify the program tests requirements dynamically.

The example reads a Microsoft  Excel file and store the new parameters in the program test objects. Each row in the Excel file contains the test id (test to read the parameters to) , test min, test max and additional extra parameter that is loaded to the ATest.Tag property (access from the test using test.tag).

Excel Spreasheet with ATEasy Test Parameters

Using the Example

The code to read the Excel file and apply the parameters reside in the ExcelTestParameters.drv driver and it uses the ATEasy EXCEL driver. To use the driver:
1. Insert the ExcelTestParameters.drv to your system
2. Insert the Excel.drv to your system
3. To load the parameters to your program use the following code:

if not EXCELTESTS ReadTestParametersFile("C:\\ExcelTestParameters.xlsx", "Sheet1")

this code typically can reside in Program.OnInit event.

Example Code

The  ExcelTestParameters.drv export one command / procedure that is used to load the parameters:

Procedure ReadExcelTestParametersFile(sFile, sSheet): Bool
    sFile: Val String
    sSheet: [Val] String = "Sheet1"
    xlwb: Excel.Workbook
    xls: Excel.Worksheet
    vrA: Variant
    vrB: Variant
    vrC: Variant
    vrD: Variant
    iRow: Long
    sRow: String
    EXCEL Initialize()
    ! open excel file
        EXCEL File Open Existing(, sFile, xlwb)
    catch else
    if not xlwb
        MsgBox("Unable to open Excel Workbook File: '"+sFile+"'")
        EXCEL Quit()
        return False

    ! verify the worksheet exist and get the object
    catch else
        MsgBox("Bad Excel Worksheet: '"+sSheet+"' found in File: '"+sFile+"'")
        EXCEL File Close(xlwb, False)
        EXCEL Quit()
        return False

    ! read row B to empty row
    ! each row has columns: 1=testid, 2=min, 3=max, 4=parameters
    for iRow=2 to 9999
        EXCEL Get Value(xlwb, sSheet, "A"+sRow, vrA)
        if vrA=""
            exitloop ! last test

        EXCEL Get Value(xlwb, sSheet, "B"+sRow, vrB)
        EXCEL Get Value(xlwb, sSheet, "C"+sRow, vrC)
        EXCEL Get Value(xlwb, sSheet, "D"+sRow, vrD)
            Program.Tests(vrA).Tag=vrD ! extra parameters
        catch else
            MsgBox("Bad Test ID: '"+vrA+"' found in Row: '"+sRow+"', in File: '"+sFile+"', Sheet: '"+sSheet+"'")
            EXCEL File Close(xlwb, False)
            EXCEL Quit()
            return False

    EXCEL File Close(xlwb, False)
    EXCEL Quit()
    return True

Running the Example

Running the example will open the ATEasy Test Executive with the new test parameters as specified in the spreadsheet:

Excel Spreadsheet with ATEasy Test Parameters Test Log

How to Improve the Example

1. Use the program name as the sheet name, every sheet in the workbook can be used for one program.
2. Add support for other test type. Once the test was located the routine can check the test type and load parameters accordingly
3. You can use the testid also for sequencing (similar to the profile driver)

Example Files

DownloadDownload ATEasy ExeclTestParametrs project files
Article Date 11/21/2012 2:18:41 PM     Updated: 4/16/2014 4:05:13 PM
Keywords ATEasy, EXCEL, Tests

1 ratings | 5 out of 5
View Comment
Rating: 5 out of 5 | Andrew Y. from Bristol, PA | Wednesday, October 7, 2015
very nice
Read Prior Article Read Next Article