On Feb 8, 2009, at 1:48 PM, Paul Berkowitz wrote: On 2/8/09 8:37 AM, "Deivy Marck Petrescu" <email@hidden> wrote:
I am scripting Excel X.
I understand that it is an old version but it does what I need.
There are some quirks with it and I'd like to know if these problems are still there in the new versions.
Problem 1:
Have an open Excel document.
The following script will not do what one expects:
---
Tell app "Microsoft Excel"
set value of Cell "A1" to 1 --sets value of cell a1 to 1
set value of Cell "B1" to 2 --sets value of cell b1 to 2
set value of Cell "C1" to 3 --sets value of every cell of column 1 to 3
end tell
--
No, this problem does not exist in Excel 2008. The value of A1 is 1, value of A2 is 2 and value of A3 is 3, and that's all.
First, thanks for the reply. Paul, I do not know if it was a distraction or you checked the wrong information. The problem is when using "C1" for the value of the cell, it understands as "Column 1" and thus puts the value 3 in all cells of column 1. Has this been resolved?
Problem 2:
This one I can not really understand
I don't understand what you're working with here. Nor do I understand where the R3 comes from in the first statement of the script. What is "RC[-1]" meant to indicate and how does it end up as "R3" in the first example and as "RC[-1]" in the second example?
I don't know about the newer versions, but in excel X, you can refer to cell "A1" as "A1" or as "R1C1", the latter means "row 1 column 1". So, to put the value in any cell, one can select either is customary excel name, "B1", Z10" or the location by row and column. One problem is everything is fine except for cells like "Cn" or "Rn" where n is a number. This, because excel understands those as "Column n" or "Row n".
Now suppose I select cell "A1" and I want to have "B1" as the same value of "A1". I can write the following, ------> set value of Cell "B1" to "=A1"
This will translate in a B1 cell as =$A$1.
Where the "$" indicates absolute reference. So if I drag down the value of cell "B1" to have the value of "B2" = "A2", I would get instead "B2"="A1". However, if if instead of the line above I use
------> set value of Cell "B1" to "=RC[1]"
This will translate it to "=A1" as a referencial position, instead of absolute position. That is, dragging down B1 would make "B2" = "A2" and so on. In other words, RC[1] means: the same row but next column. R[-2]C[2] means: 2 rows before the current row and 2 columns after the current column.
For someone not too familiar with =LOOKUP function, could you please describe what values are actually in T2:U6, what value you're trying to find ("RC[-1]" literally or something else?) and what exactly you would enter in the UI and what result you get, that you're trying to do by script. Thanks.
T2:U6 is a range of cells. The first cell is on "T2", the last in "U6". This means the range t2,t3,t4,t5,t6, u2,u3,u4,u5,u6. A bicolumnar range with 10 cells.
U2:U6 is a columnar reference to these five cells range. There is no UI scripting everything is excel scripting.
So, the R3 is right (I entered RC[-1] in S3, same row one column before), I d not understand why the -- ' -- show up. They were not supposed to be there.
---
set AC84 to (ASCII character (84))
set AC85 to (ASCII character (85))
set part1 to "(RC[-1]," & (AC84 & "2:" & AC85 & "6") & "," & (AC85 & "2:" & AC85 & "6)")
set part1 to (part1 as string)
--return (part1)
tell application "Microsoft Excel" to set Value of Cell "S3" to "=Lookup" & part1
--->=LOOKUP(R3,'T2':'U6','U2':'U6')
---
---
set AC84 to (ASCII character (84))
set AC85 to (ASCII character (85))
set part1 to "(RC[-1]," & (AC84 & "2:" & AC85 & "6") & "," & (AC85 & "2:" & AC85 & "6)")
set part1 to (part1 as string)
return (part1)
tell application "Microsoft Excel" to set Value of Cell "S3" to "=Lookup" & part1
-->"(RC[-1],T2:U6,U2:U6)"
---
So, if I check to see what is part1, I see from the script above that it is what I want, but when excel puts the formula in the cell it puts single quotes around the cell references!!!!
I have no clue on what is going on!
Any help or info on the new versions would be very appreciated.
Thanks.
Deivy Petrescu
email@hidden
Paul Berkowitz
|