[Part 1] Modern Activities - Find/Replace Value | Format Cells

[Gokul001 (Gokul Balaji)]

Hello Everyone, Hope you are doing good

I would like to present this post who are looking for the Modern Activities

Very first thing is to Enable the Modern experience (from Classic)

Go to → Activities Panel → Click on Filter → Select Show Modern.

Let Jump into the Tutorial

we are look into some Modern activities

• Find/Replace Value

• Format Cells

Note : We need to use Excel Process Scope and Use Excel File for all the above activities

Excel Process scope

Opens or reuses Excel processes, loads the Excel project settings and applies them to the associated Excel file.

Use Excel File

Lets you select an Excel file to use in the automation and enables StudioX to integrate with Excel. The data in the file is available to all the activities added inside Use Excel File. This means that when you configure child activities, you can select data from the file directly from StudioX.

Find/Replace Value

Description - Searches in a specified range for a certain value and either returns the address of the cell where it is found or replaces the value with another given value.

Step 1 : Use Excel process scope followed by Use Excel file activities.
Step 2 : Use Read range activity and Store the Output

Range : Excel.Sheet("Sheet1")
Save to : DtReadInput | Variable Type -> System.Data.Datatable

Step 3: Use LookUp Range activity (Classic) Searches in a specified range for the coordinates of a cell.

Sheet Name : "Sheet1"
Value to search : "Role in Company"
Output : Create an Output -> LookupRoleCompany | Variable Type -> String

Step 3 : Use If activity to check the column is exist or not

LookupRoleCompany.Equals(-1)

Then -> Use Log message activity -> "---> There is no Role in Company column in the input excel file"

Else -> Use Find/Replace Value activity

Operation : Replace All

Where to Search : Excel.Sheet("Sheet1").Range(LookupRoleCompany+":"+System.Text.RegularExpressions.Regex.Match(LookupRoleCompany,"\D+").ToString+DtReadInput.Rows.Count.ToString)

Note : "The Range will get dynamically"

Value to find : "Analyst"

Replace with : "RPA Developer"

Visual

Format Cells

Description - Sets the format for all the cells in a specified range. You can format the data type, alignment, font, and fill color of the cells. The activity can be used with an Excel file selected for a parent Use Excel File activity or with the Project Notebook.

Step 1 : Use Excel process scope followed by Use Excel file activities.
Step 2 : Use Read range activity and Store the Output

Range : Excel.Sheet("Sheet1")
Save to : DtReadInput | Variable Type -> System.Data.Datatable

Step 3 : Use Format Cell Activity, In this we only Format the Column Header Dynamically

Source : Excel.Sheet("Sheet1").Range("A1:"+UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(DtReadInput.Columns.Count)+"1")

Format data as Type | Set Format

Font : "Time New Roman"

Font Style : "Bold"

Font Size : "14"

Font Color : "Black"

Visual

Check out the Project File

Forum_ModernActivities.zip (91.8 KB)

Hope this will be helpful

Questions

For questions on your retrieval case open a new topic and get individual support

Feedback

Click button to share your thoughts

Regards
Gokul