AppleScript and Excel X

[Mitch Cohen]

I wrote an applescript for Excel 2004 which works fine. Someone asked
me about using it in Excel X so I thought I'd give it a try. I was
surprised that I couldn't get ANYTHING in AppleScript to work with Excel
X.

Given the following script:

tell application "Microsoft Excel"
activate
make new workbook
set value of cell 1 of row 1 to "hello"
end tell

you'd think no problem. But it fails on "make new workbook" - error
message is "Microsoft Excel got an error: Can't make a workbook."

If I comment out that line, the same sort of thing happens on setting
the value of the cell: "Microsoft Excel got an error: Can't set value of
cell 1 of row 1 to "hello"."

These are really simple, basic commands which have probably worked since
the dawn of time. These are just manually typed into Script Editor
while Excel X is already open (and Excel 2004 is not open). Reading
something else here I logged into a separate user account and did these
as the only tasks, same result.

Any idea what's going on?

 

[Paul Berkowitz]

I wrote an applescript for Excel 2004 which works fine. Someone asked
me about using it in Excel X so I thought I'd give it a try. I was
surprised that I couldn't get ANYTHING in AppleScript to work with Excel
X.

Given the following script:

tell application "Microsoft Excel"
activate
make new workbook
set value of cell 1 of row 1 to "hello"
end tell

you'd think no problem. But it fails on "make new workbook" - error
message is "Microsoft Excel got an error: Can't make a workbook."

If I comment out that line, the same sort of thing happens on setting
the value of the cell: "Microsoft Excel got an error: Can't set value of
cell 1 of row 1 to "hello"."

These are really simple, basic commands which have probably worked since
the dawn of time. These are just manually typed into Script Editor
while Excel X is already open (and Excel 2004 is not open). Reading
something else here I logged into a separate user account and did these
as the only tasks, same result.

Any idea what's going on?

Did you think of opening the Excel X AppleScript Dictionary? When you do so
(File/Open Dictionary/Browse to Excel X), you'll find a completely different
kettle of fish. The entire AppleScript implementation was changed in 2004 -
it's much larger in addition.

Yes, the commands existed since the dawn of time (well, actually they were
changed once before, in Excel 5). But then thy were changed in 2004.

Most of the simple commands have virtually exact equivalents in X, but the
terminology in X looks like WordsSquishedTogether. However the underlying
«raw codes» are completely different. If you open the X dictionary while
still in the same session of Script Editor where you were just using 2004,
the whole X dictionary will be in incomprehensible «raw codes» as will the
2004 script when closed and reopened.

To access the Excel X dictionary you have to close not only Excel 2004 but
also Script Editor. In fact I think you may need to log out and in again or
reboot. Then launch Excel X. (You'll now see only the 2004 raw codes in any
2004 script that you close an reopen when Excel X is open.) Since you wrote
the script and probably remember it, you'll have to start over again but use
the X terminology instead. If you don't trust yourself to remember it all,
then before you quit Script Editor and Excel 2004, Save a Copy of the 2004
script As Text. When you restart and relaunch Script Editor and Excel X,
open this text version. You won't be able to compile it, but you can use it
as a model for re-writing the script for X with WordsSquishedTogether X
terminology. Most of the X terms will be very close to the 2004 version, as
text. Then you will be able to compile and save.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Mitch Cohen]

No go.

I went through these exact steps:

-Restarted computer completely (G5 2x2GHz, 10.3.7)
-Launched Script Editor
-Launched Excel vX from the Finder
-Typed the following script:
tell application "Microsoft Excel"
Activate
make new Document
end tell
-Ran the script
-Recieved the error: "Microsoft Excel got an error: Can't make a
Document."

I substituted Window, Worksheet, and Workbook for Document, all the same
result.

I don't think it has anything to do with my install or having Office
2004 on the disk; someone that I'd emailed a script to (in text form
within the body of an email) had the same error on Make New Workbook,
and that user doesn't have 2004 installed.

I just tried this on another Mac here (10.3.6) that's never had 2004
installed, same result.

Make New Document has been supported for eons (as it's a standard item).
I even find a reference to it for Excel 4.0 at:

<http://support.microsoft.com/kb/q118779/>

So, at least on a few systems, something funny is happening with Excel
vX and AppleScript.

On both systems here, Word vX scripting is fine (at least it creates a
new document).

 

[JE McGimpsey]

Make New Document has been supported for eons (as it's a standard item).
I even find a reference to it for Excel 4.0 at:

<http://support.microsoft.com/kb/q118779/>

So, at least on a few systems, something funny is happening with Excel
vX and AppleScript.

Don't know about Make New Document having been supported for eons (it
doesn't work on my system with XL98, XL01, or XLv.X), but

Create New Document

works fine in all three.

 

[Paul Berkowitz]

Don't know about Make New Document having been supported for eons (it
doesn't work on my system with XL98, XL01, or XLv.X), but

Create New Document

works fine in all three.

'make [new]' is a standard AppleScript command from the Standard Suite
proposed by Apple. Sure - it's been around _in AppleScript_ since the
beginning. Most scriptable applications implement it. But like any command,
it must be implemented by the developers of the application - if they want
to. As JE says, it doesn't exist in Excel X, 2001, 98 or 5. Take a look in
the Standard Suite of the Excel X dictionary - it's not there. If it ain't
there, it doesn't exist for that application and you can't use it. Instead
you have to use the Create command from the Custom Suite. 'document' does
exist in the Standard Suite, so that's why you can use it - I think it just
is coerced to Workbook from the Custom Suite.

'make [new]' requires that every single class in the application have a
default implementation such that 'with properties' (or other parameters) are
_never_ required explicitly, but must always be available if you want. That
is not true with many of the Excel X and earlier classes. That's why they
had to come up with their proprietary 'Create' command. (You cannot 'Create
with properties' and instead have to specify many properties after first
creating the object.)

It is one of the more unnoticed features of the new Office 2004
implementation how well it adheres to standard and expected AppleScript
features like implementing 'make [new]' for all the hundreds of classes of
Excel, Word and PowerPoint. Every single class had to be provided with a
default implementation for every single one of its properties. (For example
Word tables were given five default columns and two rows, just as in the UI,
if you write just 'make new table' without specifying anything else.) This
does not exist for the VBA model which the new 2004 implementation is
otherwise mirroring. It was a tremendous amount of work done by one MacBU
developer to ensure that scripters could rely on all the expected features
"just working" and to make the Office apps netter-behaved Apple citizens -
as you expected but failed to find it in Excel X. Only by doing all this
preparatory work could 'make [new]' be implemented in the Standard Suite.

Always check the dictionary.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[JE McGimpsey]

As JE says, it doesn't exist in Excel X, 2001, 98 or 5. Take a look in
the Standard Suite of the Excel X dictionary - it's not there. If it ain't
there, it doesn't exist for that application and you can't use it.

I'm not sure about that - I switched to a different account (that I've
never, AFAIK, opened XL04 in) and checked the XLv.X dictionary and found
the make command in the Standard suite...

What I *am* sure about is that "make new document" doesn't work in
XL98-XLv.X.

 

[Paul Berkowitz]

I'm not sure about that - I switched to a different account (that I've
never, AFAIK, opened XL04 in) and checked the XLv.X dictionary and found
the make command in the Standard suite...

What I *am* sure about is that "make new document" doesn't work in
XL98-XLv.X.

Sorry, you're perfectly right, JE. My mistake. I guess that's worse - that
it was included in the Standard Suite but didn't work for at least some
classes, like document. This shouldn't be the case for 2004 - or at least
the bugs should be fixable. If anyone finds things that don't work as
advertised in 2004, please report them here and we'll pass them on. Also
even things that aren't supposed to work should just error, not crash. MacBU
does want to get AppleScript right in 2004. AppleScript in X is not being
actively supported, though, so workarounds (like using Create) or upgrading
to 2004 are the only options there. Things like the crashing are among the
reasons why AppleScript was thoroughly rewritten for 2004, even though Excel
X's version wasn't too bad (unlike Word X's which was terrible and PPT X's
which was non-existent).

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.