Excel 2004 Applescript help

[cary]

I've been using Applescript (auto-generated by python) fairly
extensively to generate Excel spreadsheets for work, and it has worked
quite well... until I upgraded to Office 2004. I don't doubt that
there are some good reasons for the drastic changes made in the
applescript sytax for this Office vs. the earlier versions, but I have
to say, it was a little unnerving to see a lot of work become invalid.
Anyways....

Has anyone had a decent amount of experience, and success, with Excel
2004 and Applescript? Right now I'm trying to figure out how to do
things like draw borders around ranges, set background colors, etc.
The code that I had for these actions no longer works at all.
Unfortunately, I still find the dictionaries to be too cryptic, and in
some cases I'd swear there's missing info.

What I would love to see is an Excel 2004 Applescript Reference like
the one Microsoft has for Word available on their Mac site. Anyone
know of any plans for this?

Thanks!

P.S. Please don't recommend VB as a solution; it won't work in my
case, I really need to be able to use straight Applescript.

 

[Paul Berkowitz]

I've been using Applescript (auto-generated by python) fairly
extensively to generate Excel spreadsheets for work, and it has worked
quite well... until I upgraded to Office 2004. I don't doubt that
there are some good reasons for the drastic changes made in the
applescript sytax for this Office vs. the earlier versions, but I have
to say, it was a little unnerving to see a lot of work become invalid.
Anyways....

There were very good reasons. This version is stable and has a much bigger
vocabulary and works in localized (non-English) versions too.

Has anyone had a decent amount of experience, and success, with Excel
2004 and Applescript? Right now I'm trying to figure out how to do
things like draw borders around ranges, set background colors, etc.
The code that I had for these actions no longer works at all.
Unfortunately, I still find the dictionaries to be too cryptic, and in
some cases I'd swear there's missing info.

The terminology has changed, as has the "real" «raw code» underlying the the
English surface. That means that when you open an older compiled script
while Excel 2004 is open, you'll see only incomprehensible raw codes, which
are now invalidated and meaningless - they don't de-compile to the English
terminology. What you can do is quite your Script Editor, quit Excel 2004,
if you use Apple's Script Editor you even need to log out and in. Then open
Excel X, open your script editor. open your old scripts. Save them, or a
copy , as Text (not Script or Application). Quit Excel X, quit Script
Editor, log out and in. Open Excel 2004, Script editor, Now open the text
scripts. They won't compile, but you can use the text version
(FormerVersionEnglish, like this) as a guide to reconstructing the scripts.
Several of the old commands have new equivalents in lowercase simple words.

What I would love to see is an Excel 2004 Applescript Reference like
the one Microsoft has for Word available on their Mac site. Anyone
know of any plans for this?

Yes, there are plans, but I don't know when it's meant to be ready.

Thanks!

P.S. Please don't recommend VB as a solution; it won't work in my
case, I really need to be able to use straight Applescript.

Not a VB solution, but VB Help. ALL the AppleScript commands and classes and
properties have exact parallels in VBA Methods, Objects and Properties. The
new AppleScript is a mirror of the VBA Object Model. (Actually, both
AppleScript and VBA plug into the lower-level OLE Automation model.) The VBE
Help is very good. So if you shove the words of an applescript term together
like the old-style AS ('border around' as "BorderAround') and enter that in
VB Editor's Help, you'll discover how it's meant to work. Then you'll know
how to do it in AppleScript, since it uses equivalent parameters
(arguments).


--
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.

 

[cary]

The terminology has changed, as has the "real" «raw code» underlying the the

English surface. That means that when you open an older compiled script
while Excel 2004 is open, you'll see only incomprehensible raw codes, which
are now invalidated and meaningless - they don't de-compile to the English
terminology. What you can do is quite your Script Editor, quit Excel 2004,
if you use Apple's Script Editor you even need to log out and in. Then open
Excel X, open your script editor. open your old scripts. Save them, or a
copy , as Text (not Script or Application). Quit Excel X, quit Script
Editor, log out and in. Open Excel 2004, Script editor, Now open the text
scripts. They won't compile, but you can use the text version
(FormerVersionEnglish, like this) as a guide to reconstructing the scripts.
Several of the old commands have new equivalents in lowercase simple words.
Very interesting, thanks.

Yes, there are plans, but I don't know when it's meant to be ready.
Glad to hear it.

Not a VB solution, but VB Help. ALL the AppleScript commands and classes and
properties have exact parallels in VBA Methods, Objects and Properties. The
new AppleScript is a mirror of the VBA Object Model. (Actually, both
AppleScript and VBA plug into the lower-level OLE Automation model.) The VBE
Help is very good. So if you shove the words of an applescript term together
like the old-style AS ('border around' as "BorderAround') and enter that in
VB Editor's Help, you'll discover how it's meant to work. Then you'll know
how to do it in AppleScript, since it uses equivalent parameters
(arguments).

This seems like great advice, and I'm sure it will work in a lot of
cases, but there are some that I am confused on. For example, I'm
trying to draw a grid, so I recorded a macro. Here's what I get:

Range("A612").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

So how do I translate a line like this:

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

or block like this:

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

???

It's the parentheses that are throwing me off.

Thanks,
Cary

 

[Paul Berkowitz]

So how do I translate a line like this:

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

set line style of (get border selection which border diagonal down) to
line style none


There are several 'get border' commands, depending whether you're looking
for the border of a cell (or range), of text, or of a thing called a format
condition. I'm assuming a cell or range here, since you're dealing with
selection. 'get border' in the Table Suite requires a range and a border
type ('which border' parameter) and returns a border. Fortunately, selection
(a property of 'application' in Excel Suite) is a range (unlike in Word), so
that's easy. AppleScript, unlike VBA, does not have "Collection Objects"
(Borders) from which to specify a particular member, so the developers have
implemented this handy 'which border' parameter with the identical set of
enumerations as the Borders collection in VBA. 'diagonal down' is the one
you want.

The trouble wit recording is that it always deals with selection, which is
messy and slow. So once you test your script version (above) replace
'selection' by the real range you want, e.g.

set line style of (get border cell "$B$6" which border diagonal down) to
line style none

or block like this:

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

???

It's the parentheses that are throwing me off.

Different syntax. In this case VB parentheses immediately after a collection
object containing an enumeration ( xlInsideHorizontal) is specifying which
member of the collection. Since we don't have collections in AppleScript
(instead we have plurals as list when dealing with application objects, or
one of these parameters with a set of possible enumerations when dealing
with a command, as here) use the tool available - the 'get border' command.

Whenever you see a "With" block in VB you can substitute tell block in AS:


tell application "Microsoft Excel"
tell (get border selection which border inside horizontal)
set line style to continuous
set weight to border weight thin
set color index to color index automatic
end tell
end tell


Again, substitute the range you want instead of 'selection'. All the
properties in the tell block are found in 'border' class in the Excel Suite.

--
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.