topic Re: How to get location from "MS Excel VBO-Find in Worksheet" in Digital Exchange

How to get location from "MS Excel VBO-Find in Worksheet"

jiyeon_gim — Thu, 27 Mar 2025 04:04:29 GMT

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.

https://digitalexchange.blueprism.com/dx/search?keyword=%22Blue%20Prism%20Enterprise%20-%20Core%22&page=1

Re: How to get location from "MS Excel VBO-Find in Worksheet"

Michael_S — Fri, 28 Mar 2025 10:01:36 GMT

Hi @jiyeon_gim - just to let you know, I've moved your question to our Digital Exchange forum where you're more likely to get a helpful answer about VBOs.

Re: How to get location from "MS Excel VBO-Find in Worksheet"

Mohamad_Dakkouri — Sat, 29 Mar 2025 21:06:38 GMT

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

Re: How to get location from "MS Excel VBO-Find in Worksheet"

jiyeon_gim — Tue, 01 Apr 2025 05:34:00 GMT

@Mohamad_Dakkouri Thank you for the information! This is what I am looking for!

I have an additional question.
Can I get the results that exact match with the search text?
(For example, If I put search text as "Harris", I want to get only $B$19 (the location of "Harris"),
not $B$20 (the location of "Harrison")).

Re: How to get location from "MS Excel VBO-Find in Worksheet"

Mohamad_Dakkouri — Tue, 01 Apr 2025 06:30:35 GMT

@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 :

----------------------------------------------------------

Dim wb, ws, excel, sheet, range, cell As Object

Dim firstAddress as String

Dim cells as new DataTable()

wb = GetWorkbook(Handle, Workbook)

ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()

ws.Activate()

excel = ws.Application

sheet = excel.ActiveSheet

' Build the DataTable/Collection definition.

cells.Columns.Add("Cell", GetType(String))

' What range do we search?

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.

' The initial char of the Search In value (capitalised)

Dim searchLoc as String = searchIn.Trim().Substring(0,1).ToUpper()

' The excel enum representing the XlFindLookIn enum.

' Note: setting to zero is just to nix a duff compiler warning

Dim xlFLI as Integer = 0

' xlFindLookIn enum values from:

' https://docs.microsoft.com/en-us/office/vba/api/excel.xlfindlookin

' Get 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

With range

cell = .Find(What:=searchText, LookIn:=xlFLI, SearchOrder:=order, MatchCase:=matchCase)

If Not cell Is Nothing Then

firstAddress = cell.Address

If Trim(cell.Value) = Trim(searchText) Then

cells.Rows.Add(cell.Address)

End If

cell = .FindNext(cell)

If cell Is Nothing Or cell.Address = firstAddress Then

Exit Do

End If

Loop

End If

End With

Matches = cells

Catch e As Exception

Success = False

Message = e.Message

Finally

wb = Nothing

ws = Nothing

excel = Nothing

sheet = Nothing

cell = Nothing

End Try

----------------------------------------------------------

- 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 🙂

Re: How to get location from "MS Excel VBO-Find in Worksheet"

faheemsd — Tue, 01 Apr 2025 12:33:33 GMT

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

Re: How to get location from "MS Excel VBO-Find in Worksheet"

Michael_S — Tue, 01 Apr 2025 12:36:02 GMT

@faheemsd @Mohamad_Dakkouri -> WOW. Amazing responses, thank you for being so helpful!

@jiyeon_gim - let us know if this gets you past your blocker 🙂

Re: How to get location from "MS Excel VBO-Find in Worksheet"

jiyeon_gim — Thu, 03 Apr 2025 06:50:29 GMT

@Mohamad_Dakkouri @faheemsd @Michael_S Thank you for your help!

I could make another object and fix my problem!