Get last row activity problem

[elt.rd13]

Hello. I am automating a task where i have to copy from a excel sheet and paste it in a different excel sheet. But the first excel sheet can contail different number of items. so i need to find last row number that includes data. please take a look at the 2 excel sheet examples. first sheet only contain 1 item and the other 1 contains 5 items. necessary items are marked in colour. i tried using get last row activity but it returns -1 all the time. please help me out guys. Thanks in advance.

[Kartheek_Battu]

Hello @elt.rd13

1. Excel Application Scope:

   • Use this activity to open the Excel file.

2. Read Range Activity:

   • Read the data from the Excel sheet into a DataTable.

3. Assign Activity:

   • Use an “Assign” activity to get the last row index with data.

     lastRowIndex = dataTable.Rows.Count - 1

     This assumes that your DataTable variable is named dataTable.

Thanks & Cheers!!!

[mohamed.saty2012 (Mohamed Saty)]

Approach 1: Using Excel Activities

1. **Read Range Activity: ** Use the “Read Range” activity to read the entire Excel sheet into a Data Table.
2. **For Each Row: ** Use a “For Each Row” activity to loop through the Data Table.
3. **Check for Empty Row: ** Inside the loop, check if the current row is empty or contains the marker color. If it does, break out of the loop; otherwise, continue to the next row.
4. Store Last Non-Empty Row: Store the row index of the last non-empty row.

Approach 2: Using VB.NET Code

If the first approach doesn’t work well with your setup, you can use VB.NET code in UiPath to find the last row with data.

1. Invoke Code Activity: Use the “Invoke Code” activity in UiPath.
2. VB.NET Code: Write VB.NET code to find the last row with data.

Dim excelApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
Dim workbook As Microsoft.Office.Interop.Excel.Workbook = excelApp.Workbooks.Open("your_excel_file_path.xlsx")
Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name

Dim lastRow As Integer = worksheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, False, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row

workbook.Close(False)
excelApp.Quit()

Return lastRow

Make sure to replace “your_excel_file_path.xlsx” and “Sheet1” with the actual path to your Excel file and the sheet name, respectively.

Important Notes:

• Ensure that the Excel file is not open when running the automation.
• If you are working with the modern activities package in UiPath, you might need to use the Read Range activity with the Add Headers property set to True.
• Always test your automation with different scenarios to make sure it handles varying numbers of items correctly.

[elt.rd13]

Thank you so much. This works.