I.e. I have an excel from that using a Column(RRN) if any duplicate values found I want to mark that duplicate values in light red colour.
If the column contains unique value then I don’t want to do anything(it should be white colour as original).
Sub testmacro()
'declare variables
Dim ws As Worksheet
Dim ColorRng As Range
Dim ColorCell As Range
Set ws = Worksheets("Sheet1")
Set ColorRng = ws.Range("C2:C13")
'highlight cells that contain duplicate values in the selected range
For Each ColorCell In ColorRng
If WorksheetFunction.CountIf(ColorRng, ColorCell.Value) > 1 Then
ColorCell.Interior.Color = RGB(255, 127, 127)
Else
ColorCell.Interior.ColorIndex = xlNone
End If
Next
End Sub
I did but I am not getting how to use the VBA code, I Saved in a text file trying to execute in Invoke VBA activity but I am not getting the required output.
Macro will be fast. Still if you face issues, you can try this workaround
You can follow the below steps:
Step1
Read the Excel using ReadRange
Step2
Add a column Duplicate which will act as a flag later to set the color in the excel
Step3
Use Assign activity and the below query to fetch the duplicate values.
USE A VARIABLE of type System.collections.Generic.IEnumerable to assign the values retrieved using below LINQ:
I have assumed the column datatype as Double,you can modify based on your column datatype.
Step 4
Then loop through the duplicates enumerable and set the value to Duplicate column in datatable dtData to 1 in the datatable using LINQ
Step 5
Filter the datatable dtData and output it to another dtFilter where Column Duplicate=1
Step 6
Inside an Excel App scope ,
loop through the filtered datatable and get the row index of the filtered row from initial datatable
Add 1 to it,as first row in excel will be header
intIndex=dtData.Rows.IndexOf(CurrentRow)+1
Use Set Range color activity to color the cell/row
Hope this helps.
If this gets resolved, please close the thread by marking it as solution. So that it will help others who are facing similar solution.
Thank you.
Hi ,
I have attached 2 screenshots to resolve the error…
The first variable to retrieve duplicates is System.Collections.Generic.IEnumerable
See the variable type and properties window
Use For each row in datatable to update column to 1
And use Invoke code to update column to 1 for duplicate records
Sharing the xaml developed using the DuplicateExcel.I have deleted the Duplicate column from excel
Any modifications if u wish,try to explore and implement
You can optimize it yourself.
Do you want live notifications when people reply to your posts? Enable Notifications
What makes it shine though is the fact that you can now install the Robot in Service Mode and therefore fully utilize the Unattended Robot on a free license. For that, follow the steps below:
Update
With this installer you will get a brand new update mechanism that will help you stay on the latest release. Although we know all of you are waiting for this, we decided to gradually release this content in the next couple of weeks.
Some of you may get multiple updates in the next period of time as we get our new update mechanism released. We really appreciate your patience with this and hope it’s not a big interruption of your RPA flow. We promise that it is for a better auto-update mechanism of the Robot from the Orchestrator.
Known issue: As we are slowly introducing a new method of auto-updating the robots, you will see in Orchestrator under User Sessions your version as being non-compliant. This does not affect functionality in any way and the message will disappear after we release the 21.10 version and you’ll get updated.
All the testing capabilities and C# projects are now available in Studio profile. The Studio Pro profile and capabilities have been merged into Studio. All your existing projects can be opened and executed in Studio starting with 21.8. No extra steps or configuration is needed if you previously had Studio or Studio Pro.
Cross-platform Projects
Our .NET 5 migration comes with the ability to execute automations on various operating systems. We are introducing a new compatibility option when you start a new project that will filter cross-platform activities.
We are pleased to introduce source control management using Git for StudioX. The capability should make source control management easy to use by taking away requirements to understand Git concepts or commands. Instead, you can view a repository just like a folder in Explorer - you can create projects, each within its own folder and save them there.
Once this feature is enabled, you should be able to configure the repositories you have access to:
When you create a new project, you will have the option to save it to your choice of Git repository:
You can also continue work from an existing project using Open from Source Repository:
When closing or publishing your project, you will be able to save your changes to the repository and in the background it will commit them to the source repository:
Preventing Production Runs from StudioX
To avoid production runs of projects from StudioX, we’ve introduced a new governance setting that will monitor automations ran for production purposes. When enabled, you will be notified that you must publish your project to Orchestrator and run your automation from Assistant.
You will be able to configure this setting in the Automation Ops policy by configuring the number of runs allowed as well customizing the user message. For more details on Governance, please check our guide here.
Save Project As
This feature enables you to save a copy of your project as a new project, allowing you to create a completely new Task leveraging existing code.
Additionally, the Save as functionality will allow you to convert a task into a template.
For Each Email activity has been enhanced with “Include subfolders” option which allows the user to specify whether to expand the search to include all subfolders of the selected mail folder.
Outlook Desktop in Picture-in-picture (PiP): now a workflow containing Outlook Desktop activities can run in a PiP session even if Outlook application is opened in the Main session.
When installing UiPath.MicrosoftOffice365.Activities 1.10.0-preview in StudioX, a new set of activities for OneDrive and SharePoint files and folders is available for citizen developers. These activities should be used within Use OneDrive & SharePoint card activity, where you select the account to use for OneDrive and SharePoint automation.
In Get File/Folder activity you can select a file of folder to use in other activities. We’ve implemented a “Select remote file or folder” browser to pick an item from the cloud locations accessible to the user account specified in Use OneDrive & SharePoint card. There are three possible cloud locations:
OneDrive: My Files and Shared with me
SharePoint: All SharePoint sites followed by the user
Groups: All SharePoint document libraries associated with the Microsoft 365 groups the user belongs to.
In order to use these, you’ll need to update your Excel package to the latest version
Excel Process Scope: A scope activity that behaves similar to SequenceX, loading the project settings and applying them to the associated Excel process. Additional settings:
Process mode: control whether it creates a new process, reuses an existing process if available, or behaves like StudioX for attended users.
File Conflict Resolution: determines the behavior in a way that supports both unattended and attended automation
Existing Excel Processes: Offer capability to manage existing processes on the machine (Same as project, Force kill, No action)
Assistant
Processes available in Start Menu
All available processes from Assistant can be added as shortcuts inside the Start Menu. In the General Settings page, you can find a toggle for “Show in Windows Start Menu”. The default value is On and when the toggle is Off, the processes will be removed from the Start Menu.
Processes grouped by folders
With this new feature you will be able to change the way of showing the processes list in the Assistant home tab. In the General Settings page, you can find a toggle called “Group by folder”. By default, the folder hierarchy is Off and when the toggle is On, the processes list will be grouped by folders. In both cases, the sections will be collapsible.
Until today, automating multiple apps simultaneously can sometimes be cumbersome, as it requires careful nesting of Use application/browser cards, and placing UI activities in the correct card. Starting today, no more! You can just nest Use application/browser cards however you want, and inside them, configure each UI activity to reference whichever card you need.
Excel cards as App cards
Starting with 21.8, StudioX’s Use Excel File is promoted to act as an Use app card. Meaning, now you can add a single Use Excel card and inside it, place both specific Excel activities, as well as any UI automation activities, like Click, Type, and so on, and they will work just like inside an Use app/browser . This will greatly increase the flexibility of automating Excel with a mix of dedicated Excel activities + UI automation activities, to achieve any and all Excel functionality.
Get Attribute
This Classic activity has been revamped and upgraded to fit in the Modern package. It serves the same purpose of extracting the value of certain attributes, but it’s been enhanced with better explanations about what each attribute is for, and a live view of the current values of the attributes.
Mouse Scroll
A highly requested feature, this scroll activity is smart! It allows the basic row-based scrolling, as well as To-element scrolling, for those situations when you need to scroll to a specific object on the page. On top of that, it supports “endless” pages, as a lot of modern websites are built this way.
Edit Table Extraction
The table extraction wizard can now be restarted to resume work from where it was left. The already added data is highlighted so you can remove it, add new data or configure the columns properties.
Type Into - Automatically set the correct input method
When building UI automations when building UI automations, setting the correct input method is paramount, as it can greatly increase reliability and speed. Unfortunately, picking the correct one is a bit of a black art, as there is no hard rule to follow, so some trial & error is involved. This new improvement automates this process, performing all the trial & error for you, with the click of a button, located right next to the input mode. Enjoy!
SAP Automation
SAP WinGUI automation improvements
Click with hardware events will perform scrolling to the right table cell independent on the cell position (“in view” / “out of view”)
Click with hardware events do not scroll the table anymore if the cell is “in view” to reduce the execution time
Click with hardware events works now also on SAP ALV Tables
Default selector for column headers in SAP Tables is using ColTooltip instead of tableCol to provide consistency across the whole table identification approach.
SAP FIORI automation improvements
Backwards compatibility improvement: Auto-scrolling for tables is now supported even for SAP Fiori UI versions older than 1.78. All available browsers such as IE, Firefox, Chrome and Edge are supported.
SAP FIORI UI5 Attributes are now fully visible and available in Modern UI Activities.
Mail package brings a new activity Outlook Mail Messages Trigger which allows you to set up a trigger to watch for incoming/outgoing mail messages that match a filter criteria.
Get IMAP Mail Messages can work with mail providers like 163.com. When the new parameters ClientName and ClientVersion are set, the activity identifies the client implementation to the server.
There are some bug fixes as well:
Fixed an issue in Save Attachments that caused the activity to fail with “length cannot be less than 0” for some emails.
An issue has been fixed in Save Mail Message activity. Now the activity creates the missing folder structure.
Fixed an issue in Exchange activities in case of O365 Interactive authentication that prompted the user to provide the credentials at each run.
An issue has been fixed for Send Exchange Mail Message when SaveCopy option was checked that caused an email with attachments to not appear in the shared mailbox’s Sent Items folder.
IT Automation
UiPath.AmazonWebServices.Activities2.0.0 brings a new set of activities to help automate AWS IAM (Identity & Access Management) operations. Examples of operations that can be automated using these new activities:
Users: create/update/delete users, change user password.
Policies: create/update/delete inline and managed policies, add/remove policies to/from IAM identities (users, groups, roles).
For Each Object in Bucket and Download Object to Fileactivities have been updated with new parameters to make it easier to deal with folders within a bucket.
Document Understanding
It is finally out! We have worked on redesigning the Taxonomy Manager, migrating it from Angular 1 to Angular 11! Experience a new design that allows you to manage document types and fields, organized by categories and groups - and on top, define custom colors & hotkeys for fields, so that working with them in the Validation and Classification Station is easier than ever!
Besides these, we have also worked on enhancements of the digitization algorithm, as well as fixed some bugs here and there.
Starting with this release, you will see the new client apps (Robot, Studio, Assistant) Auto-update feature in Orchestrator. You can read more about it and about couple of other cool features we’ve released here.
Task Capture
Import MS Visio Diagram
Do you already have a created process map using MS Visio and want to continue documenting the process in Task Capture? Easily open MS Visio files in Task Capture instead of creating the diagram from scratch!
Leverage the updated recorder
We’ve changed the recorder component to improve the quality of selectors Task Capture generates for Studio output. It’s already set up as a default recorder, so just capture your process, export it to Studio in XAML format and check out results!
Want to find out more? Please visit the separate post dedicated to Task Capture 21.8 updates here:
I.e. I have an excel from that using a Column(RRN) if any duplicate values found I want to mark that duplicate values in light red colour.
If the column contains unique value then I don’t want to do anything(it should be white colour as original).
Sub testmacro()
'declare variables
Dim ws As Worksheet
Dim ColorRng As Range
Dim ColorCell As Range
Set ws = Worksheets("Sheet1")
Set ColorRng = ws.Range("C2:C13")
'highlight cells that contain duplicate values in the selected range
For Each ColorCell In ColorRng
If WorksheetFunction.CountIf(ColorRng, ColorCell.Value) > 1 Then
ColorCell.Interior.Color = RGB(255, 127, 127)
Else
ColorCell.Interior.ColorIndex = xlNone
End If
Next
End Sub
I did but I am not getting how to use the VBA code, I Saved in a text file trying to execute in Invoke VBA activity but I am not getting the required output.
Macro will be fast. Still if you face issues, you can try this workaround
You can follow the below steps:
Step1
Read the Excel using ReadRange
Step2
Add a column Duplicate which will act as a flag later to set the color in the excel
Step3
Use Assign activity and the below query to fetch the duplicate values.
USE A VARIABLE of type System.collections.Generic.IEnumerable to assign the values retrieved using below LINQ:
I have assumed the column datatype as Double,you can modify based on your column datatype.
Step 4
Then loop through the duplicates enumerable and set the value to Duplicate column in datatable dtData to 1 in the datatable using LINQ
Step 5
Filter the datatable dtData and output it to another dtFilter where Column Duplicate=1
Step 6
Inside an Excel App scope ,
loop through the filtered datatable and get the row index of the filtered row from initial datatable
Add 1 to it,as first row in excel will be header
intIndex=dtData.Rows.IndexOf(CurrentRow)+1
Use Set Range color activity to color the cell/row
Hope this helps.
If this gets resolved, please close the thread by marking it as solution. So that it will help others who are facing similar solution.
Thank you. DuplicateValues.xaml (17.7 KB)
Thank you for help the method which you sent is working fine but its highlighting the entire rows(Which contains duplicate values) but I need to highlight only "RRN column which contains duplicate values can you please help me to do so.