Applescript Syntax query Excel 2004

[Peter Edman]

I've got about 200 Excel sheets that I need to get formatted in a
certain way. I want to create a style using AppleScript. I've figured
out how to modify the properties of the existing styles, but I cannot
figure out how to make a new style.

Can I get some basic syntax to get me started?

Many thanks.

Peter E.

 

[Peter Edman]

Please -- any MVPs or MS employees out there.

In my continuing fight with Applescript and Excel, I came across this
most irritating bug while trying to select a portion of a column:

If I tell it

set cagrs to cells headerrow through totalrow of cagrcols
where all are numeric variables and "cagrcols" is predefined as "column
N" (in this case 6, or F)

It then generates the Applescript event:
get cells 5 thru 31 of column "$F:$F"

But it produces this Excel selection (which is NOT correct)
range "$E$1,$AE$1" of column "$F:$F"

This looks like a bug to me.

Can anyone help me figure out the syntax to put the column into a
variable? I am the second person at least to ask this.

Am still hoping for a response to my earlier query about styles.

Peter E.

 

[Natasha_Microsoft]

Peter-
There seem to be two issues here: syntax for making a new Style object
and method for referring to a range.

How to make a new Style


Here is an example of how you can create a style named "Bold Style"
and modify its font and number format properties.

make new style at active workbook with properties {name:"Bold Style"}
set bold of font object of style "Bold Style" of active workbook to
true
set number format of style "Bold Style" of active workbook to "0.00"


Alternatively, you can use variables to make your scripting life a
little easier:

set myStyle to make new style at active workbook with properties
{name:"Bold Style"}
set bold of font object of myStyle to true
set number format of myStyle to "0.00"



How to make a range reference using integer row and column indices:


What you are doing should have worked, but there is a known issue
here. Luckily, there is a workaround. Use the get address event like
this: get address row 5 of column 6 of active sheet. This returns
"$F$5". Here is an example:

set firstRow to 5
set lastRow to 31
set theCol to column 6 of active sheet

set addrCell1 to get address row firstRow of theCol
set addrCell2 to get address row lastRow of theCol
set theRange to addrCell1 & ":" & addrCell2

Hope this helps you out.

Natasha

 

[Peter Edman]

Thank you, Natasha. That's a big help in both cases.

I had figured out the style syntax, but was trying to do the "make"
command inside a "tell active workbook" level, and apparently the
command must only be addressed to Excel itself.

(Also nice to know am not crazy with the addressing issue)

Cheers
Peter E.

 

[Peter Edman]

Natasha -- I spoke too soon.

Now that I've created the styles, I cannot get Excel to apply them to a
cell or range. It appears that the "name" object of the style object is
read only, so I can see it but not adjust it. I cannot see a command or
class that I could apply. I looked at the VB syntax, and it implies that
I should be able to "set style of R to Newstyle" but while it compiles,
I get error messages.

Peter

 

[Peter Edman]

Figured it out. The syntax is

set x to range "A1:Z4"
set q to style "Normal" of active workbook
set style object of x to q

The variable is required. And as with the "make new style" command, it
is (apparently) critically important that the "set q" declaration not be
within a subsidiary "tell" loop -- apparently it must be at the
application level.

Peter E.