how is the EXCEL ROW variable supposed to work

[philkryder]

I have a loop thru an excel spreadsheet. say from row 101 to 300.

within the loop, I step forward row by row and reference and use EXCEL COLUMNs

but,
I also reference SPECFIC cells like A17 and A18.

First off, is it legitimate to reference cells OUTSIDE of the GET CELLS RANGE?

Secondly,
what should the value of EXCEL ROW be when I am looping on row 105, but, I do a SPECIFIC cell reference back to CELL A17?

should it be
17
or
105 ?

[philkryder]

what should the message box in line 16 display



1 Delay: (200 ms)
2 Comment: -------? how should EXCEL CELL ROW work
3 Excel : Open Spreadsheet "H:\My Documents\Automation Anywhere\Automation Anywhere\$TestDataIncludingXYgridEXCEL\EXCELrow.xl sx". Active Sheet: Default. Session: Default
4 Delay: (200 ms)
5 Excel : Set value of Cell "A1" with "a1". Session: Default
6 Delay: (200 ms)
7 Excel : Set value of Cell "A10" with "a10". Session: Default
8 Delay: (200 ms)
9 Excel : Get multiple cells from cell "A10" to cell "B10". Session: Default
10 Delay: (200 ms)
11 Start Loop " Each row in an Excel dataset of Session = Default"
12 Variable Operation : $Excel Column(1)$ To $Clipboard$
13 Message Box: "*$Excel Cell Row$*"
14 Excel : Get value of cell "A1" and assign to variable "Clipboard". Session: Default
15 Comment: WHAT should be the value of EXCEL CELL ROW in the next MESSAGE box
16 Message Box: "*$Excel Cell Row$*"
17 Variable Operation : $Excel Column(1)$ To $Clipboard$
18 Comment: WHAT should be the value of EXCEL CELL ROW in the next MESSAGE box
19 Message Box: "*$Excel Cell Row$*"
20 End Loop
21 Delay: (200 ms)

[philkryder]

any help on this?

[forumsupport]

Hello,

Message box at line 16 would return 10. If within loop you refer the cell that is out of the specified range then also $Excel Cell Row$ will point to the current row of excel dataset.

Hope this helps.

[philkryder]

Originally Posted by forumsupport

Hello,

Message box at line 16 would return 10. If within loop you refer the cell that is out of the specified range then also $Excel Cell Row$ will point to the current row of excel dataset.

Hope this helps.

did you try running the code?

I too expected line 16 to display 10.
I also expected line 19 to display 10.
but,
they don't, at least on my machine.
they both display 1

once a specific cell is referenced, the value of Excel Cell Row contains the row number of that cell.

I had hoped that referencing a cell in the current row as is done on line 17, would "reset" Excel Cell Row - but, it doesn't seem to.

I ended up have to create a new ExcelCellRowSAVE variable and save the value of Excel Cell Row immediately upon entering the loop.

Please run the code and let us know if you see the same effect.

thanks much as always.
Phil



however,
line 16 AND LINE

[forumsupport]

Hello Phil,

We could see the similar effect at our end. Can you please try assigning the value of $Excel Cell Row$ to another variable at starting of loop. It works at our end. Text format would appear something like this,

1 Excel : Open Spreadsheet "C:\sample.xls". Active Sheet: Default. Session: Default
2 Excel : Get multiple cells from cell "A10" to cell "B12". Session: Default
3 Start Loop " Each row in an Excel dataset of Session = Default"
4 Variable Operation : $Excel Cell Row$ To $Prompt-Assignment$
5 Message Box: "$Excel Cell Row$"
6 Excel : Get value of cell "A1" and assign to variable "Clipboard". Session: Default
7 Message Box: "$Prompt-Assignment$"
8 Variable Operation : $Excel Column(1)$ To $Clipboard$
9 Message Box: "$Prompt-Assignment$"
10 End Loop

[philkryder]

Originally Posted by forumsupport

Hello Phil,

We could see the similar effect at our end. Can you please try assigning the value of $Excel Cell Row$ to another variable at starting of loop. It works at our end. Text format would appear something like this,

1 Excel : Open Spreadsheet "C:\sample.xls". Active Sheet: Default. Session: Default
2 Excel : Get multiple cells from cell "A10" to cell "B12". Session: Default
3 Start Loop " Each row in an Excel dataset of Session = Default"
4 Variable Operation : $Excel Cell Row$ To $Prompt-Assignment$
5 Message Box: "$Excel Cell Row$"
6 Excel : Get value of cell "A1" and assign to variable "Clipboard". Session: Default
7 Message Box: "$Prompt-Assignment$"
8 Variable Operation : $Excel Column(1)$ To $Clipboard$
9 Message Box: "$Prompt-Assignment$"
10 End Loop

Yes,
I agree that another variable does retain the value correctly if set before any other references.

I mentioned that I did that in my earlier post.


However, don't you agree that this is a bug that should be reported to development?

[forumsupport]

Hello Phil,

Yes, we did report this issue to development team.

[forumsupport]

Hello Phil,

The functional behavior of Excel command is, whenever any Excel command executes, it automatic set/reset system variables, ($Excel Cell Row$, $Excel Cell Column$). If user wants to preserve Excel Cell Row/Column then before using any Excel command must transfer current value to some custom variables.

[philkryder]

Originally Posted by forumsupport

Hello Phil,

The functional behavior of Excel command is, whenever any Excel command executes, it automatic set/reset system variables, ($Excel Cell Row$, $Excel Cell Column$)....

that makes sense -
but,
that doesn't seem to be happening.

look at the sample ATMN that I supplied.
I specifically referenced the current row again,
at line 17 in hopes that $Excel Cell Row$ would be reset to the current row.
but,
it wasn't.