Hi,
I am trying to filter a set of data (see below) to filter out anything beyond the current date in my automation. I am not sure how to do this at all using the filter activity. Can anybody advise/help me do this? Is it possible?
Thanks,
Michael
@mpearson9526
Read Range the excel sheet to datatable
currentDate = DateTime.Now
filteredData = dtData.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("YourDateColumn").ToString(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture) <= currentDate).CopyToDataTable()
Write range
Do you want to use the filter with excel activities or you can read the excel to a datatable and filter.
Give me a clarification on this…
If you want to filter after converting to a datatable then you can use the LINQ Expressions,
→ Use the Read range workbook activity to read the excel and store in a datatable called dt.
→ Then use the assign activity to write the LINQ Expression,
Below query is for Dates are behind and equal to Today's date
- Assign -> dt = (From row In dt
Where CDate(row("Due date").toString)<=DateTime.Now
Select row
).Copytodatatable()
Below query is for Dates are behind to Todays' date only,
- Assign -> dt = (From row In dt
Where CDate(row("Due date").toString)<DateTime.Now
Select row
).Copytodatatable()
→ Then use the write range workbook activity to write the dt to the excel.
Check the below workflow image for better understanding,
Hope it helps!!
Hi,
thank you but I want to filter using the excel activities in UiPath, is this possible?
Hi,
Thanks, do you know if it’s possible to do this through the excel filter activity? I don’t want to read the excel to a data table
Sub FilterOutFutureDates()
Dim ws As Worksheet
Dim lastRow As Long
Dim today As Date
Set ws = ThisWorkbook.Sheets("Sheet3") ' Change "Sheet1" to your sheet name
today = Date
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Adjust the column as needed
' Clear any existing filters
If ws.AutoFilterMode Then ws.AutoFilterMode = False
' Apply filter to hide rows with dates beyond today
ws.Range("B1:B" & lastRow).AutoFilter Field:=1, Criteria1:="<=" & today, Operator:=xlAnd
End Sub
In the above my date column is in “B” so i give B
FilterOutFutureDates.txt (563 Bytes)
Thank you but I don’t really know how to follow this, I have no background in coding.
The column with the dates in my spreadsheet is J and the sheet name is “Tasks”. How would this fit into the text file you provided?
FilterOutFutureDates.txt (562 Bytes)
Sub FilterOutFutureDates()
Dim ws As Worksheet
Dim lastRow As Long
Dim today As Date
Set ws = ThisWorkbook.Sheets("Tasks") ' Change "Sheet1" to your sheet name
today = Date
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Adjust the column as needed
' Clear any existing filters
If ws.AutoFilterMode Then ws.AutoFilterMode = False
' Apply filter to hide rows with dates beyond today
ws.Range("J1:J" & lastRow).AutoFilter Field:=1, Criteria1:="<=" & today, Operator:=xlAnd
End Sub
I updated the code as your sheet name Tasks and Dates Column “J1:J” It takes entire J column and filter it.
Hope this will helps for you
I appreciate that thank you. Just for clarification I’m assuming I’m meant to remove the text colored red from the file, is this correct?
It filters date which is beyond the Current date.Please run the code so you will understand.
I don’t seem to be able to target the workbook. I keep getting the following error:
“Argument ‘Workbook’: BC30512: Option Strict On disallows implicit conversions from ‘String’ to ‘IWorkbookQuickHandle’. The selected value is incompatible with the property type
Activity is valid only inside the “Use Excel File” scope.”
Any idea how to link the workbook?
Hi, unfortunately the process doesn’t work for me, there are still future dates in the filtered file when I use this. Is there another way you know of?
Can you please share the sample file so i will check.
Hello @mpearson9526
Have you tried this approach,
Create the dt variable is of datatable type in the properties panel of read range workbook activity Output Prop
dt is the datatable datatype variable variable. You are using the Read range workbook activity above the assign activity right. Create a variable to the output of Read range workbook activity dt. Then your problem will resolve.
Hope you understand!!