Excel activities do not support R1C1 Reference Style

[akhi_s27 (Akhilesh S.)]

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

[PetreN (Petre Nistor)]

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

[akhi_s27 (Akhilesh S.)]

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.

[tanaka_yoshiro]

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

[patent-atanaka (Hiroaki Tanaka)]

It’s really useful tips! Useful when specifying a range of Excel activitie “Create Table”.

ColumnNumberToAlphabeticalDigit.zip (126.1 キロバイト)