Excel VBO in Blue Prism

The Excel VBO (Visual Business Object) in Blue Prism is a powerful tool for automating tasks related to Excel files. It allows users to interact with Excel spreadsheets programmatically, enabling data extraction, manipulation, and reporting with ease.

In this guide, we will cover:

• What the Excel VBO is
• All actions available in Excel VBO
• Naming conventions
• Data manipulation techniques
• Practical examples

---

What is Excel VBO?

Excel VBO is a pre-built business object in Blue Prism designed for handling Excel files. It simplifies the process of automating Excel-related tasks, such as reading data, writing data, formatting cells, and managing workbooks.

By using Excel VBO, you can:

• Open, close, and manage Excel workbooks.
• Read and write data to Excel sheets.
• Format cells, apply filters, and create charts.
• Save or export Excel files to other formats like PDF.

Download Excel VBO

---

Key Actions in Excel VBO

Here is a breakdown of the most commonly used actions in the Excel VBO and their functionalities:

1. Create Instance:

   • Opens an instance of Excel.
   • Use this action before performing any Excel-related tasks.
   • Example:

     Input: Visible = True, Instance Handle = "Excel1"
     Output: Handle = "Excel1"

2. Open Workbook:

   • Opens a specific Excel file.
   • Example:

     Input: Handle = "Excel1", File Path = "C:\Reports\Data.xlsx"
     Output: Workbook Handle = "Workbook1"

3. Save Workbook:

   • Saves changes to the current workbook.
   • Example:

     Input: Workbook Handle = "Workbook1"
     

4. Close Workbook:

   • Closes the specified workbook.
   • Example:

     Input: Workbook Handle = "Workbook1", Save Changes = True
     

5. Read Cell:

   • Reads the value from a specific cell in a worksheet.
   • Example:

     Input: Workbook Handle = "Workbook1", Worksheet Name = "Sheet1", Cell Reference = "A1"
     Output: Value = "John Doe"

6. Write Cell:

   • Writes a value to a specific cell.
   • Example:

     Input: Workbook Handle = "Workbook1", Worksheet Name = "Sheet1", Cell Reference = "B1", Value = "12345"
     

7. Read Range:

   • Reads a range of cells and returns a collection.
   • Example:

     Input: Workbook Handle = "Workbook1", Worksheet Name = "Sheet1", Range = "A1:C10"
     Output: Collection = [Data Table]

8. Write Range:

   • Writes a collection to a range of cells.
   • Example:

     Input: Workbook Handle = "Workbook1", Worksheet Name = "Sheet1", Range = "A1", Collection = [Data Table]
     

9. Format Cell:

   • Applies formatting to a specific cell.
   • Example:

     Input: Workbook Handle = "Workbook1", Worksheet Name = "Sheet1", Cell Reference = "A1", Bold = True, Font Size = 12
     

10. Apply Filter:

    • Filters data in a worksheet based on specified criteria.
    • Example:

      Input: Workbook Handle = "Workbook1", Worksheet Name = "Sheet1", Column Name = "Status", Filter Criteria = "Completed"
      

11. Delete Worksheet:

    • Deletes a specified worksheet from the workbook.
    • Example:

      Input: Workbook Handle = "Workbook1", Worksheet Name = "Sheet2"
      

12. Save Workbook As:

    • Saves the workbook with a new name or format.
    • Example:

      Input: Workbook Handle = "Workbook1", File Path = "C:\Reports\NewData.xlsx"
      

13. Close Instance:

    • Closes the Excel application instance.
    • Example:

      Input: Handle = "Excel1"
      

---

Naming Conventions

To maintain clarity and consistency in your workflows, follow these naming conventions:

1. Descriptive Variable Names:

   • Use clear names for instance handles, workbook handles, and other variables.
   • Example: Excel_Instance, Workbook_Main, Sheet_Data.

2. Prefixing Variables:

   • Add prefixes to denote variable types.
   • Example: wb_ for workbook handles, ws_ for worksheet names, col_ for collections.

3. Camel Case:

   • Use camel case for variable names.
   • Example: filePath, cellValue, dataCollection.

4. Avoid Special Characters:

   • Use underscores instead of spaces.
   • Example: Excel_Report_2025.

---

Data Manipulation with Excel VBO

The Excel VBO enables various data manipulation techniques:

1. Filtering Data:

   • Use the Apply Filter action to filter rows based on specific criteria.
   • Example:

     Input: Workbook Handle = "Workbook1", Worksheet Name = "Sheet1", Column Name = "Age", Filter Criteria = ">30"
     

2. Sorting Data:

   • Though Excel VBO doesn't have a direct sorting action, you can achieve sorting using formulas or macros in Excel.

3. Dynamic Ranges:

   • Use the UsedRange property to identify the range of cells with data.
   • Example:

     Input: Workbook Handle = "Workbook1", Worksheet Name = "Sheet1", Range = "UsedRange"
     

4. Combining Data:

   • Use multiple Read Range actions to read data from different sheets and merge them into a single collection.

5. Conditional Formatting:

   • Use the Format Cell action to apply conditional styles to cells.

6. Writing Formulas:

   • Write Excel formulas directly into cells.
   • Example:

     Input: Workbook Handle = "Workbook1", Worksheet Name = "Sheet1", Cell Reference = "D1", Value = "=SUM(A1:A10)"
     

---

Practical Examples

1. Generating a Sales Report:

   • Actions: Open Workbook, Read Range, Apply Filter, Write Range, Save Workbook As.
   • Workflow:
     1. Open the sales data file.
     2. Filter rows where Status = Completed.
     3. Write the filtered data to a new sheet.
     4. Save the workbook with a new name.

2. Validating Data:

   • Actions: Read Cell, Write Cell, Format Cell.
   • Workflow:
     1. Read the Total column in a worksheet.
     2. If any cell is empty, write "Missing" and highlight it in red.

3. Creating a Summary Sheet:

   • Actions: Read Range, Write Range, Write Cell.
   • Workflow:
     1. Read data from multiple sheets.
     2. Calculate totals and averages.
     3. Write the summary data to a new sheet.

---

Best Practices

1. Handle Instances Properly:

   • Always close the Excel instance after completing your tasks to avoid memory leaks.

2. Use Try-Catch for Error Handling:

   • Wrap Excel actions in Try-Catch blocks to handle file-related issues gracefully.

3. Validate File Paths:

   • Check if the file exists before trying to open it.

4. Optimize Ranges:

   • Use specific ranges instead of entire sheets to improve performance.

5. Document Your Workflow:

   • Add comments to explain complex Excel manipulations.

---

Conclusion

The Excel VBO in Blue Prism simplifies Excel automation tasks, enabling efficient data handling and reporting. By understanding the available actions, following naming conventions, and applying data manipulation techniques, you can streamline your automation workflows and achieve better results. Start exploring Excel VBO today and unlock the full potential of Blue Prism in your automation projects!