Hi All,
I want to lookup for a value and return the cell reference without using Excel Scope and also i dont want to use Datatable.Please Let me know if its possible!!!
No you canât achieve this without datatable. Any reason for not using the datatble?
I think itâs possible to solve this issue without using Excel Application Scope and DataTable. One alternative could be leveraging the âInvoke Codeâ activity in UiPath along with Excel Interop library. This method allows you to directly open the Excel file, search for a specific value, and then return the cell reference where itâs found. This way, you donât have to load the entire worksheet into memory or use DataTables, which can be particularly useful when youâre trying to reduce resource usage or simplify the automation process. Just keep in mind that to use this approach, Excel must be installed on the machine running your automation.
1 Like
Yeah i want to know the cell reference value.We are getting errors with excel scope after upgrading to Microsoft 365 Apps.So we are creating a reusable code for excel functionality without using svope activities.For Read and write we used Workbook activities but for lookup had to look for another approach
Which code can be used for Invoke Code activity ?
Iâm not exactly sure what you were trying to do, but Iâve prepared this code for you. Below is the VBA code, a screenshot from Studio, and a view of the test table in Excel. It searches for a Name and returns the cell address.
I typed âMarianâ as the input, and it gave me âB2â as the result.
Below are the arguments needed to use the code:
I hope this helps with your automation in some way.
1 Like
Thank You so much and really appreciate your patience.This is what i am looking for let me try this
Do you want me to send you the VBA code?
Yeah but one doubt do we have to enable the workbook for macros?
one thing you can do is to use excel as DB and query the records, then calculate the cell number using the value
Is it some question someone asked? I dont see any reason not to read data using workbook and then use datatable for lookup
cheers
Not really, because the macro is triggered from UiPath, so it can be any workbook.
Why not try to resolve those errors? Excel Scope and all the activities work just fine if configured properly, and would quickly and easily achieve your goal.
You can also use invoke code or codedworkflows and ClosedXML (it comes as sub dependecy from UiPath.Excel.Activities)
Main advantage over Excel.Interop is that it doesnât require to have excel installed on the machine and you donât have to worry about releasing com objects and checking if excel is âstuckâ in background
Iâve written two methods below, hope this helps:
Dim file As String = "path to your xlsx file"
Dim searchText As String = "the value you want to search for"
Dim foundAddress As String = ""
Dim workbook As XLWorkbook = New XLWorkbook(file)
Dim worksheet As IXLWorksheet = workbook.Worksheets(0)
'// Code v1 where we know the in which column to search
Dim columnIndex As Integer = 1 'change this to your column index
For Each row As IXLRow In worksheet.RowsUsed()
If row.Cells().ElementAt(columnIndex).ToString("V") = searchText Then
foundAddress = row.Cells().ElementAt(columnIndex).ToString("A")
Exit For
End If
Next
Console.WriteLine(String.Format("Found value: {0} in cell: {1}", searchText, foundAddress))
foundAddress = "" 'reset reference for second method
'// Code v2 where we don't know the column index
Dim searchCellFunction As Func(Of IXLCell, Boolean) = Function(cell As IXLCell) cell.ToString("V") = searchText
For Each row As IXLRow In worksheet.RowsUsed()
If row.Cells().Any(searchCellFunction) Then
foundAddress = row.Cells().First(searchCellFunction).ToString("A")
Exit For
End If
Next
Console.WriteLine(String.Format("Found value: {0} in cell: {1}", searchText, foundAddress))
can you please send me the code?
Please find below the code used in the Invoke Code activity.
vba.txt (1,9 KB)
You have to add âClosedXML.Excelâ to imports in the workflow.
Iâm attaching the whole project (FYI itâs with the latest Studio)
BlankProcess8.zip (113.8 KB)
Thank You So Much Tried this and its giving me the output. But incase if we dint have any specified range this might not work isnt it ?
Use only method 1 if you know the column or use method 2 if you want to search in all the cells/rows from a sheet
You could also adapt to search in all the sheets.
Check if you have System.Linq in imports in that workflow, thatâs where the method ElementAtOrDefault comes from.