Applescript question

[Tom Carlson]

I had an extensive set of Applescripts that I used to use in Excel X. When I
switched to Excel 2004 for Mac, the scripts were rewritten automatically by
the computer. If I look at the identical files on another computer using
Excel X, they return to the way I originally wrote them. I have now started
to rewrite them for Excel 2004, but I am getting hung on trying to identify a
row and column number from the active cell. Here is the script;

tell application "Microsoft Excel"
activate
set RowNum to row of active cell
set ColNum to column of active cell
set formula r1c1 of active cell to "Comm"
set ColNum to (ColNum + 3)
set NewRange to "R" & RowNum & "C" & ColNum
select range NewRange
set formula r1c1 of active cell to "Commission—GSA"
set ColNum to (ColNum + 1)
set NewRange to "R" & RowNum & "C" & ColNum
select range NewRange
run script alias "OS X
BootOCUMENTS:AppleScripts:SigTaxScripts:SigCheckNum.scpt"
end tell

It works until I hit the line "set ColNum to (ColNum + 3)" at which point it
hangs and highlights the "3". I believe I am not getting the script to return
the row number and column number of the active cell properly. Does anyone
know how to do this, or know of any resource where I could search to discover
this? I tried the "Excel 2004 AppleScript Ref.pdf", but unfortunately it
didn't talk about this aspect.

Any suggestions would be greatly appreciated. I'd hate to have to go back to
Excel X or switch to QuicKeys when I am so close to getting it to work again.

Thanks,
Tom Carlson

 

[Dave Peterson]

If you don't get a response in this newsgroup, you may want to try:
microsoft.public.mac.office.excel

 

[Tom Carlson]

If you don't get a response in this newsgroup, you may want to try:
microsoft.public.mac.office.excel

Thanks, Dave. I'll try that.

Tom

 

[Tom Carlson]

Hi Again,

I tried the microsoft.public.mac.office.excel and got a couple of responses,
but nothing that solved it yet. The solution I think I'm looking for is how
to capture the row and column number from the active cell and paste it into a
variable. Seems like it should be fairly simple, but no luck so far. An
alternative method would be to start from the active cell and somehow
navigate X number of columns to the right.

Thanks in advance for anyone pondering this,

Tom Carlson

 

[Dave Peterson]

I don't think I've seen many (any???) apple script questions posted here--and
just as few responses.

Is there any other newsgroup/resource you can try?

Or maybe you can glean something from google?

http://groups.google.co.uk/advanced_group_search?q=group:*excel*
and search for:
Applescript

 

[Tom Carlson]

Thanks, Dave. I'll give it a shot.

I don't think I've seen many (any???) apple script questions posted here--and
just as few responses.

Is there any other newsgroup/resource you can try?

Or maybe you can glean something from google?

http://groups.google.co.uk/advanced_group_search?q=group:*excel*
and search for:
Applescript

 

[Bret Perry]

Thanks, Dave. I'll give it a shot.

 

[Bret Perry]

tried macscripter.net ?

I came across your thread and am just moving from mac Excel X to 2004

you do not really have the same problem that i am having, but just also
befuddled by moving from X to 2004...

At home G4 ExcelX my applescript worked great and then at work g5 Excel
2004 it "rewrote" it into an unusable mess. I got one part to work by
retyping the original (Excel X) code(!)

Weird. This works with Excel X but not when it opens in Applescript with
Excel 2004 (Office) installed. (It changes ito something I don't recognize
yet).

tell application "Microsoft Excel"
Activate
tell Worksheet 1
set Fcode_List to {Row_3:{Adv_Code:Formula of Cell 1 of Row 3,
Prod_Code:Formula of Cell 2 of Row 3, City:Formula of Cell 3 of Row 3,
F1:Formula of Cell 4 of Row 3, F2:Formula of Cell 5 of Row 3}} --etc.
end tell
end tell

 

[Tom Carlson]

Hi Dave,

Yes, it certainly is strange. I'll repost on this thread if I can find out
anything. I also got part of my script to work by retyping it. My main
problem is trying to figure out how to get the column number of the active
cell into a variable, so that I can then increment it and move a few cells to
the right. It used to work like a charm, but appears to be busted now. I'll
let you know.

Tom Carlson

 

[Tom Carlson]

Sorry, Bret. I misread the poster on your last message.

I found an answer to my problem. Here is a post I got on MacScripter:

Hi

In office 2004, if you use a text argument for the range address, you must
specify the address in A1-style notation
(you cannot use R1C1-style notation). For more information about how to
reference cells and
ranges, see the topic "How to reference cells and ranges." in
Excel2004AppleScriptRef.pdf

Open this Scriplet in your Editor:

tell application "Microsoft Excel"
activate
set formula r1c1 of active cell to "Comm"
select (get offset active cell column offset 3) -- +3 column
set formula r1c1 of active cell to "Commission—GSA"
select (get offset active cell column offset 1) -- +1 column
run script alias "OS X
BootOCUMENTS:AppleScripts:SigTaxScripts:SigCheckNum.scpt"
end tell

or

Open this Scriplet in your Editor:

tell application "Microsoft Excel"
activate
tell active cell to set {RowNum, ColNum} to {first row index, first
column index}
set formula r1c1 of active cell to "Comm"
set ColNum to (ColNum + 3)
select cell RowNum of column ColNum
set formula r1c1 of active cell to "Commission—GSA"
set ColNum to (ColNum + 1)
select cell RowNum of column ColNum
run script alias "OS X
BootOCUMENTS:AppleScripts:SigTaxScripts:SigCheckNum.scpt"
end tell

The script that Jacques sent worked perfectly. You might try posting your
question there as well.

Hope that helps,

Tom Carlson