How to delete Top 5 excel row?

Help
1

I have searched for the solution in UiPath forum and found most of them suggest Either:
1.> Read the data in DT and remove the rows from DT and write he DT in excel using "Write Range " activity. Or
2.> Using select range activity and then putting hot keys Ctrl + “-”

But I need some better solution as either of the above solution has its own limitation.
1.> Why do I have to read the whole data when I know I need to delete top 5 rows.
2.> Using Send Hot keys is not good if that hot key already defined in OS for some other activity.

Can any one suggest something better with either “Invoke Method” or “Invoke Code” activity?

For example lets say excelInstance is the Workbook object. How we can use invoke method activity to delete top 5 rows.

2

Hi,

If it is possible to use invoke code activity then execute the below code in that,

Dim excelApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook = excelApp.Workbooks.Open(“C:\Users\Desktop\sample.xlsx”, 1, False, 5, “”, “”, False, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, “”, True, False, Nothing, False)

    Try
        Dim excelWorkSheet As Microsoft.Office.Interop.Excel.Sheets = excelWorkbook.Sheets

        For Each work As Microsoft.Office.Interop.Excel.Worksheet In excelWorkSheet
            Dim range As Microsoft.Office.Interop.Excel.Range = work.Range("A1", "A5")
            Dim entireRow As Microsoft.Office.Interop.Excel.Range = range.EntireRow
            entireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp)
        Next

        excelWorkbook.Save()
    Catch ex As Exception
    Finally
        excelApp.Quit()
    End Try
3

Hi,

Thanks for your reply. I tried your solution but it is giving error:

“error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Application’. Are you missing an assembly reference? At line 1”

I did check that “Microsoft.Office.Interop.Excel” is there in my Imports tab. What could be the issue?

Is there a way to use just the “Invoke Method” activity on target Object “exccelInstance” which is a variable of the excel app scope activity?

Also is there a way to use “Invoke Code” activity using “excelInstance” variable?

Regards,
Amalendu

4

Hello @amakundu,

You can solve the problem using two ways,

  1. Read records in a data table and then delete the first 5 records from that Table and Write them in a different sheet.
  2. You can open the Excel and delete the records by using Hotkeys.

Alt+hdr

For your reference, I have attached both of the solutions.

DeleteRows -Open Excel and Delete Records for already existing data.zip (153.3 KB)
DeleteRows in Background and Write it in a diffrent sheet.zip (151.3 KB)

5

As I have mentioned in my original post, neither reading the excel into DT then delete 5 rows from DT and save it back to excel nor using hot keys is a good solution.

Why should someone read the excel data to delete 5 rows? it will increase the processing time. (consider there are thousands of records). Also hotkeys should be last option.

I am looking for a straight forward solution to delete rows from excel.

6

The straightforward method to delete the Records would be to open the excel and delete the records. what’s the issue with Hotkeys? Did you try executing “DeleteRows -Open Excel and Delete Records for already existing data.zip”?

I have dealt with sheets containing more than 3 Lakh records, the safest and fastest working solution was to read data in the DataTable and delete the first 5 records.

And the last thing you can do is write a macro and execute it using Uipath, but that would be overkill.

7

Problem with the hotkeys are if the hotkey combination is already defined by the user for something else. I as a developer do not have control to the hot key configuration made by user where the robot is running. Also the robot will perform the excel operation in non-visible mode.

data tables are viable option. True that it could be one of the accepted solution unless there is better way like running a VB.net code using Invoke Code or Invoke Method activity. I am against macro.

Just checking here if there are any such options.

8

Anyone with a working Invoke Code activity code for vb.net?

9

I have that option to work with, let me know what needs to be done.

10

@sarathi125 …Thanks buddy … your code helps.

11

I am getting following error, no solution found so far in forum…

Could not find type ‘DataTableExtensions.GroupAggregation’ in assembly ‘DataTableExtensions.Activities’. Row: 98, Column: 12