Problem to insert formula in Excel using Uipath

[Re_Walter (Walter Enzo Re)]

Hi Guys

I’m doing some tests using UiPath and I’m trying to store some data in Excel using UiPath robot.

I’ve created this sequence:

But when I try to insert some function inside Excel it stops working, showing the following error

with this error output:

RemoteException wrapping UiPath.Excel.ExcelException: Exception from HRESULT: 0x800A03EC —> RemoteException wrapping System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A03EC
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32 aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.set_Value(Object RangeValueDataType, Object )
at UiPath.Excel.WorksheetMetadataOperations.<>c__DisplayClass7_0.b__1(Range destinationRange)
at UiPath.Excel.WorksheetMetadataOperations.InternalAppendRange(ExcelRangeInfo rangeToAppendTo, Int32 rangeToCopyRowCount, Int32 rangeToCopyColumnCount, Int32 startingColumnIndex, Boolean transpose, Func`2 write)
at UiPath.Excel.WorksheetMetadataOperations.<>c__DisplayClass7_0.b__0()
at UiPath.Excel.ExcelContextHelper.Execute(Action action, ExcelContext context, Object contextParams)
— End of inner exception stack trace —
at UiPath.Excel.ExcelContextHelper.Execute(Action action, ExcelContext context, Object contextParams)
at UiPath.Excel.WorksheetMetadataOperations.AppendDataTable(DataTable source, ExcelRangeInfo rangeToAppendTo, Boolean excludeHeaders)
at UiPath.Excel.Activities.Business.WriteRangeX.<>c__DisplayClass20_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.WriteRangeX.d__20.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)

If I have only text it works pretty fine (even though it jumps some row before starts writing) but when I add formulas, any kind of it, it won’t work and it stops as soon as that formula should be written in the file.

This is my file:

The function ARROTONDA.ECCESSO.MAT is the CEILING function in my language.

This is the Excel output:

As you can see it jumps a lot of lines, and then starts writing until the formula happens.

Could you help me? Many thanks!

[Shikhar_Tandon]

Hi @Re_Walter ,

if you need to write only formula then you can use write cell activity from classic design.

Thanks,
Shikhar

[Re_Walter (Walter Enzo Re)]

Hi @Shikhar_Tandon

Many thanks for your reply.

This is just an experiment I’ve done to learn how to use these tools in Uipath.

I’d like to have a process that stores the data independently from the numbers of rows, or the text; so a process as general as possible.
I’m a very newbie in Uipath, so maybe I do something wrong with my sequence, but I don’t know how to fix it.

Many thanks

[nilesh.mahajan]

Hello,

you can use read range, counter and For Each to complete this solution.

First use Read Range and create Data Table - Then Create Counter Variable and then In For Each Row Put the formula with the help of Counter Increment.

Hope this will help to solve your challange.

[90s_Developer (Murli Manohar K)]

Hi @Re_Walter,

From converting Text files to Excel,
you need to separate the values by “,” comma which will take a cell value.

Use Read Text File, to read the text file
Use Write Text File, and in the output mention filename.csv or filename.xlsx whichever format you want.

UiPath | Convert Text File to CSV File | Get Data from Text File into CSV File | Create CSV File - YouTube

But, make sure that the values must be enclosed with “” because in the value ARROTONDA.ECCESSO.MAT(2,4;5) a comma is present in between 2 & 4, so it will consider as separator and move 4;5) value into another cell.

Sample Text File screenshot

Excel Result

Hope your issue will get solved.

Regards,
@90s_Developer

[Re_Walter (Walter Enzo Re)]

Hi @nilesh.mahajan Tanks for your reply.

I’m trying as you suggest, but I can’t understand something…

If I understand well, you suggest splitting the rows and inserting each component inside the cell? It is right?

I’ve tried using the same approach, but I’ve to change a little bit the algo, since the data coming from a file, I have to use Assign activity to store data inside a string array, named STRValue but I cannot convert string array into a data tables, since when I use generate data table from text it required a string, and I, don’t know why if I use STRValue.ToString to input, the output is “DataTable” as a String

In addition, the address of the Cell is static, and if I change the data inside the file, it is required to change also the code.

Have you any other suggestions? Many thanks

[Re_Walter (Walter Enzo Re)]

Hi @90s_Developer Many thanks for your reply.

I’ve tried, but in the way you suggest the data stored inside the Excel is a string, but I need to store also Formulas, like:

ARROTONDA.ECCESSO.MAT that is the CEIL function.

Have you other suggestions? Thanks

[90s_Developer (Murli Manohar K)]

@Re_Walter,

Sure, Give me some time I will update you with the latest logic.

Regards,
@90s_Developer