Traverse through all cells in a sheet to read their value & formulas

Help Studio
, ,
1

Hello,

I have a task where bot has to:

In every sheet read from A1 to very last cell in last column.

Following are the conditions to give context:

  1. No headers.
  2. No info on count of columns & rows.
  3. Need to read value of each cell.
  4. Need to read formula of each cell.
  5. Buttons, merged cells, Pivot tables, Charts etc. anything can be present anywhere in any sheet in the workbook.

For this I can’t read stuff in Data table and loop through it because I also need to read formulas from cells.

Please suggest logic to implement this. Especially logic on how to identify and traverse through the used range of cells across all columns for all type of possible data in them?

Suggestions will be greatly appreciated.

2

@shubhamkumar.tiwari,

You will have to find last row and column from excel file

You can read the data into DataTable for traversing purpose as it will give you number of rows and number of columns.

Using this you can easily loop through all the rows and column as you require.

Thanks,
Ashok :slight_smile:

3

I am done with part of getting rows and columns count from datatable.

Also, I am able to convert rows and columns in single letter cells, A1, A2,…etc.

but how to go ahead for double lettered columns?
Common code to work with both single/double letter cells. So that once I have cell address A1 or AA1 I can use that address in read cell & read cell formula activity directly.

4

grafik
And
grafik

1 Like
5

Wow…Didn’t know this is possible.

Thanks Peter.

May I know how you guys find this stuff since I never have seen any documentation for such stuffs. Does it comes with experience or?

6

indeed such shortcuts do come a little bit silent.

  • Release Notes do announce sometimes new functionalities
  • Forum knowledge sharings do multiplying such things
  • RnDs and explorations
1 Like
7

Thank you for your inputs Peter.

Lastly, It would be great if I can have a code snippet working behind this utility which converts column index to alphabets to satisfy my curiosity.

8

maybe this will serve:

1 Like
9

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