I'm trying to use "MS Excel VBO-Find in Worksheet"
But I find out it can only export Success(Flag), Message(Text), Matches(Collection).
Is there anything that I can do to get the location of the search text?
I downloaded this VBO from Blue Prism Digital Exchange.
Hi @jiyeon_gim
Normally in the marched coolection you will have all your needs
See example below :
i am searching Spencer here :
When i use find in worksheet i provide this :
and then in the matched collection i will have this :
So it give me the correct location.
Is that anwser your request or you need more details ?
Dont hesitate
@jiyeon_gim Sure 🙂
Follow these steps.
- Open your MS excel vbo that you have downloaded from DX
- Right Click on Find in worksheet then do duplicate
- Rename your new page "Find Entire Match in Worksheet" like this :
- open the code stage this one
- Rename it "Search for entire Text"
- Go to code
Put this :
----------------------------------------------------------
----------------------------------------------------------
- Publish your action and now run
see example below
i am looking for Haris.
I provide this
My matches give me the good answer
Try it and let us know with @Michael_S 🙂
Dear @jiyeon_gim
I have built the code to achieve your requirement.
Make a copy of Find in Worksheet and update the below code in code stage
Dim wb, ws, excel, sheet, range, cell As Object
Dim firstAddress As String
Dim cells As New DataTable()
' Initialize the DataTable to store cell addresses
cells.Columns.Add("Cell", GetType(String))
' Get the workbook and worksheet
wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)
' Activate the workbook and worksheet
wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
' Determine the search range
If (Len(Trim(searchRange)) > 0) Then
range = ws.Range(searchRange)
Else
range = ws.UsedRange
End If
' Set the search order value
Dim order As Integer = 0
If (searchOrder.Trim().Substring(0, 1).ToUpper() = "C") Then
order = 2 ' xlByColumns
Else
order = 1 ' xlByRows
End If
' Determine what type of data we'll be searching in (Comments, Formulas, or Values)
Dim searchLoc As String = searchIn.Trim().Substring(0, 1).ToUpper()
Dim xlFLI As Integer = 0
' Set the search location
Select Case searchLoc
Case "C"
xlFLI = -4144 ' Excel.XlFindLookIn.xlComments
Case "F"
xlFLI = -4123 ' Excel.XlFindLookIn.xlFormulas
Case Else
xlFLI = -4163 ' Excel.XlFindLookIn.xlValues
End Select
Try
Success = True
' Start searching in the defined range
With range
' Find the first occurrence of the exact match ("Haris")
cell = .Find(What:=searchText, LookIn:=xlFLI, SearchOrder:=order, MatchCase:=True)
' If an exact match is found, process it
If Not cell Is Nothing Then
firstAddress = cell.Address
Do
' Check if the cell value matches exactly "Haris"
If Trim(cell.Value) = searchText Then
' Add the exact match cell address to the DataTable (or collection)
cells.Rows.Add(cell.Address)
End If
' Continue searching for the next match
cell = .FindNext(cell)
' Exit loop if we have looped around to the first match
If cell.Address = firstAddress Then
Exit Do
End If
Loop While Not cell Is Nothing
End If
End With
' Set the Matches result to the collection or DataTable
Matches = cells
Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
cell = Nothing
End Try
To avoid error please rename the code stage and publish the page.
Now call the above action in MS Excel VBO, give the search name as "Haris" and now you will get only Haris cell locations as Output result
Please let us know if you face any difficulties in Implementing above.
In addition to it I'm attaching the Input Parameter descripts for the Find in Worksheet page
@Michael_S
The above code should help our Blue Prism users obtain the exact output results as cell locations
