How to find Cell position (row index and column index) on an Excel file?

datatable
excel
activities

#1

Hello,

I need to do a workflow with the following characteristics:

1º Reading the full Excel sheet [ already done with a simple Read Range Activity ]

2º Read just a portion of the sheet, where a certain word is found on a certain row. I need to keep the cell position saved once this word is found because then, I will need to make a new Read Range starting on that position.

So, basically I just managed to find the value or the existence of the word itself by doing a row(0).ToString = “WordsName”

Now, how can I detect the row index and column index from this word I found? Once I am able to do that, I will need to basically do the same to find the value of the other words whic will give me the position which will make me set the range of the new Read Range activity.

Any tips?


#2

Hey @moreirasa1

If you are checking condition row(0).ToString = “WordsName” means you already know the column index is 0! :smile:

For row index, if this condition is true ,assign the index in a variable using
dt.Rows.IndexOf(row)+2 assuming “dt” is the data table variable used in read range!


#3

Hello @Rishabh_Lakhera,

Yes, got misunderstood. I know the column index is 0. But I don’t know which letter from the excel cell it is. Column 0 could be “A”, “B”, “C” and so on.

Once my condition is valid, I need to to say which cell my word is. Only after that, I can do the new Read Range.

Thanks.


#4

Column index ‘0’ would mean A itself!
So if you know the column as 0 and you are hardcoding it, then you can hardcode “A” too!


#5

Well, not exactly.

I have an Excel file with the Column A cells empty and it only starts at Column B. When I use the Read Range activity and on the Range property I put “”, the Column 0 generated on the DataTable represents the Column B on my Excel file.


#6

Well i didn’t think about that! Good point :slight_smile:
So would you not be knowing if some columns are blank?
I mean if you’re manually making the file or extracting some data and storing inside it, you must be knowing which columns have data and which don’t?!


#7

Nope, I don’t know which columns will be blank. The files I get are generated by a web application and extracted as Excel files. This means that I have no power in which columns there will be data or not, it depends on the amount of results.

So, that’s why I first make a Read Range to get the full data on the sheet. I store that in a DataTable. Then I need to iterate through the DataTable and discover in which cell I get the first value I want which is identified by a keyword I know. Then, I will have a second keyword to identify which will be my ending cell to where I should make the read range.