Categorize Employees in OpenBots Studio
The goal of the automation is to create an individual excel document for each occupation. Each workbook created will contain the information of the employees that fall within that category. The first step is to open OpenBots studio and create a new project. Make sure the project name is relevant to the automation. We will now add a command describing the overall purpose of the process. Commands are added throughout the process to serve as a guide indicating why certain steps are being taken.
OpenBots Server assets allow its users to reference data from a centralized point. Since the employee files are updated on a monthly basis, we add the current date to the asset name in order to reference the correct file. This file will now be available to any agent connected to the server. Try catches are added to the project in order to ensure the proper measures are taken if an unexpected error is encountered. In this case, we use Get Exception Message to capture the description of the encountered error, and a log of type error to ensure it is properly documented
The next steps will involve accessing the employee file and creating the file, which will contain the output for the automation. The Format Date command under Data Commands can be used to get the current month and year in the wanted format. The control key shortcut is used to create a new variable in the variable manager. This variable will contain the formatted date and can be referenced throughout this script. Next, the create folder command is used to create a folder in the specified path. The project path variable is always included when creating a project in Studio. It references the path to the project folder of the machine running the automation. In order to get the file asset from the server, we need to use the get asset command. Indicate the asset name that was used when creating the asset in OpenBots Server.
Use the previously saved formatted date. Indicate the asset type and the location in which the file will be saved. Next, we will script the downloaded excel file based on the occupation column. When working with excel files, the first step is to use the create excel application command. Provide the Excel instance name. This is used to indicate which excel the bot is currently referencing. Since the file already exists, we use the open workbook option and indicate the file path to the employee information file. OpenBots allows you to indicate if the excel automation will be visible or hidden. In this case, let’s set the visible field to no and close all existing excel instances to yes. This will ensure there are no hanging excel instances that will interfere with the automation.
The Split Range by Column command available in OpenBots facilitates the categorizing process. It only needs the excel instance name referencing the targeted file, the range, the column name, and the path to the folder in which the generated workbooks will be saved. In addition, you can indicate the variable which will hold a list of all the data tables generated after the split, which can be used to modify its contents as needed. Finally, the Close Excel Application ensures the excel instance is closed properly. When working with Excel instances, it is best practice to include a check if app instances exist and if it does, then close the excel application.
