Executing Excel Formula using datatable

Hello Friends,

I’m trying to exécute Excel formula using datatable,

The formula is: =SI(ET(J14=“non”;OU(D14=“TVX”;D14=“PDD”;D14=“CICM”;D14=“EF”));654200;654100)

and i tryed :
“=IF(AND(“J” + vlookupCounter.ToString = “non”+”;"+OR(“D”+vlookupCounter.ToString =“TVX”+";"+D"+vlookupCounter.ToString =“PDD”+";"+“D”+ vlookupCounter.ToString =“CICM”+";"+“D”+ vlookupCounter.ToString =“EF”))+";"+“654200”+";"+“654100”)"

where “vlookupCounter” is a counter (int32).

here is the error generated:

Hi!

When inserting string values to excel you should have additional quotes.

For example:
“=IF(5>2, ““true””,”“false”")"

Instead of “” you can also use Chr(34):
“=IF(5<2, “+Chr(34)+“true”+Chr(34)+”,”+Chr(34)+“false”+Chr(34)+")"

There are a few places where you mistook = with +

Also, mind that in the places where you put strings: non, TVX, PDD, CICM, EF you’re missing double double quotes “” (as I mentioned before). So it should be for example:
“=IF(AND(J”+Counter.ToString + Chr(34) + “non” + Chr(34) + ",OR(D… etc.

If this helps you, please mark my answer as a solution.

i modified the formula:
“=SI(ET(J”+Counter.ToString+Chr(34) +“non”+Chr(34) +"; OU(D"+Counter.ToString+ Chr(34) +“TVX”+Chr(34) +"; D"+Counter.ToString+ Chr(34) +“PDD”+Chr(34) +"; D"+ Counter.ToString+Chr(34) +“CICM”+Chr(34) +"; D"+ Counter.ToString+ Chr(34) +“EF”+Chr(34) +")) ; 654200 ; 654100)"

but it puts the formula and not the value in the xlsx

Which activity are you using? Workbook Write Cell or Write Cell within Excel Application Scope?

Moreover I think you are missing some ='s in the formula, for example:
=SI(ET(J”+Counter.ToString+"="+Chr(34) +“non”+Chr(34) +"; OU…

As you can see in your screenshot from excel, the values are: J14"non", D14"TVX" etc, so you’re missing =.

Firstly try to add missing ='s, then if it does not help, switch to Write Cell within Excel Application Scope.

Does not work
“=SI(ET(J”+Counter.ToString+"="+Chr(34) +""+“non”+Chr(34) +";OU(D"+Counter.ToString+"="+Chr(34) +“TVX”+Chr(34) +";D"+Counter.ToString+"="+Chr(34)+“PDD”+Chr(34)+";D"+Counter.ToString+"="+Chr(34) +“CICM”+"="+Chr(34) +"; D"+Counter.ToString+"="+Chr(34)+“EF”+Chr(34)+"));"+Chr(34)+“654200”+Chr(34)+";"+Chr(34)+“654100))”

“=SI(ET(J”+Counter.ToString+"="+Chr(34) +""+“non”+Chr(34) +";OU(D"+Counter.ToString+"="+Chr(34) +“TVX”+Chr(34) +";D"+Counter.ToString+"="+Chr(34)+“PDD”+Chr(34)+";D"+Counter.ToString+"="+Chr(34) +“CICM”+"="+Chr(34) +"; D"+Counter.ToString+"="+Chr(34)+“EF”+Chr(34)+"));"+Chr(34)+“654200”+Chr(34)+";"+Chr(34)+“654100))”

You should use either Chr(34) or “”, not both of them ( SI(ET(J”+Counter.ToString+"="+Chr(34) +""+“non” ).

At the end of your formula you’re missing one Chr(34) for 654100 and there is one unnecessary ).

“=SI(ET(J”+Counter.ToString+"="+Chr(34)+“non”+Chr(34) +";OU(D"+Counter.ToString+"="+Chr(34) +“TVX”+Chr(34) +";D"+Counter.ToString+"="+Chr(34)+“PDD”+Chr(34)+";D"+Counter.ToString+"="+Chr(34) +“CICM”+Chr(34) +";D"+Counter.ToString+"="+Chr(34)+“EF”+Chr(34)+"));"+Chr(34)+“654200”+Chr(34)+";"+Chr(34)+“654100”+Chr(34)+"))"

Does not work, always put the formula and not the value

Remove the unnecessary ) at the end.

I tried reqriting it for English version of Excel and it is working for me:
“=IF(AND(J”+5.ToString+"="+Chr(34) +“non”+Chr(34) +";OR(D"+5.ToString+"="+Chr(34) +“TVX”+Chr(34) +";D"+5.ToString+"="+Chr(34)+“PDD”+Chr(34)+";D"+5.ToString+"="+Chr(34) +“CICM”+"="+Chr(34) +"; D"+5.ToString+"="+Chr(34)+“EF”+Chr(34)+"));"+Chr(34)+“654200”+Chr(34)+";"+Chr(34)+“654100” + Chr(34) + “)”

it did not work for me when i write the DT with:

“=SI(ET(J”+Counter.ToString+"="+Chr(34) +“non”+Chr(34) +";OU(D"+Counter.ToString+"="+Chr(34) +“TVX”+Chr(34) +";D"+Counter.ToString+"="+Chr(34)+“PDD”+Chr(34)+";D"+Counter.ToString+"="+Chr(34) +“CICM”+"="+Chr(34) +";D"+Counter.ToString+"="+Chr(34)+“EF”+Chr(34)+"));"+Chr(34)+“654200”+Chr(34)+";"+Chr(34)+“654100”+Chr(34)+“)”

OR “=IF(AND(J”+Counter.ToString+"="+Chr(34) +“non”+Chr(34) +";OR(D"+Counter.ToString+"="+Chr(34) +“TVX”+Chr(34) +";D"+Counter.ToString+"="+Chr(34)+“PDD”+Chr(34)+";D"+Counter.ToString+"="+Chr(34) +“CICM”+"="+Chr(34) +";D"+Counter.ToString+"="+Chr(34)+“EF”+Chr(34)+"));"+Chr(34)+“654200”+Chr(34)+";"+Chr(34)+“654100”+Chr(34)+“)”

Can you please provide the sample of code (xaml file) which is not working?

test.xaml (37.9 KB)
here it is
sample.xlsx (16.0 KB)

As mentioned above, try using Excel Application Scope instead of workbook activities:
image

I can’t Excel activities du to security concern

Make sure you are using correct separator in formulas (; or ,) and try with Write cell activity in a loop:

Seems it is working as it should.

didi you tryed it ?

because it not working for me

Yes, it works for me:

test (1).xaml (39.2 KB)
Please find attached the workfow that is working for me.

no, it not working fine.
result :

whereas expected result is :
image