You can interact with an Excel file either by reading data from it, or writing data to it. This article discusses how to use the Excel actions.

ScenarioBuilder Microsoft Excel actions let you perform automation steps using Microsoft Excel files. They will allow scripts to read from and write to Excel files.

The MS Excel actions can be found in the “Actions” list located on the left side of ScenarioBuilder.

Understanding MS Excel actions in ScenarioBuilder (360003854034)_Screenshot_54

Click on the arrow or double-click on MS Excel to expand the section.

A few items to note before we go into the actions and how to use them:

  • The new actions will work on machines that don't have Excel or Microsoft Office installed on them.
  • An Excel file is required on your machine in order to use the actions. The file should have one sheet with headers (sub-headers are optional) that define variable names.
  • The Excel file should have a .xlsx file extension and format
  • You can specify the Excel sheet to be used. If one is not specified, ScenarioBuilder will use the active sheet.

Here is an example of an Excel file:

Excel File-1

MS Excel Actions

Define a Worksheet

Understanding MS Excel actions in ScenarioBuilder (360003854034)_mceclip1 - 1

This action should be added for each Excel document/worksheet that will be used in the script.

Screenshot_6-10

Dataset Name: Provide a short name that will be used to identify the variables in an Excel worksheet.

File Name: Click on the folder icon to select your Excel file.

Note: The file should have a header row and at least one line of data.

Screenshot_24-2

Continue On Failure: If selected, the execution of the scenario will continue even if there is a failure.

Sheet Name: Specify the Excel sheet to be used. If one is not specified, ScenarioBuilder will use the active sheet. The active sheet is the worksheet that was active when the file was saved.

Headers Start Row: Specify the line where your header row starts.

Headers End Row: If you have sub-headers, specify the line where your last sub-header row is located.

Headers Delimiter: Provide how you want to access your variables from the Dataset by choosing a delimiter value. The default is a colon. When referencing a variable from the Excel file in your scenario, the format is the <Dataset name><delimiter><Variable name> (i.e. %Users:username%)

Redefine Dataset: If the dataset was already defined before, redefine it again.

Scenario Section: This is for Global Scenarios only. Specify the section name that can be called from other scenarios.

ForEach Line

Understanding MS Excel actions in ScenarioBuilder (360003854034)_Screenshot_55

This action repeats the execution of child actions for each line of data in the Excel sheet. Use it where you might have used a Loop action to repeat child steps. With a Loop action you have to specify the number of times it should repeat. The ForEach Line action will repeat until it reaches the last line of data.

Understanding MS Excel actions in ScenarioBuilder (360003854034)_Screenshot_57

Dataset Name: Name of the already defined Excel Dataset that you want to read from or write to. This action assumes that you already used a Define A Worksheet action before to setup a dataset. Click on the arrow to select a dataset.

On Failure: What to do if any of the child actions fail. Set to empty to continue to the next row of data.

Scenario Section: This is for Global Scenarios only. Specify the section name that can be called from other scenarios.

GoTo Line

Understanding MS Excel actions in ScenarioBuilder (360003854034)_mceclip1 - 1 - 2

This action specifies the line in your dataset to read from or write to.

Understanding MS Excel actions in ScenarioBuilder (360003854034)_mceclip2

Dataset Name: Name of the already defined Excel Dataset that you want to read from or write to. This action assumes that you already used a Define A Worksheet action before to setup a dataset. Click on the arrow to select a dataset.

Traverse To Line: Specify where you want to traverse to.

  • First: the first line after the header row(s)
  • Last: the last line in the Excel sheet
  • Next: one row down from the last line read in the Excel sheet
  • Previous: one row up from the previously read line in the Excel sheet

Continue On Failure: If selected, the execution of the scenario will continue even if there is a failure.

Scenario Section: This is for Global Scenarios only. Specify the section name that can be called from other scenarios.

Read Line

Understanding MS Excel actions in ScenarioBuilder (360003854034)_mceclip4

Read the variable data from the row in the Excel worksheet into ScenarioBuilder.

Understanding MS Excel actions in ScenarioBuilder (360003854034)_mceclip5

Dataset Name: Name of the already defined Excel Dataset that you want to read from or write to. This action assumes that you already used a Define A Worksheet action before to setup a dataset. Click on the arrow to select a dataset.

Continue On Failure: If checked, the execution of the scenario will continue even if there is a failure.

Scenario Section: This is for Global Scenarios only. Specify the section name that can be called from other scenarios.

Write Line

Understanding MS Excel actions in ScenarioBuilder (360003854034)_mceclip6

Writes the value of the dataset to the current line.

Understanding MS Excel actions in ScenarioBuilder (360003854034)_mceclip7

Dataset Name: Name of the already defined Excel dataset that you want to read from or write to. This action assumes that you already used a Define A Worksheet action before to setup a dataset. Click on the arrow to select a dataset.

Continue On Failure: If selected, the execution of the scenario will continue even if there is a failure.

Scenario Section: This is for Global Scenarios only. Specify the section name that can be called from other scenarios.

How to Read From an Excel File

  1. Use the Define A Worksheet action to call the Excel file that will be used in the script.
  2. Use the Read Line action to read the values from the file's dataset.
  3. Add actions to your script to access the values by specifying the dataset name, delimiter, and headers with the following convention: Dataset_Name:Header_Name:Sub-Header_Name. Example: If your dataset name is "patient_data", delimiter is ":", header is "Patient Name", you would use %patient_data:Patient Name%.

Understanding MS Excel actions in ScenarioBuilder (360003854034)_mceclip13

How to Write To an Excel File

  1. Use the Define A Worksheet action to call the Excel file that will be used in the script.
  2. Add "Set Variable" actions to your script to define the values by specifying the dataset name, delimiter, and headers with the following convention: Dataset_Name:Header_Name:Sub-Header_Name.
    Example: If your dataset name is "patient_data", delimiter is ":", header is "Date Entered", you would use %patient_data:Date Entered%.
  3. Use the GoTo Line action to specify the line to write to.
  4. Use the Write Line action to write the values to the file's dataset.

Understanding MS Excel actions in ScenarioBuilder (360003854034)_mceclip14

Important

To use the scenario in AppsWatch or AppLoader, you need to put your Excel file on the machines that will be playing the script in the location that you specified in the Define a Worksheet action or you need to put it in the Project's "variables" folder in ScenarioBuilder before sending it to AppsWatch or AppLoader.

Example Reading from and Writing to an Excel file

I have an Excel file called "New Patients.xlsx". It contains the following headings:

Patient Name
Patient ID
Date of Birth
Date Entered
Time Entered

New Patient Example

Let's say I need to take the patient's information that is contained in the Excel file (name, id, date of birth) and enter it into an application (we use Notepad in this example). I then want to write the date and time the patient's information was entered into Excel.

  1. First I use the Define a Worksheet action to call the Excel file that's saved on my computer. In the properties, I provide a Dataset Name and select the Excel file from the location where it is saved. I also specify the Headers Start Row, Headers End Row and Headers Delimiter.
  2. Since we are using Notepad as our application to receive the data, I add a step to open Notepad.
  3. I then add a ForEach Line action. This allows me to put all of the tasks to be completed for each row in the Excel file as child actions.
  4. The child steps are the data entry steps, setting the current date and time variables and then writing them to Excel.
  5. As the last step I close Notepad.

The script looks like this:

New Patient Script-1

The End Result

After successfully playing the script, your Excel file should look like this:

New Patient Example After Script Execution