Set Worksheet Name

Scripting Forums AppleScript | Mac OS X
#1

I need to set name of a new worksheet that name is used in other parts of my script but for some reason the combination name I build will not work when passed as a variable in the naming sequence. This scrip shows what I mean.



set Wday to ({characters 1 thru 3 of (weekday of (current date) as text)})
set CurrWeek to do shell script "date +%U"
set WsName to ("All Stocks on " & Wday & " in week " & CurrWeek)
--THIS WORKS
tell application "Microsoft Excel" to set name of active sheet to "All Stocks" & " in week " & Wday & " " & CurrentWeek
--THIS DOES NOT WORK
tell application "Microsoft Excel" to set name of active sheet to WsName
[/AppleScript]

Any suggestions please.
#2

You should be aware that there is a 31 character limit to a sheet name. If this is exceeded the sheet will not be named.

Problem is that the tell after “–THIS WORKS” the last variable is misspelled. In your code it is “CurrentWeek”, but you defined “CurrWeek” in the second line. If you substitute “CurrWeek” for “CurrentWeek”, it works.

Hope this helps.

Model: Mac Pro (Mid 2010)
AppleScript: 2.7
Browser: Firefox 79.0
Operating System: macOS 10.14

#3

What exactly do you mean by “does not work”?

Both of these work fine for me using Excel 16.44.

One thing: What does WsName look like after you assemble it? Excel has a hard limit of 31 characters for worksheet names and the one you are saying doesn’t work is hitting 28 characters on my system. It’s conceivable that on your system the resulting string could be >31, in which case it would not rename. Something to check.

#4

Thank you both. I had edited the post and the working script had CurrWeek in both places and as I said the 1st one worked the second did not.

Since posting I had closed Excel and in fact my machine when I logged back on I saw your replies ran the script I posted again (with the correction to Currweek) and both versions worked so I have no idea what my problem was.

Thank you both for taking the trouble to reply. By the way I did not know about the 31 character limited as I was truncating the weekday to 3 places it just fits but its too long and a useful bit of information.

#5

MitchBVI. I’m glad you got your script working.

This is a tiny issue, but, FWIW, the first line of your script returns a list in a list.

set Wday to ({characters 1 thru 3 of (weekday of (current date) as text)}) --> {{"S", "u", "n"}}

This works fine because the concatenation operator coerces the list to text, but a simpler approach would be:

set Wday to text 1 thru 3 of (weekday of (current date) as text) --> "Sun"
#7

Peavine I learn something every day thank you

Fredric 71 also thank you but I only wanted the 1st 3 letters, because although I did not know about the 31 character limitation in sheet names I did not want it to be too long.