Overwriting data in excel file

Objective

The objective of this How-To guide is to overwrite data in Excel Workbook using Write Row and Write Cell commands. Most processes require utilization of Excel to load or update data according to the project’s specific requirements. OpenBots enables the user to create new instance of Excel, open new workbook and then overwrite a new row or cell to an already existing row or cell in a workbook, respectively.

Walkthrough

The following are the steps involved in overwriting data in Excel using OpenBots. 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 the automation can differentiate between different Excel windows (if multiple windows are created). The Create Excel command helps in performing this task.

Select Create Excel Application Command
Enter Excel Instance name (Variable of the ‘OBAppInstance’ type used to identify the Excel file opened by this command) (vExcel)
Select ‘Open Workbook’ for the ‘New/Open Workbook’ property.
Click “Select A File” button and select the file to be opened (it must contain the “.xlsx” extension)
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)
Click OK

Step 2: Once the excel sheet has been opened, the last row index is attained using Get Last Row Index command. Column A is considered as the starting point. The last row index is attained to determine the number of rows, column A has. Once that number is determined, the value is stored in a variable.

Select Get Last Row Index Command
Enter the instance name (Global instance created earlier) (vExcel)
Enter Letter of the column to check (A)
Create a variable to receive the last row number (vLastRowIndex)
Click OK

Step 3: An entire row in an excel can be overwritten using the Write Row command. OpenBots allows in inserting the new row values in an already existing row that needs to be overwritten. The location of the row that needs to be overwritten must be mentioned specifically.

Select Write Row Command
Enter Instance name (vExcel)
Enter the row value that needs to be overwritten in an already existing row (new List(){“5”, “INR”, “AUD”,”67”})
Enter the location where the row value needs to be overwritten (“A” + vLastRowIndex.ToString())
Click OK

Step 4: A cell can be accessed or attained in an excel workbook using Get cell command. The location of the cell must be mentioned specifically.

Select Get Cell Command
Enter Instance name (vExcel)
Enter the cell that needs to be accessed (D2)
Assign the value in cell D2 to a variable (vCell)
Click OK

Step 5: A cell in an excel workbook can be overwritten using the Write Cell command. OpenBots allows in inserting a new cell value in an already existing cell. The location of the row that needs to be overwritten must be mentioned specifically.

Select Write Cell Command
Enter Instance name (vExcel)
Enter the cell value that needs to be overwritten over the already existing cell (Cell)
Enter the location where the cell value needs to be overwritten (“D” + vLastRowIndex.ToString())
Click OK

Step 6: After loading data in the workbook, the user is required to save the workbook. The ‘Save Workbook as’ command will save the new workbook under a new name into a specified folder.

Select Save Workbook as Command
Enter the Excel Instance name (vExcel)
Select a folder to save the Workbook within it.
Enter the new workbook file name (Overwrite_Excel)
Click OK

Step 7: 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 Close Excel Application Command
Enter the Excel instance name (vExcel)
Select to save the workbook before closing or not (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 overwrite data in Excel using OpenBots.

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

Excel Commands: Create Excel Application, Get Last Row Index, Write Row, Write Cell, Get Cell, Save Workbook As, Close Excel Application.

Lets stay in touch!