Write cell Excel formula

[Rems (Remi)]

Hello, I know this question has been asked before but I have not been able to solve my problem. I would like to write this formula

"=SI.NON.DISP(RECHERCHEV(CNUM(GAUCHE(G " + i.ToString + ";2));Feuil1!$B$1:$C$104;2;FAUX);"+ Chr(34) +"INDISP"+ Chr(34) + " )"

But it doesn’t work, I have an excel exception.
I tried to replace the semicolons by commas (I read it on a topic) but it doesn’t work.
Knowing that the formula works when I write it manually in Excel.
Thanks

[ankit.jain1 (Ankit Jain)]

Try by adding apostrophe i.e. ’ before = sign. And later remove it in second action from that cell and see if it works.

[Rems (Remi)]

How do you remove it exactly ?
You mean, use a first write cell activity to write the formula with an apostrophe, then use another activity to write it again without the aopostrophe this time ?

[ermanoj3101 (MANOJ)]

@Rems

"=SI.NON.DISP(RECHERCHEV(CNUM(GAUCHE(G " + i.ToString + ";2));Feuil1!$B$1:$C$104;2;FAUX);"+ Chr(34) +"INDISP"+ Chr(34) + " )"

Hi @Rems,

Is it working when you are passing hard coded values ?

Just test once on hard coded values if it’s working then you are making mistaking on making it dynamic.

If possible please share excel.

[Rems (Remi)]

I tested with hard coded value, and it doesn’t work.

UI path adds an @ to the beginning of the formula, and adds one of the ’ around the “FAUX”. So FAUX becomes ‘FAUX’.

If I manually remove the @ and the ', the formula works.

Sorry, I can’t share this document

[ermanoj3101 (MANOJ)]

Got it but without having file it’s hard to replicate.
Let me send you invoke code to update formula on cell, hope that will work for you.

Thanks.

[Rems (Remi)]

To make it simple, I have my main excel sheet, in which I have a column with postal codes (the first two digits of the code represent the department, hence the “GAUCHE” (means LEFT) function in the formula.

I have another excel sheet with department numbers associated with a person, like this:

In the main sheet I have to retrieve the person corresponding to the postal code, then sort the sheet by person. And if the search doesn’t find anything, I write “INDISP” in the cell

[ermanoj3101 (MANOJ)]

Hey I am assuming you have two diff sheets.

Try this =IFERROR(VLOOKUP(LEFT(A2,2)+0,Sheet2!A2:B4,2,0),“INDISP”)

Here,
“sheet2” is from where you want get matched records.

[Rems (Remi)]

Fine! That works well, thank you
My formula syntax was incompatible with UIpath?

Otherwise, I have a last question concerning the sorting. Is there any way to use the sorting capabilities of Excel? Or do I have to read my sheet, sort the datatable and then write the table in Excel ?

[ermanoj3101 (MANOJ)]

You can sort it but you have to use invoke code for it otherwise you have to read whole excel and put it in dt.

If you are allowed to put interop code then i will share that.

Thanks.

[ermanoj3101 (MANOJ)]

Try this
SortExcel.xaml (5.9 KB)

[Rems (Remi)]

I have an error “no compiled code to run”
I I have never used the invoke code activity, I’m not sure how it works.
I just replaced the arguments to match my excel workbook

[ermanoj3101 (MANOJ)]

It’s running code, what error you are getting ?

Is interop package installed ?

[Rems (Remi)]

mybad it wasn’t installed.
So I ran it and encountered a TargetInvocationException

Just to be sure, StartCell and EndCell delimit on which range you want to sort

And SortColumnCell represents the first cell of the column on which we sort ? I didn’t get this one well

[ermanoj3101 (MANOJ)]

Yes this is right.

SortColumnCell - The column you want to sort
StartCell and EndCel - Range if your excel ex A1 t0 Z20

[Rems (Remi)]

Yep that’s good. I was wrong about the range, it works now. Thanks!

[ermanoj3101 (MANOJ)]

great