Unit 3: Package Actions, Excel Automation, QR Codes, and File Operations

1. Overview of Advanced Package Actions

In Robotic Process Automation (RPA), a Package is a container for a set of related commands, actions, and libraries required to perform specific tasks. Packages make the RPA platform modular; developers only download and utilize the packages necessary for their specific automation logic.

1.1 Types of Packages

• System Packages: Built-in packages essential for basic bot operations (e.g., Loop, If/Else, Delay, Comment).
• Application-Specific Packages: Designed to interact with specific software (e.g., Excel, Outlook, SAP, Salesforce).
• Utility Packages: Provide tools for data manipulation (e.g., String, Number, DateTime, PDF).
• Custom/AI Packages: Packages created by developers or third parties (often integrated via Python or DLLs) to perform specialized tasks like Sentiment Analysis or NLP.

1.2 Integrating Package Actions in Bots

The integration process involves managing dependencies to ensure the bot runs across different environments.

• Version Control: RPA tools allow developers to select specific versions of a package. Using "Default" ensures the latest version is used, but pinning a specific version ensures stability if an update changes functionality.
• The Canvas/Flow: Actions are integrated by dragging them from the package palette onto the bot flow/canvas.
• Properties Configuration: Every action requires configuration via the properties panel (e.g., specifying file paths, variable mapping, or timeout sessions).

2. Excel Automation

Excel is the most frequently automated application in business processes. RPA tools typically offer distinct packages for Excel to handle different scenarios: Excel Basic and Excel Advanced.

2.1 Excel Basic vs. Excel Advanced

2.2 Basic and Advanced Actions: Reading and Writing Data

Both packages generally rely on the concept of a Session. A session acts as a bridge between the bot and the specific Excel workbook.

Common Actions:

1. Open:
   • Initializes the session.
   • Input: File path (local or shared drive).
   • Option: Open in "Read-only" or "Read-write" mode.
2. Get Cells (Reading):
   • Get Single Cell: Reads a value from a specific address (e.g., "A2").
   • Get Multiple Cells: Reads a range (e.g., "A2:D10").
   • Get All Cells: Reads the entire used range of the active sheet.
   • Output: The data is usually stored in a Table or DataTable variable.
3. Set Cells (Writing):
   • Set Cell: Writes a specific value or variable content into a cell address.
   • Set Cell to Formula: Inputs an Excel formula (e.g., =SUM(A1:A5)).
4. Save/Close:
   • Crucial for memory management. Failing to close sessions can leave "ghost" Excel processes running in the background.

2.3 Data Manipulation Techniques

Once data is read from Excel into a DataTable variable, the bot processes it within memory rather than interacting with the Excel UI repeatedly.

• Looping: A Loop action iterates through each row of the DataTable.
  • Example: Loop for each row in Table variable 'vExcelData' -> Assign current row to Record variable -> Process data.
• Filtering: Bots can filter data tables to process only rows meeting specific criteria (e.g., Status = "Pending").
• Joining Tables: Merging two DataTables based on a common key (similar to SQL JOIN or VLOOKUP).
• Column Operations: Adding new columns for calculation results or deleting unnecessary columns before writing back to Excel.

2.4 Excel Advanced: Formulas, Functions, and Macros

The Advanced package is necessary for complex financial or reporting logic.

• Run Excel Macro: Executes VBA code pre-existing in the workbook.
  • Input: Macro name and arguments.
• Excel Formulas:
  • Bots can write dynamic formulas into cells.
  • Auto-Fill: After writing a formula in the top cell, the bot can use keystrokes or specific actions to apply the formula down the column.
• Formatting: Changing cell colors, fonts, or borders based on logic (e.g., highlight row Red if Balance < 0).

2.5 Handling Large Data Sets

Working with massive Excel files (50k+ rows) requires optimization to prevent bot failure or timeouts.

1. Avoid UI Interaction: Do not use "Go to Cell" or "Select Row" loops inside Excel Advanced.
2. Database Method: Treat the Excel file as a database using OLEDB drivers. Use SQL queries (SELECT * FROM [Sheet1$]) to extract data instantly. This is significantly faster than standard Excel read actions.
3. Bulk Write: Instead of writing cell-by-cell inside a loop, manipulate the data in a DataTable variable, then use a "Write from Data Table" action to paste the entire dataset into Excel in one operation.

3. Working with QR Codes

Quick Response (QR) codes are 2-dimensional barcodes widely used in logistics, inventory, and document processing. RPA handles QR codes through image recognition packages.

3.1 QR Code Generation

Bots can create QR codes to tag documents or generate digital receipts.

• Input: Text string, URL, or data payload.
• Configuration:
  • Encoding: Selecting the character set (usually UTF-8).
  • Size/Scale: Defining the pixel dimensions of the output image.
  • Correction Level: Setting error correction (allows the code to be read even if partially damaged).
• Output: The bot saves the generated QR code as an image file (PNG/JPG) or places it onto a clipboard to be pasted into a document.

3.2 QR Code Scanning (Reading)

Bots read QR codes from scanned invoices, shipping labels, or screens.

• Source:
  • Static File: Reading an image file stored locally.
  • Screen/Window: Capturing a specific area of the active screen.
• Process:
  1. Bot loads the image.
  2. The recognition engine analyzes the visual pattern.
  3. Output: The extracted information is stored in a string variable.
• Use Case Example: An accounts payable bot opens a PDF invoice, takes a snapshot of the QR code in the header, extracts the Purchase Order number, and enters it into the ERP system.

4. File and Folder Operations

File and folder automation is critical for housekeeping, triggering bots, and managing input/output data.

4.1 Managing Folders

Before processing files, bots often need to prepare the environment.

• Create Folder: Creates a directory. Useful for organizing outputs (e.g., creating a folder named with the current timestamp: Reports_2023-10-27).
• Check if Folder Exists: A validation step to prevent errors before attempting to copy or move files into a directory.
• Delete/Rename Folder: utilized for cleanup actions after a process is complete.

4.2 File Operations

The core CRUD (Create, Read, Update, Delete) operations for file management.

1. Copy/Move:
   • Moving processed input files from an "Input" folder to a "Success" or "Failed" folder.
   • Requires Source Path and Destination Path.
2. Delete: Removing temporary files downloaded during the automation.
3. Rename: Renaming files to adhere to naming conventions (e.g., invoice.pdf to Invoice_Processed_ID123.pdf).
4. Zip/Unzip: Compressing multiple files before emailing them, or extracting downloaded datasets.
5. Print: Sending a file to the default printer driver (often used to "print to PDF").

4.3 Iterating Through Files

To process multiple files in a directory, bots use a Loop Action.

• Loop Iterator: For each file in folder.
• Variables:
  • : The file name.
  • : The file type (e.g., .xlsx, .pdf).
  • : The full directory path.
• Logic Flow Example:
  
  TEXT

      Start Loop (Target Folder: C:\Invoices)
          If $Extension$ == "pdf" THEN
              Call PDF Processing Sub-task
              Move file to C:\Invoices\Processed
          Else
              Log "Skipped file: $Name$"
          End If
      End Loop
      

4.4 System Variables in File Operations

To make bots portable (able to run on different machines), hardcoded paths should be avoided.

• User Home: References C:\Users\{Username}.
• Application Path: References the bot agent's installation directory.
• Date/Time: Used to append timestamps to filenames to ensure uniqueness.