Why you cannot use some Excel formulas

excel
activities
studio

#1

If I try to use Write Cell activity to write something to Excel, some formulas are working correctly but not all.

E.g. “=TRIM(” + value + “)” works correctly but I would need to use TEXT and CONCATENATE but I did not manage to get those working.

“=TEXT(”“21424"”; ““0000000"”)”
"=CONCATENATE("“bat”"; ““man””)"

Any ideas how to get those working?


#2

If the problem is you need " in the formula try using chr(34) instead

“=TEXT(" + chr(34) + “21424” + chr(34) + "; " + chr(34) + “0000000” + chr(34) + ")”


#3

I still receive following error; "Write Cell : The range does not exist"
Although same Activity works e.g. for TRIM that I mentioned in first comment.


#4

For embedded quotes you can double up with 4 quotes so entire line ends up being one string, like:
"=TEXT("""“21424"”""; “”"“0000000"”"")”

For “The range does not exist”, you might have a wrong Sheet name or the range field is wrong. So I would check that or you can provide that info here as well.


#5

Problem was that same Activity works if I change e.g. TRIM to formula. I do not touch to sheet name or cell definition.


#6

Do you have a screenie of the activity failing?
You can also place the formula into a message box to verify it shows how you want it to in the cell.

Thanks.


#7

This is now working as it should;
"=CONCATENATE("“bat”", ““man””)"

So it works with “,” but not with “;”. In Excel there works only “;” and for some reason some part is converting that “,” to “;”.