Re: Scripting Excel X
Re: Scripting Excel X
- Subject: Re: Scripting Excel X
- From: "Eric Phillips" <email@hidden>
- Date: Wed, 08 May 2002 11:43:20 -0400
Paul responded:
>
I am running OS 10.1.3 with AS 1.7. I wrote a script to have Excel open a file
>
and run an Excel macro using the
>
>
Evaluate "macro name"
>
>
statement. I got this statement by recording the action. The problem is when
>
you run the script the macro does not run.
You need to include the name of the Workbook followed by a "!" followed by
the name of the macro with () after:
Evaluate "Workbook1!Macro Name()"
It's one of the very, very few things in Excel AppleScripting that is
described in the MS Knowledge Base:
<
http://www.microsoft.com/mac/support/mysupport.asp?lu=0>
>
So to fix that I took the time to
>
convert the macro to AppleScript. My problem here is I want to refer to a cell
>
in the form
>
>
select cell(rowcounter, 2)
>
>
The problem of doing this in AS is that I think it is looking at the cell
>
coordinates as an array because it changes the () to {}. To get the cell
>
coordinates into its preferred syntax I need to do the following
>
>
set cellref to "R" $ rowcounter & "C2" as text
>
select cell (cellref)
Yes.
Cell "R4C2"
is the way to refer to a cell, for example. Your code is a little wordy, but
correct. If you just call your variable 'rowcounter' by the letter 'n'
instead, it becomes:
set cellRefto "R" & n & "C2"
Select Cell cellRef
which isn't too bad.
>
>
Am I missing something or do I need to add this extra step every time I need
>
to change my cell reference?
There's possibly a neater way to do it if you supply more sample code.
>
Is there a problem of calling Excel macros from
>
AS?
No, just poor documentation. 'Evaluate' works as above.
--
Paul Berkowitz
I am sorry I didn't make my Evaluate statement more clear. I used exactly what Script Editor recorded.
Evaluate "Personal Macro Workbook!Macro Name()"
I am going from memory here since I don't have the script in font of me ans I am on a PC. I tried looking at the MS Knowledge Base but I must not be looking for the right think since I can't find information about the Evaluate command. I have had Excel open with only the document that I want to run the macro on open. I then used the following script but and Excel activates and then the Results window appears with "=" quotes and all. The Excel file is not changes at all.
Tell application "Microsoft Excel"
activate
Evaluate "Personal Macro Workbook!Macro Name()"
end tell
When changing this VB macro to AS I am having problems checking the value of a cell. In VB the statement looks like:
if Cell.Value (rowcounter,3) = "" then
I assumed since Cell.select (rowcounter,3) in VB changes to
Select cell "R1C3"
in AS the
Value cell "R1C3" should work but it doesn't.
I have also tried just
If cell "R1C3" is "" then
but this does not work either.
All this needs to do is to walk through one column looking for certain values in that column and then cut that cell and paste it one row down and two columns over. Any help in how to evaluate what the value of the cell is and a better way to move from cell to cell with out having to change "cellref" each time would be helpful. I feel dirty saying this but being able to use (1,1) format in VB is cleaner than having to use "R1C1" format in AS. I am sure Bill had his hand in this. I remember in Excel 4 Macro Language I could refer to a cell as y rows down and x columns over from the active cell. Can I do this in AS, it would mean when I cut the contents from one cell I could just use a referance to the cell I want to paste the information into with out having to go through the whole set cellref statement. Here is a snip of my script, the If statements never get entered:
set cellref to "R" & rowcounter & "C3" as text
if cell cellref is "Meats" then
Select cell cellref
cut
set nextrow to roecounter + 2
set cellref to "R" & nextrow & "C1" as text
select cell cellref
paste
select row rowcounter
delete selection
end if
_______________________________________________
applescript-users mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/applescript-users
Do not post admin requests to the list. They will be ignored.