Excel advanced package

The Excel advanced package contains actions that enable you to automate many of the repetitive tasks when working with Microsoft Excel spreadsheets.

You can automate tasks related to the workbook, worksheet, rows, columns, and cell operations. You can use these actions when Microsoft Excel is installed on the device that you want to use to automate the Microsoft Excel-related tasks.
Note:
  • The Excel file that you use from the Control Room is applicable only for the current bot session. If you modify the file during bot execution, the updated file cannot be uploaded back to the Control Room
  • You need not install the Microsoft Access Database Engine as an additional component in your system to use Excel as Database if you are using any of the following office versions:
    • Microsoft 365 Apps for Enterprise
    • Microsoft Office 2016, or 2019 (Consumer Version 2009 or later)

    However, if you have both Microsoft 365 Apps and the Microsoft Access Database Engine are installed on your system, then you can uninstall the Microsoft Access Database Engine and repair Microsoft 365 Apps.

    Microsoft recommends that you do not install the Microsoft Access Database Engine driver separately because both the Microsoft Access Database Engine 2016 and Microsoft 365 Apps use the same major version identifier (16.0). To learn more about the Microsoft Access Database Engine driver installation requirement, see Unable to use the Access ODBC or OLEDB provider

Important: When you use Microsoft Excel Version 2208 and a bot tries to open an unprotected macro, a security warning is displayed indicating that the macros are blocked as they are not from a trusted source.

See Macros from the internet will be blocked by default in Office for more details.

Note: If you use different versions of the Excel advanced package in a parent bot and a child bot, the capability to share a session across bots is currently not supported. To share a session across parent and child bots, ensure that you use the same version of the package in both the parent and child bots.
Actions in the Excel advanced package support Microsoft Office 2016 through Microsoft Office 2019, Microsoft 365, and the following file formats:
  • .xls
  • .xlsx
  • .xlsb
  • .xml
  • .xlsm
  • .txt
  • .ods
  • .htm
  • .html
  • .mht
  • .mhtml
  • .slk
  • .pdf
Important:
  • For files with .ods extension, unless you specify the sheet name in the Specify sheet name field of the Open action, the action always opens the .ods file with the first sheet as active, by default. If the sheet name contains a space character, after you save and close the Excel sheet, the space character is displayed as underscore.
  • Files with the .htm, .html, .mht, and .mhtml extensions cannot be converted into PDF.
  • Files with the .slk and .pdf extensions contain only a single sheet. The sheet cannot be renamed. If you rename the sheet and save the Excel sheet, the sheet does not retain the new name.
  • Files with .htm, .html, .mht, .mhtml, .slk, and .pdf extensions cannot retain passwords.
  • You cannot run a macro in files with the .pdf extension.
  • Note: Macro-enabled Excel applications with VBA using 32-bit and 64-bit code are not supported. If you encounter a Compile error in hidden module error message when running a macro, then see the following image to add the VBA code manually in the Excel file Workaround script for 32 bit and 64 bit Excel VBA
You can also use some of the actions with the .csv format. See CSV operations in Excel advanced.

See Example of migrating data from Excel to a database.

Choosing the Excel package in Automation 360

Automation 360 includes packages to support three types of Microsoft Excel usage. For optimal results, use the package that corresponds to the type of Excel that is available on the device you are running bots on.

  • No Excel installed: If you do not have Microsoft Excel installed on the device on which you are running bots to automate Excel-related processes, use the Excel basic package.
  • Desktop Excel installed: If you have a desktop version of Microsoft Excel installed on your computer, use the Excel advanced package in your bots.
  • Online Microsoft 365 Excel only: If you are using Microsoft Excel 365 on a web browser, use the Microsoft 365 Excel package for automating tasks related to Excel.

Before you start

Perform the following actions within the Excel advanced package as part of using the set of available actions:

  1. Open the Microsoft Excel spreadsheet that you want to use to automate the Microsoft Excel-related tasks. Use the Open action to open the spreadsheet. See Using the Open action for Excel advanced.
    Note: For files containing large data sets, use actions from the Database package to automate create, read, and update operations. See Using Connect action for database.
  2. Use the different actions available in the Excel advanced package to automate the Microsoft Excel-related tasks.
  3. After you have automated all the Microsoft Excel-related tasks, close the spreadsheet using the Close action.

Actions in the Excel advanced package

The actions in the Excel advanced package enable you to perform the following operations:

Action Description
Cell operations Perform operations such as extracting data from cells, deleting values from a cell, moving a cursor to a specific cell, and finding and replacing content. See Cell operations in Excel advanced.
Row and column operations Perform operations such as inserting new rows and columns, and hiding and unhiding rows and columns. See Row and column operations in Excel advanced.
Table operations Perform operations such as sorting and filtering data in a table, inserting and deleting columns, and getting the table range. See Table operations in Excel advanced.
Workbook operations Perform operations such as opening a workbook, appending data, protecting and unprotecting a workbook, and converting a Microsoft Excel workbook to a PDF file. See Workbook operations in Excel advanced.
Worksheet operations Perform operations such as creating and deleting a worksheet, appending data, and hiding and unhiding a worksheet. See Worksheet operations in Excel advanced.
Example tasks:

More resources