Deleting data in a worksheet

Objective

The objective of this How-To guide is to delete data in an Excel Workbook using the Delete Row and Delete Range commands. Most processes require utilization of Excel to load or update data according to a project’s specific requirements. OpenBots Studio enables the user to create new instance of Excel, open a workbook and then delete an entire row or even a range of data in an Excel workbook.

Walkthrough

The following are the steps involved in deleting data in Excel using OpenBots Studio. It is a procedure that involves click level operations from the Excel command list.

Step 1: To access an Excel file throughout the entire process, a global instance must be created first so that automation can be differentiated between different Excel windows (if multiple windows are created). The Create Excel Application command helps in performing this task.

Select the Create Excel Application command.
Enter Excel Instance Name. (This is the variable used to identify the Excel file opened by this command. The Excel Instance variable used for this guide will be vDeletingData.)
For the New/Open Workbook field, select the “Open Workbook” option. (This will create an empty Workbook File Path field.)
Enter the file path of the file that will be opened into the Workbook File Path field. (The file opened for this guide will be a test file that is located in the project folder alongside the Main.JSON and the project.config files. It will be displayed below. To ensure that a file can be found within the project folder no matter what computer/system the automation is run on, the {ProjectPath} file path is used.)
Select the visibility criteria (Yes, to make the Excel window visible during process execution.)
Select whether to close all existing Excel instances (User’s choice) (Yes is chosen to prevent any errors that may be caused by the file already being open when the automation is run multiple times.)
Click OK

Step 2:Now that the file is open, the data within it can be edited/deleted. This can be done with several different commands in multiple different ways. For example, the (Excel) Write commands will technically delete whatever data is in a cell and overwrite it with the data that was specified within the command itself. However, the (Excel) Delete commands erase whatever data is within the cells specified by the different (Excel) Delete commands. The first of the commands that will be discussed will be the Delete Row command.

Select the Delete Row command.
Enter the Excel Instance Name (vDeletingData).
In the Row Number field, enter the number of the row that will be deleted.
Select whether to shift the remaining cells up. (Yes was chosen for this guide. This works similarly to shifting cells up in Excel.)
Click OK

Step 3:The Delete Range command is another command that will delete the data within the cells of the range that is provided. This can expand across multiple rows and columns.

Select the Delete Range command.
Enter the Excel Instance Name. (vDeletingData)
Enter the range of cells that will be deleted. (A3:B4 was used for this guide.)
The Shift Cells Up command can be set to “No.” (The “No” option was selected because if the cells are shifted up in certain situations, then incorrect data could end up being saved in a document. Explanation below.)
Click OK

Note: If the cells are shifted up in this scenario, it will lead to a workbook that looks like the image below. The data in this version of the workbook is incorrect because the data was shifted into incorrect spots. For those that are unaware, 1 Euro does not equate to 1049.68 Indian Rupees. At present, it translates to 86.20 Indian Rupees.

Note: The guide will produce the below file. Take notice that the second row shown in the screenshot in Step 1 no longer exists and the third row in that screenshot is now the second row in this screenshot because the cells were shifted up in Step 2.

Step 4: After the files have been thoroughly edited, the file can be saved. This can be done with the Save Workbook command.

Select the Save Workbook command.
Enter the Excel Instance Name. (vDeletingData)
Click OK

Step 5: Once the process has been completed, the Excel instance must be closed using the Close Excel Application command. It is important to close these instances to avoid errors caused by the file already being open when the process is executed. An error will occur if the file is already open when the process is run.

Select the Close Excel Application command.
Enter the Excel Instance Name. (vDeletingData)
Select to save the workbook before closing. (Yes)
Click OK

All the commands in this guide can be utilized alongside many of the other commands that will be discussed in later guides. Below is a snapshot containing the commands used to Delete data in Excel using OpenBots Studio.

Commands utilized for the above concept (deleting data in Excel workbook) are as follows:

Excel Commands: Create Excel Application, Delete Row, Delete Range, Save Workbook, Close Excel Application

Lets stay in touch!