Software Automation

In RPA processes, we often need to automate interactions with common software programs like Microsoft Office programs (Word, Excel, etc.) and various web browsers.Of course, these software programs all have interfaces, and you can interact with them directly through Laiye RPA with the knowledge you've learned in Chapter 3, "Command with Target." However, because of how common these software automations are needed, Laiye RPA provides special Commands that encapsulate automated interactions with Excel, Word, Outlook, browsers, and databases. Working with these special Commands are vastly more efficient and convenient than simulating interface interactions. For example, while we can simulate interactions with the Excel interface to open and read an Excel file, it's quite complicated. However, using special Excel automation Commands, we can achieve the same goal using only one Command.

Before we can get started using Laiye RPA to automate these software programs, you must install the programs on your local computer. For Excel and Word automation, you need to install Office 2007 or above, or WPS 2016 or above. For browser automation, you need to install Internet Explorer (IE), Google Chrome, or Firefox.

In this chapter, we assume that the reader has a preliminary understanding of browser, Word, Excel, database , and working knowledge with all these programs is even better. If you need to learn more about these tools before we get started, resources on these topics are easily accessible on the internet through a simple Google search.

Excel Automation

Excel is an important member of Office software suite. It provides powerful tools for making calculations, analyses, and visualizing data, and it is one of the most used software for processing spreadsheets. This makes it a common target for RPA platforms to automate.

Before we start automating Excel, let us define a few concepts. Every Excel file corresponds to a workbook , which the user can use to modify and store data in the file. Whenever you open an Excel file, the title on top is the name of the workbook. A workbook has multiple worksheets , and a workbook contains three worksheets by default, named Sheet1, Sheet2, and Sheet3. Of course, you can always add or delete worksheets.

An Excel worksheet is a two-dimensional table made up of cells. Each cell is determined by a row and column number. Row numbers are represented by a sequence of numbers such as 1, 2, 3, 4, etc. Column numbers are represented by a sequence of uppercase letters such as A, B, C, D, etc. Therefore, you can refer to a cell by its column number + row number. For example, cell B3 refers to the cell intersected by the third row and the second column of the worksheet.

When using Laiye RPA to automate operations on an Excel table, you first need to open a workbook. The ensuing operations to a particular worksheet or cell are all within the scope of the opened workbook. When you are done using a workbook, you need to close it using a command.

Let's try to open a workbook with Laiye RPA. Click to expand the Software Automation category in the command area and expand the item Excel. Insert the first Command listed, Open Excel File, into the assembly area.

Notice that this Command takes three Properties (Figure 83). The Property "File Path" takes the file location of the intended Excel file (we support .xls, .xlsx, .xlsm files). As we have mentioned earlier, this can be the absolute path to the file or a relative path like @res"example.xlsx", which points to the "res" directory under the directory of the current process. Moreover, whenever the "\" appears in a string in Laiye RPA, rewrite it as "\".

If the given workbook file exists, we will operate on that file when running the process. If it does not exist, then Laiye RPA will create an empty Excel file in that directory and operate on the created file.

The Property "Visible or not" takes a Boolean value (true or false) and indicates if Laiye RPA will display the opened file using Excel. If the value is false, Laiye RPA will operate on the file without displaying the Excel interface.

The Property "Output to" takes a variable name to take on a value that refers to the Excel workbook we have opened. The value is a workbook object, and when we use other Commands to operate on this workbook, we need to pass in the workbook object to indicate that this is the workbook to operate on. For example, in Figure 84, we store the workbook object in the variable objExcelWorkBook. In subsequent Excel Commands, we will pass in objExcelWorkBook to the Property "Workbook Object".

Let's try to read the content of cell A1 in Sheet1 of this workbook. Insert a Read Cell Command. Its Properties are shown in Figure 84.

As we have said, the Property "Workbook Object" here should be the same as the value we chose for the Property "Output to" in the Command "Open Excel". Therefore, we use objExcelWorkBook to indicate that we want to read the cell content from the workbook we have just opened.

Besides, we need to specify which worksheet and cell to read in Properties "Worksheet" and "Cell". There are two methods to do so. The first one is to enter "Sheet1" and "A1" (as strings) respectively to read Cell A1 in Sheet1.

The second is to use one integer to refer to the worksheet and two integers in brackets to the cell. Since serial numbers usually start with "0" in Laiye RPA, "1" would refer to the second worksheet and the rest follow the same rule despite their given names. [X, Y] with two integers in brackets, that is, an array, could refer to a cell, in which X is its row and Y is the column (we will get to know about arrays in detail later). Both X and Y start with "1" and [1,1] would refer to the cell "A1".

The advantages of these two methods are quite obvious. For example, business personnel may find it easier to understand if conduct the first way, using strings to present worksheets and cells. When using integers to present worksheets and cells, we can store these integers in variables so that they could be changed flexibly according to business requirements. To be more specific, when using [X, Y] to present a cell, we can read multiple cells quickly by putting variables "X" and "Y" into a loop for regular value increase. To choose strings or arrays may depend on our actual needs.

We also need to provide a variable name for the Property "Output to", but in this case, the variable will be assigned the content read from the cell. If the content of the cell is a number, then the variable will also be assigned a value. If the content is a string, then the variable will be a string.

This is great, but in our daily work, we often need to read in data from multiple cells. If we can only use Laiye RPA to read one cell at a time, this would quickly become cumbersome and inefficient. Luckily, Laiye RPA provides a Command "Read Range", which allows you to read the content of all the cells in a rectangular area. Insert a "Read Range" Command. Its Properties are shown in Figure 57.

The "Read Range" Command shares two common Properties with the Read Cell Command: "Workbook Object" and "Worksheet", which refer to the workbook and worksheet to read data from.

The Property "Range" takes a string that indicates the area to read, specified by the top left cell and the bottom right cell of the area, joined by a colon. For example, filling in "A2:B6" will read an area from the worksheet that starts with the A2 cell as the top left corner and ends with the B6 cell as the bottom right corner, with a total of 12 cells distributed in 6 rows and 2 columns.

The Property "Range" can also be set as two-dimensional arrays. For example, [[2,1], [6,2]] and " A2:B6" could refer to the same area. Integers in this two-dimensional array can be replaced by variables so that the range can change along with business requirements flexibly. If you have no idea about two-dimensional arrays, just ignore this method and set the property "Range" as strings.

The Property "Output to" has been filled in with the variable named arrayRet , which means the data read from the workbook will be passed to the variable arrayRet. As shown in Figure 86, Laiye RPA is asked to read the area " A1:E2" with the command "Read Range". To demonstrate the result, we can insert the "Output Debug Info" Command to print the value of the variable "arrayRet" out.

We can find that the output is a two-dimensional array, with values like [["Joe", "Jack", "James", "Jay", "John"],["123456" , " 654321", "987654", "741258", "951753"]].

Even though we haven't formally introduced "arrays" and, in particular, "two-dimensional arrays", we will get to know them in detail later. For now, you only need to know that we can use the "Read Range" Command to read a rectangular area of an Excel worksheet and store it in a variable arrayRet.

In addition to reading the content, Laiye RPA also provides a series of Excel Commands to modify the content of a workbook. Let's try to write "Jack" in cell A7 of Sheet1 of the example workbook. Insert the "Open Excel" Command, and then insert a "Write Cell" Command. Figure 87 shows the Properties of the Write Cell Command.

The Properties "Workbook object", "Worksheet", and "Cell" mean the same thing as their counterparts in the "Read Cell" Command, selecting which cell of which worksheet of which workbook to write to.

The Property "Data" specifies the actual content to be written into the selected cell. It can be a number, a string, a variable, or an expression.

Commands that write data into an Excel file all share an important Property: "Save Now". Selecting "Yes" will cause Laiye RPA to save the change immediately, just like when we manually modify the content of an Excel file and press ctrl-s to save it. Selecting "No" results in the change not being saved immediately, and we have to use the "Save Excel" Command or set the Property "Save now" to "Yes" on the "Close Excel" Command to save the changes. Both methods save the changes, and it's just a matter of personal preference.

Other Commands that write data into an Excel file work similarly, so we refrain from going into details here. Do keep in mind that the Property "Data" of each "write" Command must be consistent with its write area for the data to be written correctly. Specifically, when writing into a cell, the Data Property should be the data of that cell. When writing into a row of cells, the Data Property should be a one-dimensional array representing a row of data points. When writing into an area, the Data Property should be a two-dimensional array representing several rows and columns of data points.

Finishing Excel related operations, it's recommended to close the corresponding file with a "Close Excel" Command. Otherwise, the file will be kept open even if the process ends in Laiye RPA. Then this open file continues to consume system resources, and this is even harder for us to realize when the file was opened invisibly.

Word Automation

Similar to Excel, Word is also an important member of Office software suite.Word files are the standard medium for digitally encoding work documents. Therefore, automating Word is a necessity for a mature RPA platform.

Like in Excel, we need to first open a Word document to operate on it, and subsequent operations are all done on that opened document. After we are done making changes, we need to close the opened file.

Let's try and open a Word document using Laiye RPA. In the command area of Laiye RPA Creator, under the category Software Automation, click on "Word" to expand it and select the first Command, "Open File", which allows us to open a Word file.

This Command has five Properties, as shown in Figure 88.Let's first look at the Property "File Path". Here we need to specify the file path of a Word document (we support ".doc" and ".docx" files). Otherwise, the details are exactly the same as the Command "Open Excel". Here we open a test document example.docx,located under the directory "res".

Now, pay attention the two new Properties "Password for Access" and "Password for Edit". What do these mean?Sometimes, due to privacy considerations, we do not want other people to open our documents, or we do not want others to edit after opening it, so we set a password. There are two types of passwords. The first type is a "Password for Access", which you need to enter in order to open the document. The second type is a "Password for Edit", which you need to enter in order to modify the document. Laiye RPA will use the provided passwords to open the document. If the document has no password, simply set these Properties to empty strings "".

The Property "Visible or not" here has the same meaning as that of the Command "Open Excel", indicating whether to open the Word software interface when operating on the document.

The Property "Output to" is similar to that of the Command "Open Excel". It expects a variable name, and this variable will refer to the Word document we open. When performing various read and write operations on the document, we need to supply this variable to the Property "Document Object" of the Command to indicate which document to read or write to.As in our example in Figure 88, the variable filled in Property "Output to" is objWord , and in subsequent Word Commands, we need to provide objWord to the Property "Document Object".

Now, let's read the content of this Word document.Insert a "Read Document" Command after the "Open Document" Command. The Properties of this Command are shown in Figure 89.

The Property "Document Object" should be the same variable as the Property "Output to" in "Open Document": objWord. This indicates that we're reading from the document we just opened.

The "Output to" Property is filled with a variable named sRet , which means that content we read will be outputted to the variable sRet. Insert an "Output Debug Info" Command to print the content of sRet. After it runs, we can see something like Figure 90.

When we open the original document, we notice thatthe original Word document includes text, tables, and pictures, and the text is formatted in specific ways. The "Read Document" Command will read all the text in a document, but it does not support parsing text formatting, tables, and figures.

The "Read Document" Command operates on the entire document. Similar Commands include "Rewrite Document", "Save Document", "Save Document as", "Close Document", "Get Document Path", etc, all of which operate on the entire document.If we need to perform more fine-grained operations on the document, we need to use an important concept in Word: focus.Focus refers to the currently selected area in a Word document, and it is usually highlighted by the software. If no area is selected, the current cursor position is the focus.Therefore, the focus can be either a cursor position or a selected area. Word operations usually revolve around the focus. For example, if we want to change the font of a paragraph, we must select the text first, and then we can modify its size, color, style, etc. If we want to insert some text, picture, or other content, we also need to move the cursor to the insertion point first.

Let's see how to control the focus using Laiye RPA. Insert a Command "Set Cursor Position", which can move the cursor focus to a specified position.This Command has three Properties: the Property "Document Object" is the object " objWord" we have created earlier; the Property "Move Position" is used with the optional Property "Movement Mode" to indicate how many times to move; it can be one of "character", "line", and "paragraph", each corresponding to moving the cursor to the right by one character, moving it down by one line, and moving it down by one paragraph respectively. Here, we set "Movement Mode" to "line" and "Move Position" to two, which instruct our Command to move the focus down by two lines onto the third line. Please note that "Move Position" cannot be a negative number. This means that we cannot use this Command to move up or back.

Let's insert a Select Row Command to highlight a specific line. This Command has three Properties: the "Document Object" Property is the same as before, which takes the document object objWord ; the "Starting Line" and "Ending Line" Properties indicate the selected area. Here, we set "Starting Line" to 1 and "Ending Line" to 2, selecting lines 1 to 2 (2 lines in total).

In practical application, just using "Set Cursor Position" and "Select Row" does not work well. Why is that? Even though Word is a What You See Is What You Get software that allows you to format text and images, it often has some hidden formatting markings that affect the calculation of the positions of each "character", "line", and "paragraph". This makes locating the focus difficult and leads to unexpected results. Here's a little tip. We can mark the locations in a Word document we want to operate with text segments. If we want to insert a word somewhere, then we can leave a special marker. For example, add the text "Name" in that location of the Word document. Use the Command "Find Text and Set Cursor Position". There are two Properties of this Command. One is "Text content", and we can input "Name" we just added. The other is "Relative Position", and we can select "The text is selected". In this way, we can find the word "Name" we just added and select it. Finally, use the "Write Text" Command to replace the selected marker "Name" with the actual content. We can use this same technique to setup multiple special markers in a Word document and repeatedly use the "Find Text and Set Cursor Position" Command to fill out a Word document.

Back to our example. After we have moved our cursor to the specified position or after we have selected the specified content, we can start executing edit operations. Available operations include inserting content, reading content, deleting content, setting content format, cut/copy/paste content, etc. Here, we demonstrate the "Set Text Size" Command as an example. Insert a "Set Text Size" Command after the Command "Select Row". It has two Properties: the "Document Object" Property is set as the document object objWord we have created before, and the "Font Size" property specifies the font size to change the text to. Here, we set it to 9, which would change the selected text's font size to 9.

Outlook Automation

Outlook is an important member of Office software suite that helps in email transmission and cooperation. As shown in Figure 95, a new email is being written with Outlook 2019.

Many Commands are available in RPA Creator to automate Outlook, such as "Send Email", "Get Message List", "Reply Mail", "Download Attachment" and so on. Users only need to fill in the "Sender's email address", "Inbox", "Mail subject", "Email body", "Mail attachment" and other Properties. However, before editing Properties "Sender's email address" and "Inbox", make sure the email address has been bound to Outlook (multiple email addresses can to bound to Outlook).

And don't forget other Outlook Commands such as "Move Email" and "Delete Email". Note that these Commands discussed above mainly work on automating Outlook 2010, 2013, 2016, and 2019.

IBM Notes Automation

Like in Outlook, Laiye RPA can also automate the IBM Notes client with "Send Mail", "Get Mailing List", "Reply Mail", "Download Attachment" and other Commands. Same as Outlook, the email address should be bound to IBM Notes before executing automated operations. But configuration Properties of the two kinds of Commands are different. For all IBM Notes Commands, users don't need to fill in the sender's email address but have to set a password in Property.

For the moment, Laiye RPA supports automation in the Chinese version of IBM Notes 9.0.1 and 11.1.

Browser Automation

Browser automation is an important part of software automation. Automating actions like retrieving data from a certain website and interacting with web-based service systems is dependent on automating browser interactions.

First of all, we need to open a browser by using the "Launch a New Browser" Command. If there is already a browser program open on the computer, we can directly use that browser by invoking the Bind Browser Command, which gives you the same output object to work with as the "Launch a New Browser" Command.

Figure 101 lists the Properties of the Command "Launch a New Browser". The Property "Type of Browser" specifies which browser to start. Laiye RPA currently supports four browsers: Internet Explorer (IE), Google Chrome, Firefox, and RPA Creator-provided Browser. Using the first three browsers requires the corresponding browser to be installed on your computer. RPA Creator-provided Browser comes with RPA Creator 5.0 and versions above. Comparing with the first three browsers, RPA Creator-provided Browser has the following advantages: Firstly, robots can select elements directly in the browser without installing browser extensions (Both Google Chrome and Firefox require extensions to be installed in advance, during which an accident might happen. For example, the installation task is intercepted by anti-virus software.). Secondly, with RPA Creator-provided Browser, robots can select cross-domain elements (robots can't find the input boxes for the username and password to log in to NetEase Mailbox or QQ Mailbox using other browsers.). Thirdly, RPA Creator-provided Browser can call the JavaScript function directly on the visited web page. Users are recommended to choose RPA Creator-provided Browser to conduct browser automation, taking the advantages above into consideration. However, users have to choose other browsers to open some specific websites. For example, certain government websites or online banking sites can only function on Internet Explorer. In this case, we have to select "Internet Explorer" for the Property "Browser".

The "Open URL" Property specifies which link to open on the browser. Supplying "www.google.com", for example, will instruct the browser to open Google. You can always leave the link blank and open a website later using the "Open Webpage" Command.

When the "Launch a New Browser" Command behaves unexpectedly, such as if the browser cannot be found or the specific URL cannot be opened, Laiye RPA will try repeatedly until some maximum time limit is exceeded. This time limit is set using the "Timeout(ms)" Property.

There are two commonly used optional Properties. The "Path to Browser" Property links to the executable file of the intended browser. This is useful when you have multiple versions of the same browser software installed on one computer. This Property allows you to select the intended version to use. If this Property is empty, Laiye RPA will search in the default installation directory of the browser and use that version. The "Browser's Parameters" Property allows us to pass in additional specifications when launching a browser. Besides just the default startup method when we launch a browser by double-clicking its shortcut, browsers can actually be instructed to launch in very specific ways, like what website(s) to open by default, whether to full screen the browser window, whether to enable certain features, etc. We can specify these browser-specific parameters through the Browser's Parameters Property. To learn more about the supported parameters of each browser, please consult the relevant documentation.

After launching the browser, we can perform a series of operations to the browser itself and the webpages displayed on the browser. We can browse the web, enter texts in webpages, click on links and buttons, etc. For example, we can open Google's homepage, enter "Laiye RPA" on the search bar, and click the "Google Search" button to retrieve the search results of "Laiye RPA". We can complete these steps using Commands with target, which we have introduced in Chapter 3. Moreover, we can process the search results—scraping data, parsing data, etc.--using the Commands under the "Data Processing" category. We will introduce Data Processing Commands later.

Database Automation

In an information system, the most important content is its data. Nowadays, almost all information systems store their data inside databases. Besides using software clients to access the database, sometimes we also need to access and make changes to the database directly. Therefore, automating database operations is an indispensable part of RPA. Specifically, automating database operation allows us to login to a database using our username and password and interact with the database using SQL queries, all through a secure connection. For SQL basic knowledge, you can check other SQL tutorials online.

Let's see how we can access a database through Laiye RPA. First, we need to establish a connection to the database. Insert a "Create Database Object" Command, located under "Software Automation" – "Database". This Command will connect to a specific database and create a corresponding database object.

The "Create Database Object" Command has three Properties. The "Database Type" Property specifies the type of the database we are connecting to. Laiye RPA currently supports MySQL, SQLServer, Oracle, Sqlite3, and PostgreSQL databases. The "Database Config" Property is a string that describes some key information used to create a database object. This string is relatively long and difficult to parse, but we can click on the button to the right of this Property to view the value broken down as a list of sub-properties (Figure 103).

"Charset" refers to the character set of the database, and normally we can just keep the default "utf8". "Database" refers to the name of the database we are connecting to. "Host" and "port" refer to the IP address and port number of the database. In this tutorial, we connect to a database located on port "3306" of IP address "192.168.0.1", which can also be access through "http://192.168.0.1:3306". "User" and "password" sub-properties refer to the username and password used to access the database. By configuring these parameters, we have created a database object.

Different types of databases often have different sub-properties. For example, an Oracle database does not have the "Database" sub-property, but it has a "sid" sub-property, which amounts to a similar meaning. A Sqlite 3 database is a file database, which differs from the MySQL, SQL Server and Oracle databases, that are relational databases. Therefore, the "Database Config" Property of Sqlite3 databases only has one sub-property "filepath", indicating the location of the Sqlite3 database file. The sub-properties of "Database Config" for PostgreSQL databases are slightly different from that of MYSQL databases. But currently only rational automations are supported.

The Property "Output to" specifies which variable to assign the created database object to. Here, we create a variable objDatabase , and we will perform all subsequent database operations on this object.

Now that we have created the database object, we can operate on the database. Laiye RPA supports two database operations: data query and data modifying. To query data, we can use the "Query and Return One" and "Query and Return All" Commands. To modify the database, we can use the "Execute Statement" and "Batch Execution of SQL Statements" Commands.

Let's take a look at the "Query and Return One" Command. This Command executes an SQL query statement and returns the first query result. Insert an "Query and Return One" Command. It has three Properties. For the Property "Database Object", supply our newly created database object objDatabase. For the Property "SQL Statement", write the SQL query statement to execute. Here, we write "select * from table1" , which selects all data from the table table1 and returns the first result. The Property "Output to" indicates a variable to assign the query result to. Here we supply iRet. We can use the value of iRet to determine whether the SQL query executed successfully.

Finally, remember to use "Close Connection" Command to close our database connection. The only Property of this Command is "Database Object". We supply our database object objDatabase to close our connection to that database.