Excel Modules

The folder TBox XEngines->Excel in the Standard subset contains Modules that perform specific tasks for the Excel Engine 3.0.

TBox Open Excel Workbook

The Module TBox Open Excel Workbook allows you to open an Excel file, i.e. an Excel workbook. Every workbook can contain several worksheets.

This step is mandatory for every Excel Engine 3.0 TestCase.

The Excel Engine 3.0 opens the file in the background. You do not actually see the file opening in Excel.

The Module TBox Open Excel Workbook has the following ModuleAttributes:

ModuleAttribute

Description

Workbook Name

Specify a name for the workbook.

This can be any name. The Excel Engine 3.0 uses it to identify the file you want to steer in other Excel Engine 3.0 TestSteps.

Path

Full path to the Excel file that you want to open, including the file name and extension.

Create new

Specify whether you want to create a new Excel file. You can use the following values:

  • False: don't create a new file. This is the default value.

  • True: create a new file and open it. The Excel Engine 3.0 creates the file in memory. You can save the file later with the TBox Close Excel Workbook Module. 

Password

Optional password for password-protected workbooks.

Open as Read-only

Specify whether you want to open the file in read-only mode.

  • Value False: don't open read-only. This is the default value.

  • Value True: open read-only.

In this example, you open the Excel file sales_data.xlsx, located at C:\Temp.

You assign the name MyWorkbook to the workbook that you want to steer.

Since the file is password protected, enter the password and then open the file in read-only mode.

Open an Excel file

In this example, you create the new Excel file marketing.xlsx. The Excel Engine 3.0 saves it to C:\Users\janedoe\Desktop.

You assign the name CustomerData to the workbook that you want to steer.

Create a new Excel file and open it

TBox Define Excel Range

The Module TBox Define Excel Range allows you to define a data range that you want to steer. This step is mandatory for every TestCase in which you want to manipulate a particular data range.

The Module has the following ModuleAttributes:

ModuleAttribute

Description

Workbook Name

Name of the workbook that you defined.

Worksheet Name

Name of the Excel worksheet that contains the data. This name must be an exact match for the worksheet within the workbook.

Range Name

Specify a name for the data range that you want to steer.

This can be any name. The Excel Engine 3.0 uses it to identify the data range you want to steer in other Excel Engine 3.0 TestSteps.

Data Range

Read-only.

Data Range->Start Cell

Start cell of the data range that you want to steer.

Data Range->End Cell

End cell of the data range that you want to steer.

In this example, you define a data range in the workbook MyWorkbook. This data range is located in the Excel sheet January.

You assign the name JanuarySales to the data range.

The range starts with cell A2 and ends with cell T982.

Define the range that you want to steer

TBox Create Excel Worksheet

The Module TBox Create Excel Worksheet allows you to create a new worksheet in an existing Excel file.

It has the following ModuleAttributes:

ModuleAttribute

Description

Workbook Name

Name of the workbook that you defined.

Worksheet Name

Specify a name for the worksheet.

Worksheet Order

Define where the Excel Engine 3.0 should add the worksheet. You can enter the following values:

  • Numbers to indicate the position. For instance, enter 3 to create the worksheet as the third sheet in the Excel file.

  • The value first, 0, or a negative number to create the worksheet as the first sheet in the Excel file.

  • The value last or a number greater than the total number of existing worksheets to create the worksheet as the last sheet in the Excel file.

In this example, you create a new worksheet named JAN_summary in the workbook MyWorkbook.

The Excel Engine 3.0 creates the worksheet as the second sheet in the Excel file.

Create a new worksheet

TBox Delete Excel Worksheet

The Module TBox Delete Excel Worksheet allows you to delete a worksheet in an existing Excel file.

It has the following ModuleAttributes:

ModuleAttribute

Description

Workbook Name

Name of the workbook that you defined.

Worksheet Name

Specify the name of the worksheet that you want to delete.

In this example, you delete a worksheet named JAN_summary in the workbook MyWorkbook.

Delete a worksheet

TBox Update Excel Worksheet

The Module TBox Update Excel Worksheet allows you to update a worksheet in an existing Excel file.

It has the following ModuleAttributes:

ModuleAttribute

Description

Workbook Name

Name of the workbook that you defined.

Current Worksheet Name

Name of the worksheet that you want to update.

New Worksheet Name

The new name you want to give to the worksheet.

New Order

Define where the Excel Engine 3.0 should move the worksheet to. You can enter the following values:

  • Numbers to indicate the position. For instance, enter 3 to create the worksheet as the third sheet in the Excel file.

  • The value first, 0, or a negative number to create the worksheet as the first sheet in the Excel file.

  • The value last, a number greater than the total number of existing worksheets or a blank value to create the worksheet as the last sheet in the Excel file.

In this example, you perform the following actions for a worksheet named JAN_summary in the workbook MyWorkbook:

  • You rename the worksheet to FEB_summary.

  • You move the worksheet, so it's the second sheet in the Excel file.

Update a worksheet

TBox Excel Range Manipulation

The Module TBox Excel Range Manipulation allows you to manipulate data in an Excel file.

It has the following ModuleAttributes:

ModuleAttribute

Description

Range Name

Name of the data range that you defined.

Data Table and its sub-Attributes

Table element.

Steer the data in your Excel file as you would steer a table. For information on how to do so, see chapter "Table".

In this example, you steer the data range ExampleRange.

The Excel Engine 3.0 looks for a row that fulfills the following requirements:

  • The value of the cell in the column First name is Sylvia.

  • The value of the cell in the column Last name is Jones.

Then the Engine performs a verification. In this particular row, the value of the cell in the column Salary should be greater than 3000.

Steer Excel data

Special Technical Properties of Excel Cells

Excel table cells have some special technical properties which you can use to automate some features in Microsoft Excel:

  • BackgroundColor: set or verify background color using the format R=<x>,G=<y>,B=<z>, e.g. R=255,G=0,B=0 for bright red background.

  • Comment: use this property to add, update, verify, or perform other operations on the Excel Comment associated with a particular cell.

  • Font: set or verify the name of the font used for the cell, e.g. Arial Black.

  • FontColor: set or verify font color using the format R=<x>,G=<y>,B=<z>, e.g. R=255,G=0,B=0 for bright red font.

  • FontSize: set or verify number indicating font size.

    Note that Excel rounds numbers to the nearest .5, e.g. an input of 11.3 will be rounded to 11.5.

  • FontStyle: set or verify if the text in the cell is bold and/or italic, e.g. Bold Italic indicates that it is both.

  • FormattedText: verify or perform read-only operations on the text content of a cell.

  • Formula: set or verify the formula for the cell, e.g. =SUM(A1:A2) for the sum of the values in cells A1 and A2.

  • HasFormula: verify whether the cell has a formula, e.g. True for a cell with a formula.

  • IsHidden: set or verify whether the cell is hidden, e.g. True for a hidden cell.

  • IsLocked: set or verify whether the cell is locked, e.g. False if the cell is not locked.

  • Note: use this property to add, update, verify or perform other operations on the Excel Note associated with a particular cell.

  • NumberFormat: use this property to set, verify or perform other operations using the formatting string for a particular cell. For more information, see the Microsoft documentation.

    Some of the formatting syntax is language-specific with regard to the language settings in Microsoft Excel.

    As an example, NumberFormat==0.0% in an English version of Microsoft Excel is equivalent to NumberFormat==0,0% in a German version of Microsoft Excel.

  • Underline: set or verify underlining for the text in the cell, e.g. Double for double underlined text.

In this example, you steer technical properties of the data range ExampleRange.

You perform the following actions:

  • You verify that cell #2 contains a formula.

  • You add to cell #3 the formula SUM(A15:B15).

  • In cell #7 you set the background color to R=123, G=0, B=50.

  • In cell #9 you verify that the name of the font is Arial Black.

  • In cell #9 you set the font size to 22,5.

  • You verify that cell #10 is not locked.

  • You set the number format in cell #11 to 0,00;[Rot]0,00.

  • In cell #5 you set the font style to Regular.

  • In cell #6 you set the font color to R=100, G=50, B=102.

  • In cell #7 you verify if the text Single Accounting is underlined.

  • You verify that column #16 is hidden.

Use technical properties of cells in Excel

TBox Clear Excel Range

The Module TBox Clear Excel Range allows you to clear data and formatting from a range of cells in an Excel file.

It has the following ModuleAttributes:

ModuleAttribute

Description

Range Name

Name of the range that you defined.

Contents

Specify whether you want to clear the content of the specified range. You can use the following values:

  • False: don't delete the cell contents.

  • True: delete the cell contents. This is the default value.

Formats

Specify whether you want to clear the formatting of the specified range. You can use the following values:

  • False: don't clear the cell formatting. This is the default value.

  • True: clear the cell formatting.

Notes and Comments

Specify whether you want to clear notes and comments from the specified range. You can use the following values:

  • False: don't clear notes and comments. This is the default value.

  • True: clear notes and comments.

Hyperlinks

Specify whether you want to clear hyperlinks from the specified range. You can use the following values:

  • False: don't clear hyperlinks. This is the default value.

  • True: clear hyperlinks.

Outlines

Specify whether you want to clear outlines from the specified range. You can use the following values:

  • False: don't clear outlines. This is the default value.

  • True: clear outlines.

In this example, you clear data and formatting from a range of cells named MyRange.

Clear a range of cells

TBox Run Excel Macro

The Module TBox Run Excel Macro allows you to run existing Microsoft Excel macros on an Excel file. For information on Microsoft Excel macros, see the Microsoft Excel documentation.

The Module has the following ModuleAttributes:

ModuleAttribute

Description

Workbook Name

Name of the workbook that you defined.

Macro Name

Name of the Microsoft Excel macro that you want to run.

Timeout

Maximum duration (in milliseconds) for which the macro is allowed to run. Upon reaching this threshold, the running macro will terminate and the test step will fail.

In this example, you run a macro called HighlightOverdueAccounts on the workbook Accounts. You set the maximum duration for the macro to 10000 milliseconds.

Run a macro on the workbook Accounts

TBox Close Excel Workbook

The Module TBox Close Excel Workbook allows you to close an open workbook. This step is mandatory for every Excel Engine 3.0 TestCase.

The Module has the following ModuleAttributes:

ModuleAttribute

Description

Workbook Name

Name of the workbook that you defined.

Path

Full path to the Excel file that you want to close, including the file name and extension.

Save

Specify whether you want to save the Excel file.

  • Value False: don't save the file. This is the default value.

  • Value True: save the file.

Save Path

Absolute path to where you want to save the file, including file name and extension. If you don't provide a path, Tricentis Tosca overwrites the existing file.

Save As Type

File format in which you want to save the file.

This ModuleAttribute only works together with Save Path. If Save Path is empty, Tricentis Tosca ignores any value you have selected for Save As Type.

If you specify a Save Path but leave Save As Type empty, Tricentis Tosca uses the file format of the existing file.

To prevent file corruption, the file extension provided in Save Path must match the extension you choose for this ModuleAttribute.

In this example, you close the Excel file sales_data.xlsx, located at C:\Temp.

The Excel Engine 3.0 saves any changes to sales_orders.pdf, located at C:\Temp\Orders. You specify the file format as PDF (*.pdf).

Save and close an Excel file

TBox Excel 1:1 File Compare

The Module TBox Excel 1:1 Compare allows you to compare the cell contents, cell formatting, and objects of two Excel files.

The Module has the following ModuleAttributes:

ModuleAttribute

Description

Reference File

Full path to the reference Excel file, including the file name and extension.

Reference Password

Enter the password if your reference file is password-protected.

Target File

Full path to the target Excel file, including the file name and extension.

Target Password

Enter the password if your target file is password-protected.

Include Cells Data

By default, the Excel Engine 3.0 compares the cell content. If you don't want to compare cell content, enter the value False.

Include Formats

By default, the Excel Engine 3.0 doesn't compare cell formatting. If you want to compare cell formatting, enter the value True.

Include Objects

By default, the Excel Engine 3.0 doesn't compare object types, such as images, shapes, and charts. If you want to compare objects, enter the value True.

Include Sheets

If your Excel files have multiple data sheets, you can define which sheets you want to compare. To do so, enter the sheet names, separated by a semicolon (;).

To compare all sheets, leave this value empty.

Output Path

If you want to save the result to a text file, enter the full path to the file, including the file name and extension.

In this example, you compare the Excel files reference.xlsx and target.xlsx. Both files are located at C:\Temp. You perform the following steps:

  • You specify the required password for each file.

  • You specify that you want to compare only the cell contents of Sheet1 and Sheet2.

  • You specify that Tricentis Tosca should save the result to C:\Temp\report\results.txt.

Compare two Excel files