We can dynamically set the underlying Excel file that will serve as a data source for the whole run session, though this is probably a very rare case, and even switch between such files during the run session. It is possible to import a whole Excel workbook for a test or just a single worksheet for a specific action.
The classical case of importing an Excel file to a test is when the same flow needs to be executed on different environments, such as with multilingual systems. In such a case, the test would require an external parameter to identify the environment, and then load the correct Excel file. Another possibility is that the test identifies the language dynamically, for example, by retrieving the runtime property value of a Test Object (TO), which indicates the current language, or by retrieving the lang attribute of a web page or element.
Ensure that a new test is open and create a new action. Ensure that an external Excel sheet exists with one global worksheet and worksheets named after each action in the test. The Excel sheet will contain three worksheets, namely, Global, Action1, and Action2. The Action2 worksheet will contain data shown in the following screenshot. In our example, we will use the Excel sheet named MyDynamicallyLoadedExcel.xls, and to simplify matters, we will put it under the same test folder (it should be placed in a separate shared folder):
In the Flow pane, make sure that the Action Call properties are set to Run on all rows.
In order to load the MyDynamicallyLoadedExcel.xls file to the test, perform the following steps:
We use the
DataTable.Importmethod to load the Excel sheet. InAction1(the first to be run), we use the following code to ensure that the Excel file is loaded only once (to avoid loading Excel for each iteration in case the test is set to Run on all rows):Print "Test Iteration #" & Environment("TestIteration") & " - " & Environment("ActionName") & " - Action Iteration #" & Environment("ActionIteration") if cint(Environment("TestIteration")) = 1 then DataTable.Import("MyDynamicallyLoadedExcel.xls") end ifIn
Action2, we use the following code to retrieve the values for all parameters defined in the local datasheet forAction2. We first print the number of the current action iteration, so that we may distinguish between the outputs in the console.Print Environment("ActionName") & " - Action Iteration #" & Environment("ActionIteration") For p = 1 to DataTable.LocalSheet.GetParameterCount print DataTable.LocalSheet.GetParameter(p) Next
When a test is set to Run on all rows, it means that it will be executed repeatedly for each row having data in
GlobalSheet.The output to the console looks like the following screenshot:
In Action1, the DataTable.Import method replaces Default.xls with the target Excel file. The code in Action2 retrieves and prints the values for each parameter, and as the action was set to Run on all rows, the code repeats this for all rows with data.