Applescript Syntax

[fwbmorison]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm very new to Applescript, so I am sure I am overlooking some simple rule of syntax, but I would appreciate a pointer.

This compiles:
tell application "Microsoft Excel"
activate
(* Work with active sheet *)

set myRange to current region of active cell
set myTotal to get offset myRange row offset 9 column offset 1


select myTotal

end tell

As does this:

tell application "Microsoft Excel"
activate
(* Work with active sheet *)

set myRange to current region of active cell
set myTotal to get offset myRange row offset count of rows of myRange


select myTotal

end tell

but when I try to add a column offset in the second example, the applescript compiler tells me about a syntax error at the word column - expected end of line, etc. but found class name

e.g this won't compile
tell application "Microsoft Excel"
activate
(* Work with active sheet *)

set myRange to current region of active cell
set myTotal to get offset myRange row offset count of rows of myRange column offset 1


select myTotal

end tell

Thanks

 

[John McGhie]

Yeah, well I'm struggling too, because I don't do AppleScript, but I suggest
that you need to return rows and columns to a single OFFSET statement:

Maybe something like:
myRange row offset((count of rows of myRange), columns 1)

How do you know which is the active sheet? If you simply tell a workbook to
activate, will the active sheet not become the "first" sheet? So should you
not activate the sheet you want by name?

And how do you know which is the active cell? When you activate a sheet,
the active cell will be the cell last selected, won't it? Which is not
really safe...

I would be inclined to construct a formula that populates a specific cell
with the value you want, then create a Defined Name for that cell.

Then you can simply "ask" Excel directly for the total in "TheTotalIWant"
and get it, without your AppleScript needing to work out where it is

Note: Names are workbook-wide by default, but can also be created as
specific to a worksheet. In this case, you would want the global version,
so you do not have to work out which sheet you are on.

Look up "Dynamically Expanding Ranges"
http://www.contextures.com/xlNames01.html


Cheers

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm very new to Applescript, so I am sure I am overlooking some simple rule of
syntax, but I would appreciate a pointer.

This compiles:
tell application "Microsoft Excel"
activate
(* Work with active sheet *)

set myRange to current region of active cell
set myTotal to get offset myRange row offset 9 column offset 1


select myTotal

end tell

As does this:

tell application "Microsoft Excel"
activate
(* Work with active sheet *)

set myRange to current region of active cell
set myTotal to get offset myRange row offset count of rows of myRange


select myTotal

end tell

but when I try to add a column offset in the second example, the applescript
compiler tells me about a syntax error at the word column - expected end of
line, etc. but found class name

e.g this won't compile
tell application "Microsoft Excel"
activate
(* Work with active sheet *)

set myRange to current region of active cell
set myTotal to get offset myRange row offset count of rows of myRange
column offset 1


select myTotal

end tell

Thanks

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[fwbmorison]

Thanks John - unfortunately, the brackets thing didn't work. However, I will try to translate the VBA in the link you sent me to applescript and see whether than will work.

 

[John McGhie]

Yeah, sorry, I just don't know AppleScript. But I would expect that the
error is that you need to give OFFSET two arguments: Row and Column.

Cheers

Thanks John - unfortunately, the brackets thing didn't work. However, I will
try to translate the VBA in the link you sent me to applescript and see
whether than will work.

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]