Excel modules

The technology-specific modules for excel are standard modules that perform specific tasks.

Open Excel Workbook

Opens an Excel file, such as an Excel workbook. Every workbook can contain several worksheets.

Value

Description

Workbook Name

Name for the workbook. This can be any name.

Path

Full path to the Excel file 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 system creates the file in memory. You can save the file later with the Close/Save 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.

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

  • True: open read-only.

Delimiter

Optional. Use this parameter if Tosca Cloud opens files in Excel but doesn't convert columns because of your Microsoft Office language pack. For instance, some locales use ; instead of , in CSV files. Specify which delimiter you want to use:

  • System to use your current system's delimiter character.

  • Office to use the delimiter defined by the current setting in Excel.

Alternatively, you can enter the character that separates values in your CSV files.

Define Excel Range

Defines a data range you want to steer. This step is mandatory for every test case in which you want to manipulate a particular data range.

Value

Description

Workbook Name

Name of the workbook that you defined.

Worksheet Name

Position or name of the Excel worksheet that contains the data.

  • To identify the worksheet by its position in the index, enter $<number>. For example, $3 to identify the third worksheet.

  • To identify the worksheet by name, enter the exact name. This must perfectly match the name in the workbook.

Range Name

Name for the data range you want to steer. This can be any name.

Data Range

Specify how you want to define your Excel data range:

  • Explicit: manually defines the range based on start and end cells. You do this by specifying the exact cells in both Data Range->Start Cell and Data Range->End Cell.

  • Used Range: lets Excel automatically define the range based on what's currently used in the worksheet.

Note that the specified range influences how the system calculates the values for the properties ColumnCount, RowCount, ColumnNumber, RowNumber, and ResultCount.

Open as Ready-only

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

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

  • True: open read-only.

Delimiter

Optional. Use this parameter if Tosca Cloud opens files in Excel but doesn't convert columns because of your Microsoft Office language pack. For instance, some locales use ; instead of , in CSV files. Specify which delimiter you want to use:

  • System to use your current system's delimiter character.

  • Office to use the delimiter defined by the current setting in Excel.

Alternatively, you can enter the character that separates values in your CSV files.

Create Excel Worksheet

Creates a new worksheet in an existing Excel file.

Value

Description

Workbook Name

Name of the workbook that you defined.

Worksheet Name

Name of the worksheet.

Worksheet Order

Define where the system 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.

Delete Excel Worksheet

Deletes a worksheet in an existing Excel file.

Value

Description

Workbook Name

Name of the workbook that you defined.

Worksheet Name

Position or name of the Excel worksheet that you want to delete.

  • To identify the worksheet by its position in the index, enter $<number>. For example, $3 to identify the third worksheet. For example, $3 to identify the third worksheet.

  • To identify the worksheet by name, enter the exact name. This must perfectly match the name in the workbook.

Update Excel Worksheet

Update a worksheet in an existing Excel file.

Value

Description

Workbook Name

Name of the workbook that you defined.

Current Worksheet Name

Position or name of the Excel worksheet that you want to delete.

  • To identify the worksheet by its position in the index, enter $<number>. For example, $3 to identify the third worksheet. For example, $3 to identify the third worksheet.

  • To identify the worksheet by name, enter the exact name. This must match the name in the workbook.

New Worksheet Name

The new name you want to give to the worksheet.

New Order

Define where the system 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.

Manipulate Excel Range

Manipulates data in an Excel file.

Value

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.

Clear Excel Range

Clears data and formatting from a range of cells in an Excel file.

Value

Description

Range Name

Name of the data 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 notes and comments from the specified range. You can use the following values:

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

  • True: clear outlines.

Run Excel Macro

Runs existing Microsoft Excel macros (opens in new tab) on an Excel file.

Value

Description

Workbook Name

Name of the workbook you defined.

Macro Name

Name of the Microsoft Excel macro 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.

Close/Save Excel Workbook

Closes an open workbook.

Value

Description

Workbook Name

Name of the workbook you defined. Note that you don't need to specify a Workbook Name if you specify a Path.

Path

Full path to the Excel file that you want to close or save, including the file name and extension. Note that you don't need to specify a Path if you provided the Workbook Name.

Operation

Specify the type of operation you want to perform:

  • Save the Excel file.

  • Save & Close the Excel file.

  • Close the Excel 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, Tosca Cloud overwrites the existing file.

Save As Type

File format in which you want to save the file.

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

If you specify a Save Path but leave Save As Type empty, Tosca Cloud 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.

Excel 1:1 File Compare

Compares the cell contents, cell formatting, and objects of two Excel files.

Value

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(s)

Specify which file(s) you want to compare to the Reference File.

Target File Password(s)

Password(s) for decrypting the Target File(s). To specify multiple passwords, use a semicolon (;) as a separator.

Moreover, note the following:

  • The number of passwords must match the number of files you specify as Target File(s).

  • If you specify folder paths as Target File(s), the same password(s) applies to all files within each specified folder.

Include Cells Data

If you don't want to compare cell contents as the default, enter the value False.

Include Formats

If you want to compare cell formatting, enter the value True.

If you are dealing with very large Excel spreadsheets, this option may have a negative impact on the performance of your tests. We recommend setting this to True only if cell formatting is relevant for your use case.

Include Objects

If you want to compare object types, such as images, shapes, and charts, enter the value True.

Include Sheets

If your Excel files have multiple data sheets, you can define which sheets of the Reference File you want to use for the comparison. 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.

Abort after Differences

Optionally, specify the number of differences allowed in a file before the comparison stops and the test fails. This means you don't need to wait for the entire comparison to finish, especially for large reference and target files.

If don't specify any number, Tosca Cloud performs a complete comparison and reports all file differences.