Loading data into new worksheet

Objective

The objective of this How-To guide is to load data in Excel Workbook using Append Row and Append Cell command. 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 append new data to the already existing data in workbook or new blank workbook.

Walkthrough

The following are the steps involved in loading 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 automation can differentiated 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 used to identify the Excel file opened by this command) (vExcel)
Select ‘Open Workbook’ from dropdown
Click “Select A File” button and select the file to be opened (It must contain “.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: A new sheet in the same Excel can be accessed and activated using Activate Sheet command and then data can be loaded into it.

Select Activate Sheet Command
Enter the Excel Instance name (vExcel)
Enter the worksheet name (Name of the new worksheet that is supposed to be activated)(Sheet2)
Click OK

Step 3: A new row can be appended in the new sheet that is activated using Append Row command. Data is loaded using the append row command by providing values with specific separator (Example: Comma).

Select Append Row Command
Enter Excel Instance name (vExcel)
Enter the data to be loaded (The row data that is appended) (Data should be passed in as a List or DataRow)
Click OK

Step 4: To load the value of a cell in excel, append cell command is utilized to perform the task by appending a new cell at the end of the excel sheet.

Select Append Cell Command
Enter Instance name (vExcel)
Enter the cell value to be appended (519 cupcakes)
Click OK

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

Select Save Workbook As Command
Enter the Excel Instance name (vExcel)
Select the folder to save the new file within. (Path where the file is supposed to be saved)
Insert Workbook file name (Type the desired name of this new file) (CurrencyUpdated)
Click OK

Step 6: 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 of the process containing the commands used to load data in Excel using OpenBots.

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

Excel Commands: Create Excel Application, Activate Sheet, Append Row, Append Cell, Save Workbook As, Close Excel Application.

Lets stay in touch!