Find Max Date in Excel, Filter another Excel when date value is less than Max Date

[mlellison (Max Ellison)]

I have 2 excel files, “Master” & “Export”

“Master” Excel

“Export” Excel

Purpose:
1.) Find max date in “Master” Excel, store max date value as variable {maxDate}
2.) Filter “Export” Excel, keep only datarow that date value is larger than {maxDate}

Expected result in this case:
1.) maxDate will be 05/07/2023-12:54
2.) Below is the expected result after filtering

If possible, please try to solve this without using LINQ, Thanks!

[raja.arslankhan (Raja Muhammad Arslan khan)]

Step 1: Read the “Master” Excel file and find the maximum date value.

1. Use the “Excel Application Scope” activity to open the “Master” Excel file.
2. Use the “Read Range” activity to read the data from the desired sheet in the Excel file and store it in a DataTable variable, let’s say “dtMaster”.
3. Use the following Assign activity to get the maximum date value from the “dtMaster” DataTable and store it in the “maxDate” variable:

vbCopy code

maxDate = dtMaster.AsEnumerable().Max(Function(row) DateTime.Parse(row("DateColumn").ToString()))

Note: Replace “DateColumn” with the actual column name in your “Master” Excel file that contains the date values.

Step 2: Filter the “Export” Excel file based on the maximum date value.

1. Use the “Excel Application Scope” activity to open the “Export” Excel file.
2. Use the “Read Range” activity to read the data from the desired sheet in the Excel file and store it in a DataTable variable, let’s say “dtExport”.
3. Use the “Filter Data Table” activity to filter the “dtExport” DataTable based on the date column. Configure it as follows:

• Input: dtExport
• Output: filteredDtExport (create a new DataTable variable)
• Filter Wizard:
  • Add a condition where the date column is greater than {maxDate}:
    • ColumnName: DateColumn (replace with the actual column name in the “Export” Excel file)
    • Operation: >
    • Value: maxDate.ToString(“MM/dd/yyyy”)
  • Click on “OK” to apply the filter.

4. You can then use the “filteredDtExport” DataTable for further processing or write it to another Excel file using the “Write Range” activity.

Remember to add the necessary error handling and validation steps based on your specific requirements.