Delete Excel rows with specific value

The code executes without any error but doesn’t remove the rows that is to be removed based on the keywords

Hi,

In my environment, the workflow removes row of ID1,2,4 and 6. This overwrites the Excel file, so it can only be executed once. Do you mean your excel sheet is still the left image, even if run the workflow?

img20220201-a1

Regards,

yes still the left image

Alright. I’ll review my workflow.

Hi @Yoichi ,

Any updates on the workflow coz its not working for me

Hi,

For now, can you try the following for investigating cause? Probably, exception occurs in InvokeCode and i added logic of output Exception.Message to Console.

Sample20220201-2v3.zip (15.3 KB)

regards

Will try , the excel file has to be in the same folder or it can be anywher

Hi,

The above sample assumes the target excel file exists in same folder. However if we want to change it, please modify content of argument of Invoke code activity.
When you run (debug mode) it, please share content of Output panel.

Regards,

1 Like

Hi @Ritika_Singh ,

try with the below VB code to filter the excel with the multiple options and delete the filtered rows. if you have lot of options need to filter you could go with array concept. thanks

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb1 As Microsoft.Office.Interop.Excel.Workbook
Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb1 = excel.Workbooks.Open(“C:\Users\kirmah\Desktop\ID.xlsx”)
ws1 = CType(wb1.Sheets(“Sheet1”), Microsoft.Office.Interop.Excel.Worksheet)
ws1.Range(“A1:E1”).AutoFilter(2,“Completed”,Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr,“Exception”)
excel.DisplayAlerts=False
ws1.UsedRange.Rows.Delete
wb1.Save
wb1.Close
excel.Quit
ws1 = Nothing
wb1 = Nothing
excel = Nothing
GC.Collect

Filter and Delete rows.zip (8.5 KB)

For the sample file which you have shared its working for my actual file which has 52 columns and 26456 records it is not removing the rows and the keywords are big not one worded like " Exception Triggered" Unhandled Exception Caught

Is there a way directly by serching the Status column with these key words we can remove rather than serching the whole sheet for ythe key words

Hi @Ritika_Singh ,

Did you tried vb code I suggested for your requirement. Thanks.

VB Script need to be written in text file and execute from Uipath right ?

No we can directly paste the code which I shared in invoke code activity and select language type vb in the invoke code activity properties. pls refer the attached work flow which I shared for your reference. Thanks

Tried it but keeps the excel locked for sometime and no changes. The Range is not fixed for me the Column name I know and can be the 50th column or any place

you no need to give any range just mention the range like headers A1:Z1
use your last column having data. it will create filters in the headers. you can select the entire columns and give the index of the column which is having status. thanks


Not working and after code execution the file is locked for long time

Did you refer the work flow i have shared it is working version. what is the status condition you have to filter in your excel.

Exception Triggered , Unhandled Exception found

is it possible to share your excel and share us the requirement like what are the options you are filtering in the status column so that i would try at my end. thanks