I need to insert a row into an excel sheet, preferably at the end of the current data but at the top would work too. The problem is, the sheet contains a template with headers and 1500+ blank rows as well as any number of rows of actual data as well. Because of this, it is hard to determine where the end of the data is.
I have tried to insert at row 5 every time, this works once but then when it runs again it overwrites the row that was just inserted. I have also tried to get the data into a data table, get the row count, and append to the end…but the template and blank rows make it so there are 1500+ rows even though there are only 10 or so rows of actual data.
If your data is inside Excel as a table, you can use the activity "Append Range/Append after Range = Excel.Table(“Tabela1”)
If you are not using a table inside Excel, you can use the “Find First/Last Data Row” activity.
Ex:
The trick is in the “Source Range” setting. You can define it from Ax to A1000000 (1 million), so when the activity finds the last filled cell in that column, it will stop and provide you with the index of that row.
If you don’t know the number of empty rows at the beginning of the Excel sheet, you can read the range and then count the empty values using a loop. Keep iterating until you find a non-empty value and record its index as the starting point for your data. This way, you dynamically determine the position of your data even if there are variable empty rows at the beginning of the sheet.
As last recurse you can try a VB.NET code (something like this):
Imports System
Imports System.Data
Imports System.Linq
Imports System.Threading
Imports System.Collections.Generic
Imports Microsoft.Office.Interop.Excel
Dim excelApp As New ApplicationClass()
Dim workBook As Workbook = excelApp.Workbooks.Open("C:\path\file.xlsx")
Dim workSheet As Worksheet = TryCast(workBook.Sheets("sheet"), Worksheet)
Dim lastRow As Integer = workSheet.Cells(workSheet.Rows.Count, "A").End(XlDirection.xlUp).Row
Dim firstNonEmptyRow As Integer = 0
For i As Integer = 1 To lastRow
Dim cell As Range = CType(workSheet.Cells(i, 1), Range)
If cell.Value IsNot Nothing AndAlso Not String.IsNullOrEmpty(cell.Value.ToString()) Then
firstNonEmptyRow = i
Exit For
End If
Next
' Now, 'firstNonEmptyRow' has the index for the first non-empty row.
workBook.Close()
Marshal.ReleaseComObject(workBook)
excelApp.Quit()
Marshal.ReleaseComObject(excelApp)
Thank you for this information. I am attempting to use the Find First/Last Row of Data activity but my source doesn’t have column names. How can I reference the first column without the name?
If your source doesn’t have column names, you can reference the columns by their index (0-based) rather than by name. In UiPath’s “Find First/Last Row of Data” activity, you can specify the column index
In UiPath, you can specify the column index in the “Column Name” field of the “Find First/Last Row of Data” activity properties as follows:
1.Add the “Find First/Last Row of Data” activity to your workflow.
2.In the “Properties” pane on the right-hand side of the UiPath Studio, look for the “Column Name” property under the “Input” section.
3.Click on the “Column Name” field, and a small icon with three dots (…) should appear on the right side of the field.
4.Click on the icon (…) to open the “Select” window.
5.In the “Select a Column” window, you can specify the column index by simply entering the index number as text. For example, to reference the first column (index 0), you would enter “0” in this field.
6. Click the “OK” button to save your selection.
UiPath.Excel.ExcelException: Exception from HRESULT: 0x800A03EC —> System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A03EC at Microsoft.Office.Interop.Excel._Worksheet.get_Range(Object Cell1, Object Cell2)
at UiPath.Excel.ExcelActionExecutor.<>c__DisplayClass5_11.<ExecuteForCurrentWorkbook>b__1() at UiPath.Excel.ExcelContextHelper.Execute[T](Func1 func, ExcelContext context, Object contextParams)
— End of inner exception stack trace —
at UiPath.Excel.ExcelContextHelper.Execute[T](Func1 func, ExcelContext context, Object[] contextParams) at UiPath.Excel.ExcelActionExecutor.ExecuteForCurrentWorkbook[T](Func2 func, String workSheet, String range)
at UiPath.Excel.WorkbookMetadataOperations.GetExcelMetadataForRange(String range, String worksheet, Boolean clipToUsedRange)
at UiPath.Excel.Activities.QuickHandle.RangeRefExtensions.GetAbsoluteDataAddressForRangeColumn(IReadRangeRef range, String columnName, Boolean hasHeaders, IExtendedWorkbook workbook)
at UiPath.Excel.Activities.Business.FindFirstLastDataRowX.<>c__DisplayClass36_0.b__0()
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at UiPath.Excel.Activities.Business.FindFirstLastDataRowX.d__36.MoveNext()
— End of stack trace from previous location where exception was thrown —
at UiPath.Shared.Activities.AsyncTaskCodeActivityImplementation.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at UiPath.Shared.Activities.AsyncTaskCodeActivity.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
I actually have both of those already in place. Kill Excel process before opening excel and delays after opening excel. I can try to lengthen the delays
