Excel activities do not support R1C1 Reference Style

excel

#1

In excel there are 2 ways of specifying a range

  1. A2:C10
     This traditional style is supported.
     Secifies the cells from Column A row 2 till Column C row 10
    
  2. R2C1:R10C3
     This is a R1C1 reference style which is supported by excel.[Screenshot attached]
     But, when this style range is specified in the read range activity, an error is thrown which says -` `**`Invalid Address format`**``
    

If an address format is supported by excel, I expect UiPath should also have supported it.

For huge excel inputs it is not always that we can have datatables created for entire ranges. Also, when there is a need for reading custom ranges within loops, conversion from iteration variables to Column names comes into picture(Eg. variable value 3 should get converted to Column C & value 28 to AB)(this further varies when someone’s indexing starts from 0 which means 3 = D and 28 = AC)
This R1C1 Reference style of specifying range will definitely be a huge help. wouldn’t it?

@badita @Sachin_Desai - please help with this issue


#2

A small work-around that I thought of for this is the use of ASCII codes:
ex: you have the code 65 for capital A and you can use CHR(65+variable)+rownumber.ToString to enter any range you need.
Hope this helps


#3

Thanks for the response but, this will only work till (65+25). What for further referencing?
Instead of developing a xaml for this kind of conversion which will be universally required for all excels, I expect that UiPath should have supported the R1C1 reference style which is already available in excel.


#4

I was inspired by this idea and wrote the code.
With this code you can convert numbers to column symbols.
Use “Invoke code”
++++++
Dim colstr As String
Dim modn As Integer

Do
num = num - 1
modn = num Mod 26
'65=ASCII A
colstr = chr(65+modn) & colstr
num = CInt(Math.truncate(num / 26))
Loop While num > 0
col=colstr
++++++
Arguments
num in int32
str out String