Find a cell position in Data Table

Hei!

I would like to ask you if it is possible to find an exact position of a cell in Excel. I have all data from Excel file in a proper variable and I am able to find if this file contains specific word/number/value. However, I need to know where in my excel file this word/number/value is (for example, Sheet: ABC, Cell: A12). Is it possible?

Thank you!

Tomas

Hi Tomas,

Yes you can do this.
You will need the row number within the DataTable and the column Index.

To get the row number, you can simply have a counter inside a loop (but that can be slow). If you are using vb.net to pull out the row that contains a word then you can use the .IndexOf() function, I believe.

For Example,
rowIndex =(datatableVariable.Rows.IndexOf(datatableVariable.AsEnumerable().Where(Function(row) row(0).ToString.Contains(“word”)).ToArray()(0))+1).ToString

Then you need the column index, so you can hardcode that or go by the Column Name.
For Example,
columnIndex = (datatable.Columns.Item(“ColumnName”).Ordinal+1).ToString

Finally, you need to convert the column index to an alpha.
There are a few ways you can do this. I suggest looking around this forum for options.
To simply convert a number from 1 to 26, A to Z, you can do Convert.ToChar(columnIndex + 64) but converting to AA-ZZ adds some additional logic.

Another simple way is to create an array of characters like alphas={“A”,“B”,…,“Z”,“AA”,“AB”…}
, Then you can take the columnIndex and pull the alpha.
For Example,
alphas(columnIndex-1)

In the end, you will combine the 2 in the range field
alphas(columnIndex-1)+rowIndex

As for your sheet name, you should store that in a variable before you Read the Range so you can use it again. You can also use a WorkbookApplication variable (from your Excel Scope) and extract the Sheet from the array of sheets based on certain words.
For Example, (with wb as your WorkbookApplication variable)
wb.GetSheets(0) will get the first sheet and wb.GetSheets(wb.GetSheets.Count-1) will get the last sheet. Or you can use vb.net to filter the array on certain words, like wb.GetSheets.Where(Function(s) s.ToString.StartsWith(“abc”)).ToArray().OrderByDescending(Function(s) s)(0)

um yeah.
Hope that helps!

Clayton.

23 Likes

Hi ClaytonM,

I have the same question to ask.
How to find a cell position in DataTable.
I am beginning in Uipath. So after review
your help, I don’t know how to do like you.
I tried to test before. But don’t have result. I use loop each row and
when the value of cell is “S.TL”, it will show message box
position’s information.
Please check to help.
TestFindPosition.xaml (15.1 KB)
002090_000003_A.xlsx (150.2 KB)

Thanks you

Hi Clayton,

Do u have xaml files related getsheets count

I have one query :slight_smile:
How to clear the contetnt of whole column of datatable.

That will remove the column , i dont want to remove the column i just want to clear the values of that column for each row.

1 Like

Hi @Suchi3190,

use for each row activity.

inside you can use for each activity to loop through the column values.

item(ColumnIndex)=String.empty

Regards,
Arivu

Hi @arivu96

Its not working :frowning:

HI @Suchi3190,

Refer this xaml file.

DataTableColumnValuesToEmpty.xaml (10.8 KB)

Regards,
Arivu

Hello,

Its not working :frowning:

I am using the file as dictionary and that file column i want to clear.
is it problem?

Hi @Suchi3190,

Use for each row activity.

Inside the for each row use assign activity
row("value")=string.empty
Or
row("value")=""

Regards,
Arivu

I tried it… its not working :frowning:

Hi @Rajtk7,

Check the following Workflow, you might get an Idea.

Find Cell Position.xaml (16.3 KB)
Sample_data.xlsx (8.9 KB)

Hello , I had a similar kind of problem which was resolved using this. Thanks :slight_smile:

its done by using some other workaround.Main.xaml (11.7 KB)

1 Like

@Suchi3190

Can you please post the query here.

Because you told the file is in dictionary format.

Regards,
Mahesh

Hi,

Please refer this topic below if you want to find a cell position in excel file with “invoke VBA”:

Hi can you help me in assigning a value to a cell in data table and I know the column name and row index

Hi @harinathreddy.yn,
use assign activity

dt.rows(rowvalue)("Columnname")="your value"

Regards,
Arivu