Comparing Two Variables

Objective

The objective of this How-To guide is to demonstrate the functions of the If command. This will be done by checking to see if a certain Excel file exists, comparing two values in an Excel file, and then writing to that same file before saving it. If the file does not exist, then a message stating that the file does not exist will be shown. OpenBots Studio enables the user to utilize If statements in order to perform tasks like comparing variables and values, determine which window is active, determine if a certain error has occurred, or even determine if certain GUI (Graphical User Interface) elements exist. Multiple If statements are very useful when certain actions need to occur once multiple criteria are met.

Prerequisites

In order to follow along with this guide, it is recommended that the user create an Excel file that contains at least two rows that themselves contain two numeric values. The file does not need to be complex and a screenshot of the file that was created for this exercise has been attached. Also ensure to save the file in the project folder alongside the Main .obscript file.

Walkthrough

The following are the steps involved in checking for a file, comparing variables within that file, and saving that file using OpenBots Studio. This automation involves click level operations from the Microsoft -> Excel, Core Commands -> If, and Core Commands -> Misc command lists.

Step 1: The If command can be used to perform a set of actions only if the specified file exists.

Select If command
Condition Option: Builder
If Condition: File Exists
Select file under File Path (Variable called vFilePath used to indicate file in this example)
True When: It Does Exist
Click OK

Note: All of the commands placed within an If statement will only be executed if the conditions of said If statement are true. If an Else command is placed within the If statement, then anything that is placed within the Else command will execute if the If statement is NOT true.

Example 1:

If (1 < 2)
Write “1 is less than 2” (This code will execute because the condition is true)
Else
Write “1 is greater than 2” (This code will not execute because the condition is true)
End If

Example 2:

If (1 > 2)
Write “1 is greater than 2” (This code will not execute because the condition is false)
Else
Write “1 is less than 2” (This code will execute because the condition is false)
End If

Step 2: In order to access an Excel file throughout the entire process, the Excel application must be opened, and an instance must be created first. For a more in-depth explanation of the Excel commands used in this guide, please review the Excel How To Guides here (https://openbots.ai/product/openbots-studio/how-to-guide-for-openbots-studio/excel-automation/). The Create Excel command is used to open Excel and create a new instance. This command will be placed within the File Exists If statement.

Select the Create Excel Application Command
Enter the Excel Instance name variabe (User’s Choice) (Variable used for this guide will be Test.)
Select “Open Workbook”
Enter ProjectPath + @“\Quick Test.xlsx” (By using Project Path, it can be ensured that the automation will always check the project folder)
Select the visibility criteria (Yes)
Select whether to close all existing Excel instances (User’s choice) (Yes)
Click OK

Step 3: In order to compare the two values within the Excel file that was just opened, the Get Cell command must be used to extract the two values and store them in variables. This will be placed underneath the Open Workbook Command.

Select the Get Cell Command
Enter the Excel Instance name (Test)
Enter the Cell Location of the value that needs to be extracted (Depends on user’s file. For this guide, the value was in cell A2.)
Enter the name of the variable that will be created to store the cell value. (User Choice) (This guide uses the name vValue1.)
Click OK

Step 4: The second cell value needs to be extracted as well and placed beneath the previous Get Cell command.

Select the Get Cell Command
Enter the Excel Instance name (Test)
Enter the Cell Location of the value that needs to be extracted (Depends on user’s file. For this guide, the value was in cell B2.)
Enter the name of the variable that will be created to store the cell value. (User Choice) (This guide uses the name vValue2.)
Click OK

Step 5: Now that the two cell values have been extracted, they can be compared using the If command again. A simple value comparison is all that is needed for this guide. This command will be placed within the File Exists If statement underneath the previous command.

Select the “Number Compare” Condition Type
Input vValue1 (the value that stores the first cell extracted) into the “Number1” Parameter Value.
Select an Operand option (User Choice)(“Is greater than or equal to” was chosen for this guide)
Input vValue2 (the value that stores the second cell extracted) into the “Number2” Parameter Value.
Click OK

Note 1: Variables and values can be compared to each other using this and other Condition Types as well.
Example: vValue1 is greater than 5

Note 2: The code created so far should appear very similar to the code above. An If statement within an If statement is called a Nested If statement. Nested If statements can be used to check if multiple conditions are true, however, the Begin Multi If command (covered in the Semi-Complex If Guide) is a better choice to use if multiple conditions are needed.

Step 6: Within the Comparison If statement, an action needs to be taken if the condition is true. A simple command like a Write Cell command can be added to show that the comparison was true. It will be added to the Excel file and the command itself will be placed within the Comparison If statement.

Select the Write Cell command
Enter the Excel Instance name (Test)
In the Cell Value field, write a message that indicates that the comparison performed was true. (Because “vValue1 is greater than or equal to vValue2” was chosen as the condition and the condition was true, the message in this guide will be “Value 1 >= Value 2”
Enter the Cell Location where the message will be placed within the Excel file. (Depends on user’s file. For this guide, the message was placed in cell C2.)
Click OK

Step 7: But what if the condition in the Comparison If statement is not true? Then a set of backup actions are needed. These actions would be placed inside of an Else command that itself should be placed within the Comparison If statement after the Write Cell command created in the last step.

Select the Else Command and click OK

Step 8: Within the Else statement, an action needs to be taken if the condition is not true. Another simple command like a Write Cell command can be added to show that the comparison was false. It will be added to the Excel file and the command itself will be placed within the Comparison If statement.

Select the Write Cell command
Enter the Excel Instance name (Test)
In the Cell Value field, write a message that indicates that the comparison performed was true. (Because “vValue1 is greater than or equal to vValue2” was chosen as the condition and the condition is false, the message in this guide will be “Value 1 < Value 2”
Enter the Cell Location where the message will be placed within the Excel file. (Depends on user’s file. For this guide, the message was placed in cell C2.)
Click OK

Note: At this point in the process, the automation code should be similar to the screenshot above.

Step 9: Between the two End If commands, add a Close Excel Application command because the Excel file has been edited and is no longer needed.

Select the Close Excel Application command
Enter the Excel Instance name (Test)
For Save Workbook, select Yes.
Click OK

Step 10: Because all of the previous steps in this guide were based on the assumption that the File Exists If statement was true, another Else command is needed to specify the actions to take if the File Exists If statement is NOT true. This means that the Close Excel Application command marks the end of the commands that will execute if the condition in the File Exists If statement is true. This Else command should be placed after the Close Excel Application command but before the last End If command.

Select the Else Command and click OK

Step 11: If the file specified in the first step does not exist, then it cannot be edited, and the user of the program would need to be notified. This can be done with a simple Show Message command which will create a window that contains a specified message. In this case, a message stating that the file does not exist. Place the Show Message command inside the Else statement created in the last step. This marks the end of the process.

Select the Show Message Misc Command
Enter the message that the window will display. (In this guide, the message “File does not exist” was chosen.) (User Choice)
Enter the amount of time that the window will stay open. (User Choice)
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 compare variables using OpenBots Studio. Feel free to run the code to test out the automation!

Commands utilized for the above concept (Comparing Two Variables) are as follows:

Excel Commands: Create Excel Application, Get Cell, Write Cell, Close Excel Application

If Commands: If, End If, Else

Misc Commands: Show Message

Lets stay in touch!