How to get the range of data present in excel

Help Studio
1

i need to get the range of the data present in the excel sheets whose range varies, so that i can write the formula to each cell present in the excel sheet

1 Like
2

Hey @jeevan.h

You can first do a read range with empty range which will read the entire range which has data.

Then you can manipulate the address with current row and column index.

Let’s say you have a match in particular row and column index,

Char(64+ColIndex).ToString + RowIndex.ToString

We are using ASCII for creating alphabets in the above address range manipulation

Hope this helps

Thanks
#nK

3

Welcome to the UiPath Community @jeevan.h

You can read the entire data ij that Excel as mentioned by @Nithinkrishna

Then the rows will be simply the dt.rows.count

And first get the number of columns using, dt.columns.count

And then pass that count in the Excel formula (either in a cell in new sheet on that file)
This will give the Excel address of the last column

=SUBSTITUTE(ADDRESS(1,column_couny,4),"1","")

Give it a try, this should work!

2 Likes
4

Just to understand, will this work if column count is more than 26?
As in Excel the column address after Z is AA

1 Like
5

Hey @rahulsharma

Yes of course, this won’t.

But that’s just the demo and if it’s more than 26 columns we need to add a bit of logic.

Thanks
#nK

6

formula works
but when i want to push the formula into excel and read the value. how is it possible it will again reaad the formula rite?

7

and its showing sytex error while i tried reading that cell

8

Write that into a new temporary sheet, read that entire sheet in a datatable using Read Range Workbook activity, and then use Output Datatable to convert it into a text.

Hope this helps!

9

How are you reading that cell?

10

image
image1280×720 83.8 KB

This is the formula i am writing, excel file is not taking the formula
its trying to remove the formula when trying to open excel file

11

Hi @jeevan.h ,

Could you try writing it using Excel Application Scope?

I’ve noticed that sometimes the file gets corrupted when we try to write formulas into Excel using the Workbook Activities.

Kind Regards,
Ashwin A.K

12

image
image1280×720 43.7 KB

i get this error while using excel application scope

13

It worked with application scope

14

It worked with your formula , thankyou

1 Like
15

Great!

Please mark the answer as solution so that others facing similar issues may benefit from it and also so as to close this thread.

Kind Regards,
Ashwin A.K

16

Glad it worked. Mark the post as solution to close the thread.

17

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.