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

Help
, ,
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 Likes
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.

8

Hello,

I’ve a very similar problem like on this case but on my side I’ll need instead to write some information on the right cell that I need to find out.

Is there anyone around here who knows? Since this question was not solved in 1 year…

1 Like
9

Excel Cell Position.zip (19.0 KB)

@samureira

Try this above workflow. I think it might give u some insight.

Hope it works for you.

Cheers
Aryan

1 Like
10

Hi,
I have similar issue.
i want to get transaction item from Queue item and put it back into excel file as a final result. but the result is always get the last queue items and write to excel.
i use write cell activity to write the data, but i don’t know how to set the range to be match with the cell position of the Items. Anyone can help me? Thanks
Rgrds,
Ann