Executing Excel Formula using datatable

[abdel (Robot usa)]

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:

[Marta (Marta Miąsko - Office Samurai)]

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)+“)”

[abdel (Robot usa)]

[Marta (Marta Miąsko - Office Samurai)]

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.

[abdel (Robot usa)]

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

[Marta (Marta Miąsko - Office Samurai)]

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.

[abdel (Robot usa)]

@Marta

Chr(34)

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))”

[Marta (Marta Miąsko - Office Samurai)]

“=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 ).

[abdel (Robot usa)]

“=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

[Marta (Marta Miąsko - Office Samurai)]

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) + “)”

[abdel (Robot usa)]

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)+“)”

[Marta (Marta Miąsko - Office Samurai)]

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

[abdel (Robot usa)]

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

[Marta (Marta Miąsko - Office Samurai)]

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

[abdel (Robot usa)]

I can’t Excel activities du to security concern

[Marta (Marta Miąsko - Office Samurai)]

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.

[abdel (Robot usa)]

didi you tryed it ?

because it not working for me

[Marta (Marta Miąsko - Office Samurai)]

Yes, it works for me:

[Marta (Marta Miąsko - Office Samurai)]

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

[abdel (Robot usa)]

no, it not working fine.
result :

whereas expected result is :