studiox

2021.10

false

OUT OF SUPPORT

StudioX User Guide

Last updated Nov 18, 2024

Excel Automation

About Excel Automation

StudioX offers a deep integration with Microsoft Excel and ships with a number of activities that automate the tasks you perform in Excel. For information on installing the Excel add-in, see the Studio guide.

You start by adding the Excel file you want to automate as a resource to the automation project, and then add the activities to perform in the file. When configuring the activities that use the file, you can select sheets, tables, named ranges, and named cells directly from the Plus menu in StudioX. You can also seamlessly switch from StudioX to Excel to indicate the data to use directly from the file.

If you click Indicate in Excel in the Plus menu when configuring an activity to select data directly from the file, your spreadsheet is opened in Excel and a UiPath tab appears in the Excel ribbon. Select the data you want to use, for example a cell or a range of cells, and then click Confirm in the UiPath tab to confirm the selection.

Note: If you are using Microsoft Excel 2010, do not indicate entire columns in the Excel file (for example, B:G), always indicate finite ranges (for example, B1:G100). Due to limitations in older Excel versions, indicating entire columns may result in an error when the project is executed.

You can also manually indicate the Excel data to use by selecting the Custom Input option in the Plus menu. This option enables you to enter cell and range references, table names, or sheet names, which can be useful when you are working with Excel files with dynamic names, or with files that don't exist at design time and no template file is available.

If you want to repeat the same activities for each row in a range, add a For Each Excel Row activity, and then add the activities to repeat inside it.

To learn how to automate Excel tasks, see the following tutorials:

The following Excel activities are available in StudioX:

Note: There are a couple of different ways of launching and previewing Excel files, both at runtime and at design time. To learn more, see the Project settings.

Adding an Excel File to an Automation

There are two ways in which you can add an Excel file as a resource to an automation. The main difference between them is which activities in the project can access and modify the data in the Excel file:

Add a Use Excel File resource activity. Using this option, the data in the file can only be extracted or modified by the activities added inside the resource activity. After all the activities inside Use Excel File are executed, the file is closed. This is useful when the file will be created at runtime (e.g. downloaded or saved from an email attachment) or if the file needs to sent to another location (e.g. uploaded or sent as an email attachment).
Connect the project to the Excel file. Using this option, the file is added as a global resource, which means that it can be accessed and modified by all the activities in the project. The connection to the file is closed only after the execution of the automation is completed. This is intended for cases where the file name and location won't change and the file doesn't need to to be used for other purposes such as uploading or sending as an email attachment.

Connecting a Project to an Excel File

To connect to an Excel file and add it as a global resource:

Click Connect to Resource at the top of the Data Manager panel, and then select Connect to a new Excel file.
In the Manage Resource window, provide the following information, and then click OK:
- Select Excel file - Click Browse next to the field, and then browse to and select the Excel file to add to the automation.
- And give it a name for later - Enter a name by which to refer to the file in the automation project.
- Save changes - Select this option if you want the file to be saved at the end of the automation. Otherwise, the file is returned to the initial state after project execution. This option is selected by default.
  
  When the option to save changes is disabled, the message save off is displayed next to the Excel resource entries in the Plus menu and in the Data Manager panel.
- Read only - Select this option if you want to open the file in read-only mode. This allows you to perform data extraction operations if the file is locked for editing or has an edit password. This option is not selected by default.

Managing Excel Resources

You can manage Excel resources from the Data Manager panel, regardless of the way in which they were added to the automation. Right-click an Excel resource and select one of the options in the context menu:

Open Excel file - Opens the file in Excel.
Open Excel file location - Opens the folder containing the Excel file.
Edit - Opens the Manage Resource window where you can edit the resource properties.
Delete - Removes the resource from the project.

About the Project Notebook

The Project Notebook is an Excel workbook that you can use as a scratchpad where to keep formulas and manipulate data. Its purpose is to be used for calculations and temporary data storage during project execution. By default, changes made to the Project Notebook are not saved when the execution of the automation is completed.

The default Project Notebook is a file named Project_Notebook.xlsx that is created by StudioX in the project folder. The file comes with a predefined set of formulas that you can use in any automation project. The formulas are split on several sheets based on the area they apply to. Each sheet contains cells where you can add input data and formulas that manipulate that data. The formulas are in easy to identify named cells that you can select for use in your automations directly from the Plus menu.

Configuring the Project Notebook

Select Notebook > Configure Notebook in the StudioX ribbon. You can configure the following settings:

Notebook file - Select another Excel file to use as the Project Notebook.
And give it a name for later - Enter a name by which to refer to the Excel file in the project when an activity interacts with it. The default name is Notes.
Access password - If applicable, enter the password required to open the file.
Editing password - If applicable, enter the password required to edit the file.
Autosave file - Select this option if you want the file to be saved at the end of the automation. Otherwise, the file is returned to the initial state after project execution. This option is not selected by default.

When the option to save changes is disabled, the message Save changes: off is displayed next to the Project Notebook entries in the Plus menu and in the Data Manager panel.
Read only - Select this option if you want to open the file in read-only mode. This allows you to perform data extraction operations if the file is locked for editing or has an edit password. This option is not selected by default.

Project Notebook Formulas

The formulas available in the Project Notebook are detailed below. To learn how you can add formulas to the Project Notebook, see Tutorial: Adding Your Own Formulas to the Project Notebook.

Date

Enter a base date (by default today's date) in cell B4 (Date_Input) and a number of days (by default 7) in cell B6 (Days) to be added to the date in cell B4 later in the sheet. The following formulas are available:

Date plus a number of days (DatePlusDays) - returns the date that results from adding the number of days in cell B6 to the input date in cell B4.
Date plus a number of working days (DatePlusWorkingDays) - returns the date that results from adding the number of days in cell B6 to the input date in cell B4 not including weekends.
Date Format (YYYYMMDD) - returns the input date from cell B4 converted to a text with the format YYYYMMDD.
Today - returns today's date.
Last week's dates (Monday, Friday, Sunday) - returns the date for last week's Monday (LastWeekMonday), Friday (LastWeekFriday), and Sunday (LastWeekSunday).
Last month's dates (First and Last) - returns the dates of last month's first day (LastMonthStartDate) and last day (LastMonthEndDate).
First / Last business day this month - returns the dates of the first workday (ThisMonthFirstWorkingDay) and last workday (ThisMonthLastWorkingDay) from the current month.

Convert Text to a Date in a Locale-Independent Way

Enter a date in cell B18 (DateText) that contains Japanese characters for year, month, and day inserted after the numerals. The date in converted to the MM/DD/YYYY format and returned in cell B29 (ReformattedDate).

For an example of how you can use the formulas in the Date sheet, see Tutorial: Working with Files and Folders.

Text

Enter a text in cell B4 (Text_Input). To search for a specific text inside cell B4, enter the text to search for in cell B10 (Search). To replace the searched text with another text, enter the replacement text in cell B11 (Replace). The following formulas are available:

Trimmed - returns the text from cell B4 with any extra spaces removed.
Length- returns the number of characters in the text from cell B4.
Upper case- returns the text from cell B4 with all the characters converted to upper case.
Lower case - returns the text from cell B4 with all the characters converted to lower case.
Result - returns the text in cell B4 modified by replacing the text added in cell B10 with the text added in cell B11.
Contains - checks whether the text added in cell B10 appears in the text from cell B4 and returns TRUE if the text is found or FALSE if the text is not found.
First Name - returns the text before the last space character in the text from cell B4.
Last Name - returns the text after the last space character in the text from cell B4.

Extract Values from a Text

To extract a specific value from the text added in cell B4 to cell F5, add text to the left of the value in cell D5 and/or the text to the right of the value in cell E5. To extract multiple values, use the cells in the same columns on rows 6-9. You can use this in automations to extract variable values from blocks of text that follow the same template. For example, you can extract values such as invoice numbers and amounts from automated email notifications where they are preceded by the same labels.

For an example of how you can use the formulas in the Text sheet, see Tutorial: Extracting Data from Automated Emails and Moving It to a Desktop Application.

Number

Enter a number in cell B4 (Number_Input). The following formulas are available to manipulate the number:

Cleaned Up (CleanNumber) - returns the number added in cell B4 with all line breaks and non-breaking spaces removed.
Int - returns the integer part of the number from cell B4.
2 decimals (TwoDecimals) - returns the number added in cell B4 with 2 decimal places.

Convert Text to a Number in a Locale-Independent Way

Enter a number that has no group separator or a point (.) as group separator, and has a comma (,) as decimal separator in cell B11 (NumberText_Input). The number is returned in cell B15 (ReformattedNumber) converted to use comma (,) as group separator and point (.) as decimal separator.

File

Enter the full path to a file including the file extension in cell B6 (FullFileName_Input). The following formulas are available:

File name (FileName) - returns the file name including extension from the file path added in cell B6.
File extension (FileExtension) - returns the file extension from the file path added in cell B6.
File name no extension (FileNameNoExtension) - returns the file name without extension from the file path added in cell B6.
Folder (Folder) - returns the path to the folder that contains the file whose path was added in cell B6.
Reformatted File Name (ReformattedFileName) - returns the values in cells B9 and B10 separated by . (point). The result is the file name with extension.

For an example of how you can use the formulas in the File sheet, see Tutorial: Saving, Renaming, and Moving Attachments.

Tutorial: Comparing Excel Files and Emailing Reconciliation Errors

In this tutorial, we will create an automation to compare the amounts in a spreadsheet that contains invoices with the amounts from a CSV file that contains a summary per supplier. If any reconciliation errors exist between the files, we will create a CSV file with details and send the file by email.

We will create a project with the following activities:

Two Use Excel File activities to indicate the Excel file with the invoices and the file where we want to reconcile the data.
A series of Write Cell activities to add two column headers to the reconciliation Excel file and to calculate the sum of invoices for one supplier and the difference between that sum and the amount in the initial summary. We will add a Copy Range activity to copy the formulas we added for one supplier to all the rows in the sheet so that we can perform the same calculations for all the suppliers, and then add one more Write Cell activity to add the total difference to a cell.
An If activity where we will add a condition that if there are reconciliation errors (the sum of differences is greater than 0), a CSV file with details should be created and emailed to a specified address. For the email tasks, inside the If activity we will add a Use Desktop Outlook App activity to indicate the Outlook account we want to send the mail from and a Send Email activity inside Use Desktop Outlook App.

Step 1: Set up the project and get the necessary files.
1. Create a new blank project using the default settings .
2. Download and extract the archive with the automation project in this tutorial using the button at the bottom of this page. Copy the folder data to your project folder.
Step 2: Add the Excel files to the project.
1. Click Add activity in the Designer panel, and then find the Use Excel File activity in the search box at the top of the screen and select it. A Use Excel File activity is added to the Designer panel.
2. In the activity:
  - Click Browse next to the Excel file field, and then browse to and select the file invoices.xlsx
  - In the field Reference as, enter Invoices.
    
    You have indicated that you will work on the file invoices.xlsx that is known in your automation as Invoices.
3. Click Add activity inside the Use Excel File activity, and then add a another Use Excel File activity.
4. In the second activity:
  - Click Browse next to the Excel file field, and then browse to and select the file reconcile.xlsx
  - In the field Reference as, enter Rec.
    
    You have indicated that you will work on the file reconcile.xlsx that is known in your automation as Rec.
Step 3: Copy the data to the reconciliation file.
1. Click Add activity inside the second Use Excel File, and then find the Read CSV activity in the search box at the top of the screen and select it. The activity is added inside the Use Excel File activity.
2. In the Read CSV activity:
  - Click Browse next to the Read from file field, and then browse to and select the file summary.csv.
  - Click Plus on the right side of the Output to field, and then select Rec > Summary [Sheet]. The field is updated with your selection [Rec] Invoices.
    
    You have indicated that you want to copy the data from the CSV file summary.csv to the file Rec in the sheet Summary.
3. Click Add activity inside the second Use Excel File below the Read CSV activity, and then find the Copy Range activity in the search box at the top of the screen and select it. A Copy Range activity is added inside the Use Excel File activity.
4. In the Copy Range activity:
  - Click Plus on the right side of the Source range field, and then select Invoices > Invoices [Sheet]. Your selection is displayed in the field as [Invoices] Invoices.
    
    You have indicated that you want to copy the Invoices sheet from the file Invoices.
  - Click Plus on the right side of the Destination range field, and then select Rec > Invoices [Sheet]. Your selection is displayed in the field as [Rec] Invoices.
    
    You have indicated that you want to paste the copied range to the Invoices sheet in the file Rec.
Step 4: Make the necessary calculations to compare the data.
1. In the Activities panel, select the Excel tab, and then drag the Write Cell activity and drop it in the second Use Excel File below Copy Range. A Write Cell activity is added to the Designer panel.
2. In the Write Cell activity:
  - Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text Calculated Amount.
  - Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell C1, and then click Confirm. The field is updated with your selection [Rec] Summary!C1.
    
    You have indicated that you want the first cell in column C from the Summary sheet of the Rec file to contain the text Calculated Amount.
3. Add a second Write cell activity below the previous one and, in the activity::
  - Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text =IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), "").
  - Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell C2, and then click Confirm. The field is updated with your selection [Rec] Summary!C2.
    
    You have indicated that you want C2 from the Summary sheet of the Rec file to contain the formula =IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), ""). This formula checks if the supplier cell (A2) is not empty and sums the values in the C column of the Invoices sheet for the supplier in the cell A2 of the Summary sheet.
4. Add a third Write cell activity below the previous one and, in the activity::
  - Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text Difference.
  - Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell D1, and then click Confirm. The field is updated with your selection [Rec] Summary!D1.
    
    You have indicated that you want the first cell in column D from the Summary sheet of the Rec file to contain the text Difference.
5. Add a fourth Write cell activity below the previous one and, in the activity::
  - Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text =IF(A2 <> "", C2-B2, "").
  - Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell D2, and then click Confirm. The field is updated with your selection [Rec] Summary!D2.
    
    You have indicated that you want D2 from the Summary sheet of the Rec file to contain the formula =IF(A2 <> "", C2-B2, ""). This formula checks if the supplier cell (A2) is not empty and calculates the difference between the values in cells C2 and B2 of the Summary sheet.
6. Click Add activity below the last Write Cell activity, and then find the Copy Range activity in the search box at the top of the screen and select it. A Copy Range activity is added to the Designer panel.
7. In the Copy Range activity:
  - Click Plus on the right side of the Source range field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the range C2:D2, and then click Confirm. The field is updated with your selection [Rec] Summary!C2:D2.
  - Click Plus on the right side of the Destination range field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the range C3-D20, and then click Confirm. The field is updated with your selection [Rec] Summary!C3-D20.
  - From the What to copy drop-down menu, select Formulas
    
    You have indicated that you want to paste the formula in the copied range C2:D2 down to the same columns in the sheet.
8. In the Activities panel, select the Excel tab, and then drag the Write Cell activity and drop it below the last Copy Range activity. A Write Cell activity is added to the Designer panel.
9. In the Write Cell activity:
  - Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the formula =SUM(Summary!D:D).
  - Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the sheet Invoices, then the cell F1, and then click Confirm. The field is updated with your selection [Rec] Invoices!F1.
    
    You have indicated that you want the first cell in column F from the Invoices sheet of the Rec file to contain the formula =SUM(Summary!D:D). The formula adds the sum of the differences in the D column of the Summary sheet to the F1 cell in the Invoices sheet.
Step 5: Send an email with details about the reconciliation if the sum of differences is greater than 0.
1. Click Add activity below the last Write Cell activity, and then, in the search box at the top of the screen, locate and select If. An If activity is added to the Designer panel.
2. In the If activity, click Plus on the right side of the Condition field, and then select Condition Builder.
3. In the Condition Builder, add the condition "if the sum of differences is greater than 0":
  - Click Plus on the right side of the field on the left, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the sheet Invoices, then the cell F1, and then click Confirm. The field is updated with your selection [Rec] Invoices!F1.
  - From the drop-down menu in the middle, select greater than.
  - Click Plus on the right side of the field on the right, select Number, enter 0 and click Save twice.
    
    The activities you add in the Then branch of the If activity will be executed if this condition is met.
4. In the Activities panel, select the CSV tab, and then drag the Write CSV activity and drop it in the Then branch of the If activity. A Write CSV activity is added to the Designer panel.
5. In the Write CSV activity:
  - Click Plus on the right side of the Write to what file field, and then select Text. In the Text Builder, enter reconcile , and then from the Plus menu on the right side of the Text Builder select Project Notebook (Notes) > Date [Sheet] > YYYYMMDD [Cell]. The text in the Text Builder is updated to reconcile Excel Date!YYYYMMDD. Enter the text .csv at the end and click Save. The final text should be reconcile Excel Date!YYYYMMDD.csv.
  - Click Plus on the right side of the Write from field, and then select Rec > Summary [Sheet]. The field is updated with your selection [Rec] Summary.
    
    You have indicated that you want to create a CSV file in the project folder whose name contains the word reconcile and today's date and copy the Summary sheet of the Rec file to it.
6. In the Activities panel, select the Mail tab, and then drag the Use Desktop Outlook App activity and drop it in the Then branch of the If activity below the Write CSV activity.
7. In the activity, the default email account is already selected in the Account field. If you want to use a different account, select it from the drop-down menu.
  
  In the Reference as field, leave the default value Outlook as the name by which to refer to the account in the automation.
8. In the Activities panel, drag the Send Email activity and drop it inside the Use Desktop Outlook App activity.
9. In the Send Email activity:
  - Click Plus on the right side of the From account field, and then select Outlook.
  - Click Plus on the right side of the To field, and then select Text. In the Text Builder window, enter an email address where to send the email. For example, you can enter your own email address to send the email to yourself. If you leave the Is draft option selected, the automation does not send the email, it instead saves the email to the Outlook Drafts folder.
  - Click Plus on the right side of the Subject field, and then select Text. In the Text Builder window, enter Reconciliation errors for, and then from the Plus menu on the right side of the Text Builder select Project Notebook (Notes) > Date [Sheet] > Today [Cell]. The text in the Text Builder is updated to Reconciliation errors for Date!Today.
  - Click Plus on the right side of the Body field, and then select Text. In the Text Builder window, enter text for the body of the email, for example One or more suppliers have reconciliation errors. See attached CSV file for details..
  - For Attachments, select Files, and then enter the same file name as the one used in the Write CSV activity in step 5, reconcile Excel Date!YYYYMMDD.csv. For example, you can open the Text Builder for the Write to what file field of the Write CSV activity, copy all the text, and then paste in the Text Builder for the file attachment field.
10. Click Save in the StudioX ribbon to save the automation, then click Run to execute the automation.
The invoices and summary data are copied to the reconcile.xlsx file, the calculations are performed and, because the total amount in the invoices does not match the one in the summary for one of the suppliers, the Summary sheet of the reconcile.xsls file is copied to a CSV file whose name includes today's date, the file is attached to an email and sent to the specified address. In the file, you can identify the supplier with reconciliation errors by the value in cell D3 that is greater than 0.

Download example