Excel "Remove Duplicates" with "Send Hotkey" activity

Help
1

Hi All,

I would like to use Excel “Remove Duplicates” function to delete the duplicated records in my worksheet. In excel, I can enter the following keys (in Excel) to perform this operation :

  1. Alt + a
  2. m
  3. tab
  4. tab
  5. enter

Then, i tried to add 5 “Send Hotkey” activities in uiPath according to the list as above. However, when it run into step 2, the robot type a “M” into cell A1 instead of trigger the “Remove Duplicates” window.

Any suggestion?

2

@Benny_Chan,

Try another the way to delete duplicates records.

  1. first use Read Range activity to read the Excel file and it will give you output as data table.
    2)Required dataTable = dataTablename.DefaultView.ToTable(true, “Column1”,“Column2”,…).CopyToDataTable

Where:

  • First parameter in ToTable() is a boolean which indicates whether you want distinct rows or not.
  • Second parameter in the ToTable() is the column name based on which we have to select distinct rows.

Only these columns will be in the returned datatable.

3

HI @Benny_Chan

Your approach is good… But, when working with UiPath, you cannot edit a excel sheet in a Excel Application Scope activity. So, even though you were able to remove duplicates using the hot key method, it will not get saved. So the best option would be to follow these steps.

  1. use a Read Range activity to read the data into a datatable
  2. Use the datatable activity Remove Duplicate Rows to remove all the duplicate rows from the datatable
  3. Use a Write Range activity to write the data back into the same excel. Write Range activity will overwrite the existing data, so you will finally have the modified dataset without duplicates.
4

Thanks @Lahiru.Fernando @lakshman for prompt action.

In fact, I cannot use data table approach because when I paste the content, the leading zero will be removed and the value become Number (e.g. read range with value ‘00001’ and the cell become 1 after using write range. For details, you may refer to this question I posted before.

5

@Benny_Chan
You can use Excel Processing “Remove Duplicates Range” activity to remove the duplicates present in your excel sheet.

1 Like
6

Hi @Benny_Chan
Use remove data row activity based on condition

Thanks
Ashwin.S

7

@Benny_Chan,

Please find a attached files,
Main.xaml (16.2 KB)

You will need to change selectors as per excel file before you go to run.
Please keep us posted If you stuck :slight_smile:

Cheers,
Pankaj

8

Hi @Benny_Chan,

I created a vba code to implement remove duplicates row action.
Please refer to my attachment.
RemoveDuplicate.zip (26.4 KB)

9

Thank You all!! I mark @Karthick_Settu 's answer as solution as that exactly what I need.

Thanks again!!!

1 Like
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.