Excel operations with UiPath - Challenges and Solutions

excel
orchestrator
invoke_code

#1

There are occurrences where we find that a Job on Prod-Orchestrator gets faulted and comes to an abrupt end, while analyzing thru Event Viewer , most of the times it is because of UiPath Excel Activity - OutOfMemory exception.It is intriguing that this is not necessarily to be a case where we are writing/reading huge data or using excel activities in loop, OutOfMemory exception can even occur for a single writeCell activity.Though we can raise ticket to UiPath team and they can work onto improve the code for excel activities, there are certain workarounds that we can adapt to meet the project timelines -
I would like to share workarounds/approaches that we can apply to different situations, while dealing with Excel:

Case 1 : Only few cells to be updated
You can use Invoke code activity, and update the cells or range using Excel Interop library.You can combine other Excel operations which you are doing using separate excel activities. It is advisable to not use this approach when you are required to update huge data in loop, as each range object creates an interface with Excel and it consumes more run time memory.Here is sample

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(Filepath)
excel.Visible=False
excel.DisplayAlerts=False
ws=CType(wb.Sheets(SheetName),Microsoft.Office.Interop.Excel.Worksheet)
ws.Cells(6,2)=“Test”
ws.Cells(1,1)=“1050”
ws.UsedRange.Font.Name=“Arial”
ws.UsedRange.Font.Size=“8”
wb.Save
wb.Close(True)
excel.DisplayAlerts=True
wb=Nothing
excel.Quit
excel=Nothing

Case 2: Reading and writing huge data
Using OLEDB driver to read and write the data into excel as database, is the quick and reliable method.You can use the Database activities to Connect to database and Execute the queries.In case existing business process uses excel as the mediator application to connect to external data sources sing Microsoft query, It is advisable to read data directly using ODBC connection instead of doing surface automation.