Searching text in MS excel and getting the cell position as output.

[ashish.kushwaha_2185]

I am using MS EXCEL business object to open an excel and read contents from the same. I am able to read the entire worksheet as well particular cell value. For some business requirement I want to search particular text in the excel and get the cell position of that text in sheet. Text to be searched is unique so output I am expecting is only one cell/range.

[Anonymous]

The easiest way to do this (without any coding), would probaby be to iterate through both rows and columns in your document (assuming that the document does not contain too much information). You could make a loop inside of a loop - to explain, here is a code equivalent: Dim MyRow, MyColumn as integer For column=1 to 10 -----For row=1 to 100 ----------If cell(row, column) = ""MySearchText"" Then ---------------MyRow = row ---------------MyColumn = column ----------End If -----Next row Next column Two loop stages - one inside another - looping based on two collections (one with column letter, and one with row numbers) could be used as input for ""Get cell value""-page in the business object. As soon a you have a match, concatenate the values in the collections in a variable - and this will be the result.

[susheel_maurya]

Hi timmorthorst, I agree with you, Iterating is ok if you have small data but if you have too much data in excel it would take too much time to find text.So we can use ""Find"" method of ms excel object that is very fast, no matter how much data you have. You could make a new action in your existing VBO Object using below code to find address, rownumber or column number i.e. to find ""Description"" Text in excel file. DIM address as Object=cells.EntireRow.Find(""Description"", misValue, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlNext, True, misValue, misValue).Address Dim rowNumber as Object=cells.EntireRow.Find(""Description"", misValue, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlNext, True, misValue, misValue).Row Dim columnNumber as Object=cells.EntireRow.Find(""Description"", misValue, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlNext, True, misValue, misValue).Column also you can use enum value as parameter instead of enum name for same i.e. DIM address as Object =cells.EntireRow.Find(""Description"", misValue, -4163, 2, 2, 1, True, misValue, misValue).Address DIM address as Object =cells.EntireRow.Find(""Description"", misValue, -4163, 2, 2, 1, True, misValue, misValue).Row DIM address as Object =cells.EntireRow.Find(""Description"", misValue, -4163, 2, 2, 1, True, misValue, misValue).Column

[John__Carter]

Hi AshishAs Susheel has pointed out, extending the MS Excel object is the better option. However, I would advise you make a copy (Save As) of the original and add your functionality to the new object. That way when a new version of the original is issued, you won't have to overwrite your logic. I find the easiest way to create new excel logic is to record a macro first to see how the VBA works, then translate it into .Net code.