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.

After you select the Excel file to work with by adding it to a Use Excel File activity, add the activities that use data from the file inside this activity. When configuring the child activities, you can select sheets, tables, named ranges, and named cells in the file directly from 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.

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

To learn how to automate Excel tasks, see the tutorials in this section.

To find out more about Excel activities, this section contains detailed descriptions for each activity.

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.

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 Project Notebook > Configure Project Notebook in the StudioX ribbon. You can configure the following settings:

• Project 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.
• Read only - Select this option if you want the process 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.