255 character limit when copy to Excel

Scripting Forums AppleScript | Mac OS X
#1

When I read mail content into Excel I notice that the limit is 255 characters, an excel cell can have 1000+ characters. Is it possible to change that in AppleScript ?

set SetMailContent to content of MailInloop

set formula of range (“D” & MyRow) of DestSheet to SetMailContent

Thanks for reading

#2

This is because you are using “formula” to access the cell.
Formula is a reserved word used to enter a calculation formula, and there is a limit to the length of the formula due to the specifications of Excel.
You can use “value” to set a longer string.

スクリーンショット 2024-04-16 20.01.49
スクリーンショット 2024-04-16 20.01.491329×1184 236 KB

#3

Thanks for your answer, this is good to know. I am a VBA person so not very good at AppleScript. This I never forget :+1:

#4

To be clear, 255 is a limit on the AppleScript side; Excel allows up to 8192 characters in a formula.

Try this script:

tell application "Microsoft Excel" -- version must be > 2019
	tell active sheet
		-- fml is 255 characters
		set fml to "=LET(
char1,MID(\"ABCDEFGHIJKLMNOPQR\",RANDBETWEEN(1,17),1),
char2,MID(\"ABCDEFGHIJKLMNOPQR\",RANDBETWEEN(1,17),1),
char3,MID(\"ABCDEFGHIJKLMNOPQR\",RANDBETWEEN(1,17),1),
char4,MID(\"ABCDEFGHIJKLMNOPQ\",RANDBETWEEN(1,16),1),
CONCATENATE(char1,char2,char3,char4)
)"
		set formula2 of range "D13" to fml
		set chars to evaluate name fml
	end tell
end tell

and everything works. The formula result will be output to cell D13 and the chars variable will be set to the result of evaluating the formula.

But try this script:

tell application "Microsoft Excel" -- version must be > 2019
	tell active sheet
		-- fml is 256 characters
		set fml to "=LET(
char1,MID(\"ABCDEFGHIJKLMNOPQR\",RANDBETWEEN(1,17),1),
char2,MID(\"ABCDEFGHIJKLMNOPQR\",RANDBETWEEN(1,17),1),
char3,MID(\"ABCDEFGHIJKLMNOPQR\",RANDBETWEEN(1,17),1),
char4,MID(\"ABCDEFGHIJKLMNOPQR\",RANDBETWEEN(1,17),1),
CONCATENATE(char1,char2,char3,char4)
)"
		set formula2 of range "D13" to fml
		set chars to evaluate name fml
	end tell
end tell

and nothing will be output to Excel and the value of chars will be missing value.

(Note: I specify version must be > 2019 just because using a LET function was the easiest way for me to construct a long formula and LET is only available in post-2019 versions of Excel.)

But, you can absolutely paste the second formula into the formula bar of Excel and it will work. :roll_eyes:

#5

I describe the AppleScript support for Excel’s “formula” as a “limitation on the Excel side” that is implemented in this way.
Long calculation formulas can be entered from the Excel GUI side, but the Excel side internally sets a limit of 255 characters for “formula”, which is Excel’s AppleScript reserved word. That’s all there is to it.