Find a cell position in Data Table

Help
2

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