Excel: Find Value

[jgregor4]

The action will be to open an Excel file and then find a value so that I can then extract which column the value is in.  The issue is that I will get a list of cases to work but will need to format account numbers before pulling into Blue Prism.  My concern is that at present the account number column is in "H" however if additional fields get added/removed then it may move from "H" to "G" and then the format action will no longer work. I have looked at the VBO but all the actions seem to need you to specify a cell location to go to rather than using a search/find type function.  Would anyone have an idea of how to do this? I have searched to try and find a simple code stage I could add to a custom Excel VBO but haven't seen anything that appears to be straight forward

[zdenek.kabatek]

Hi, jgregor,   here is Find action you are looking for. Put this into the code stage. It expects three parameters as input 1. handle 2. range to be searched 3. string to search for It generates a collection as an output which contains cell reference(s) where it found the string which you search. So in the process you check whether the collection has got any row if yes, you found the string if it is empty no match was found. Dim Instance As Object = GetInstance(handle) Dim wb As Object = Instance.ActiveWorkBook  Dim ws as Object = wb.ActiveSheet Dim xlValues as Integer = -4163 Dim cellref as Object = Instance.ActiveCell Dim firstAddress as Object = Instance.ActiveCell Dim TableRowExists as Boolean = False Dim table As New DataTable() table.Columns.Add(""Column1"") Dim row as DataRow = table.NewRow() ws.Activate() With ws.Range(range)     cellref = .Find(String_To_Search,Lookin:=xlValues)          If Not cellref Is Nothing Then          TableRowExists = True         firstAddress = cellref         table.Rows.Add(cellref.Address(False,False))         Do              cellref = .FindNext(cellref)              table.Rows.Add(cellref.Address(False,False))         Loop While Not cellref Is Nothing And cellref.Address(False,False) firstAddress.Address(False,False)          else '        MsgBox(""Cell not found."")     End If  End With If TableRowExists then      table.Rows.RemoveAt(table.Rows.Count - 1) End If Found_Cells = table  

[AndreyKudinov]

My Sheet.Find, for multiple result can be use recursively, setting ""after"" to last result in: handle,workbookname,worksheetname,What(Text: search string),After(Text: cell address),xlWhole(Number: 1 - whole cell, 2 - partial match) out: sheetexists (Flag), address(Text) Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname,False) address = """" sheetexists = ws IsNot Nothing If sheetexists Then     Try         address = ws.UsedRange.Find(What,ws.Range(After),Type.Missing,xlWhole).Address()     Catch ex As Exception         address = ws.UsedRange.Find(What,ws.UsedRange.Cells(1),Type.Missing,xlWhole).Address()     End Try End If