Applescript and Excel 2004 - Paste ?

[Marty Thomasson]

I just want to paste into Excel 2004. I have multiple columns of data copied
from another worksheet. I just want to make a new document, go to cell A1
and paste.

Not being an Applescript pro, I have, in the past, been able to look around
the web for examples and cobble routines together to get simple tasks done.
Now I am pulling my hair out trying to find examples of Applescript use in
Excel 2004 to help me figure out the syntax. Microsoft doesn't even seem to
supply any. Frustrating!

-marty

 

[cary]

Slowly but surely I'm piecing together how to use Applescript with
Excel 2004. I agree that the lack of documentation is frustrating.

In case this is of any use to you, here's a script the shows pretty
much everything I know how to do so far:

--------------------
tell application "Microsoft Excel"
activate
make new document
set view of active window to normal view

-- set the name of the sheet
set name of active sheet to "first sheet"

-- set the value of cell 1 of row 1
set value of cell 1 of row 1 to "This is cell 1 of row 1"

-- another way to set the value of a cell, below the previous
set value of range "A2" to "This is range A2"

-- set the font of these cells to 16 pt, bold, and red
set font size of font object of range "A1:A2" to 16.0
set bold of font object of range "A1:A2" to true
set color of font object of range "A1:A2" to {255, 0, 0}

-- set width of column A to 40
set column width of column "A:A" to 40.0

-- save the file (replace <HD> with hard drive name and
-- <username> with your username
tell active workbook
save workbook as filename
"<HD>:Users:<username>esktop:sample.xls"
end tell

close active window
end tell
--------------------

I'm still trying to figure out how to set the background color, draw
grids, etc. If you or anyone else figures out how to do this please
let me know!

Cary

 

[Fredrik Wahlgren]

Isn't it possible to record AppleScript events? In that case, you should be
able to do what you want to do manually and then edit the generated script.

/ Fredrik

 

[Paul Berkowitz]

Isn't it possible to record AppleScript events? In that case, you should be
able to do what you want to do manually and then edit the generated script.

Not in 2994. See my reply to cary's earlier thread.


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

 

[Paul Berkowitz]

I just want to paste into Excel 2004. I have multiple columns of data copied
from another worksheet. I just want to make a new document, go to cell A1
and paste.

Not being an Applescript pro, I have, in the past, been able to look around
the web for examples and cobble routines together to get simple tasks done.
Now I am pulling my hair out trying to find examples of Applescript use in
Excel 2004 to help me figure out the syntax. Microsoft doesn't even seem to
supply any. Frustrating!

tell application "Microsoft Excel"
make new workbook
paste worksheet active sheet
end tell


assuming you meant 'workbook' when you said 'document'. If you mean just a
new sheet in an existing workbook already in the front, then


tell application "Microsoft Excel"
make new worksheet at active workbook
paste worksheet active sheet
end tell



You just need to look in the dictionary. In the Microsoft Excel Suite, look
up up the commands (non-italic) which are alphabetical, and you'll find
'paste worksheet'. It's followed by the term 'sheet', meaning you have to
specify which sheet to use. Sheets are usual Excel worksheets ( a subclass),
and are elements of workbooks (see 'workbook' in the Dictionary - italics
since it's a class) where they can be referred to their name or number
Workbooks in turn are referred to by name or number. E.g.


worksheet "Sheet 2" of workbook "My Excel File.xls"

But in this case, since you're making a new workbook or worksheet, the new
sheet will be in the front, i.e. 'active worksheet', so just use that after
making the object. You don't know what name it will have, (it depends on how
many windows are open and how many sheets already exist( so this is in fact
the best way to do it.


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

 

[Paul Berkowitz]

I'm still trying to figure out how to set the background color, draw
grids, etc. If you or anyone else figures out how to do this please
let me know!

I've never done any of this sort of thing in Excel (in the UI). But I can
see from the Dictionary how it's likely to work.

You haven't made clear if you're looking for the background color of a cell
(or range), or of a shape. I'll assume a cell.

This gets rather arcane. You'll notice that cell and range have an 'interior
object' property of type 'interior'. If you look long and hard, you'll find
an 'interior' class buried deep in the Chart Suite, of all places. It has a
'color' property, which is of type RGB color. Now RGB color is a standard
AppleScript type, which is a list of three integers between 0 and 65535 (2 ^
16) - 1. But _this_ RGB value seems to be the Microsoft VBA variety - a list
of three integers between 0 and 255. I'll report this as a bug. (You can
convert between the two types by squaring or getting the square root of each
integer.)

First I colored one cell D5 a shade of teal in Format/Cells. This works:

tell application "Microsoft Excel"
set theCell to cell "D5"
set theInterior to interior object of theCell
set b to color of theInterior
end tell
--> {128, 249, 111}

Or you can learn the table of color indices, I suppose, and use 'color
index':


tell application "Microsoft Excel"
set theCell to cell "D5"
set theInterior to interior object of theCell
set b to color index of theInterior
end tell
--> 33

You can set the color too. Here I set the same background color for the
whole used range:


tell application "Microsoft Excel"
set color of interior object of used range of active sheet to {128, 249,
111}
end tell


-- except that turned it green! There's a bug here somewhere.

tell application "Microsoft Excel"
set color index of interior object of used range of active sheet to 33
end tell

This worked correctly.

(I tried squaring the integers, but that resulted in a different darker
blue.)

So you'd better get the color index of all the colors you're interested in
and use those.

If you're dealing with shapes (Drawing Suite) , which can be made 'at' a
worksheet with properties {auto shape type: , left position: , top: ,
fill format: } , you can set the back color of the fill format (Drawing
Suite) to an RGB color.

You'll see in the Table Suite that you can add a border to a cell or range
by using the command 'border around':

border around: Adds a border to a range and sets the color, line style, and
weight properties for the new border.
border around range
[line style continuous/dash/dash dot/dash dot dot/dot/double/slant
dash dot/line style none] -- The line style for the border.
[weight border weight hairline/border weight medium/border weight
thick/border weight thin] -- The border weight.
[color index color index automatic/color index none/a color index
integer] -- The border color, as an index into the current color palette.
[color RGB color] -- The border color, as an RGB value.


which has all the options you need. It's very strange that you can then 'get
border' range, although range itself has no border property or elements.
Still, this should work. I'll look into it.

There's also a 'border' class in the Excel Suite, but only checkboxes,
option (boxes) ad text boxes seem to have this type of border. (And text
perhaps.) 'get border' command can get these too.


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

First I colored one cell D5 a shade of teal in Format/Cells. This works:

tell application "Microsoft Excel"
set theCell to cell "D5"
set theInterior to interior object of theCell
set b to color of theInterior
end tell
--> {128, 249, 111}

Or you can learn the table of color indices, I suppose, and use 'color
index':


tell application "Microsoft Excel"
set theCell to cell "D5"
set theInterior to interior object of theCell
set b to color index of theInterior
end tell
--> 33

You can set the color too. Here I set the same background color for the
whole used range:


tell application "Microsoft Excel"
set color of interior object of used range of active sheet to {128, 249,
111}
end tell


-- except that turned it green! There's a bug here somewhere.

tell application "Microsoft Excel"
set color index of interior object of used range of active sheet to 33
end tell

This worked correctly.

(I tried squaring the integers, but that resulted in a different darker
blue.)

So you'd better get the color index of all the colors you're interested in
and use those.

This is really helpful, thanks! I do have a table of the color indices
I can use.

You'll see in the Table Suite that you can add a border to a cell or range
by using the command 'border around':

border around: Adds a border to a range and sets the color, line style, and
weight properties for the new border.
border around range
[line style continuous/dash/dash dot/dash dot dot/dot/double/slant
dash dot/line style none] -- The line style for the border.
[weight border weight hairline/border weight medium/border weight
thick/border weight thin] -- The border weight.
[color index color index automatic/color index none/a color index
integer] -- The border color, as an index into the current color palette.
[color RGB color] -- The border color, as an RGB value.


which has all the options you need. It's very strange that you can then 'get
border' range, although range itself has no border property or elements.
Still, this should work. I'll look into it.

There's also a 'border' class in the Excel Suite, but only checkboxes,
option (boxes) ad text boxes seem to have this type of border. (And text
perhaps.) 'get border' command can get these too.

This is helpful too, thanks! I can now draw borders again.

My next difficulty is trying to set a grid pattern (inside horizontal
and vertical lines) on a range instead of just a border (around the
outside of the range only). I try this:

set insideHorizontalBorder to (get border inside horizontal of range
"A612")

or this:

set insideVerticalBorder to (get border inside vertical of range
"A612")

to get at these borders and I get the error message:

Can't get inside horizontal of range "A612". Access not allowed.

Any ideas of what I could be doing wrong, or how to set a grid for a
selection/range?

 

[Paul Berkowitz]

My next difficulty is trying to set a grid pattern (inside horizontal
and vertical lines) on a range instead of just a border (around the
outside of the range only). I try this:

set insideHorizontalBorder to (get border inside horizontal of range
"A612")

or this:

set insideVerticalBorder to (get border inside vertical of range
"A612")

to get at these borders and I get the error message:

Can't get inside horizontal of range "A612". Access not allowed.

Any ideas of what I could be doing wrong, or how to set a grid for a
selection/range?

set insideHorizontalBorder to (get border of range "A612" which border
inside horizontal)

You forgot to include the 'which border' parameter, although you used one of
its enumerations (inside horizontal).


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