topic RE: Excel Code Stage- Find and Replace in Product Forum

Excel Code Stage- Find and Replace

JordanWain — Fri, 05 Mar 2021 11:18:00 GMT

Hi,

Has anyone came across or used a Find and Replace excel code stage at all?

------------------------------
Jordan Wain
Blue Prism Developer
Shop Direct Group
Europe/London
------------------------------

RE: Excel Code Stage- Find and Replace

ewilson — Fri, 05 Mar 2021 14:42:00 GMT

Hi @Jordan Wain,

The current standard Excel VBO that ships with Blue Prism doesn't include a generic Find action, but it's something we can add. In the meantime, adding it via a Code stage is not terribly difficult. Here's a very simple example:

' Get the Excel instance.
Dim excel as Object = GetInstance(handle)

Dim range As Object
range = excel.Sheets(sheetName).UsedRange.Find(text)

If Not range Is Nothing Then
	cellref = range.Address
Else
	cellref = ""
End If

Notice there are three input parameters to the Code stage: handle, text, and sheetName. This assumes you're looking for the text value in a specific worksheet. If the value is found it will return the specific cell. There are several properties you can set within Excel. This is just the most basic option.

Cheers,
Eric

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Excel Code Stage- Find and Replace

ewilson — Fri, 05 Mar 2021 14:54:00 GMT

@Jordan Wain,

I just realized there is a Find action on the Excel - Extended VBO. You can find it here:

https://digitalexchange.blueprism.com/dx/entry/9648/solution/ms-excel-vbo---extended

I don't see a Replace method, but you should be able to implement that fairly easily once you have the cell references for where your search text is found.

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Excel Code Stage- Find and Replace

ewilson — Fri, 05 Mar 2021 23:36:00 GMT

@Jordan Wain,

One last update on this. I went ahead and had new actions added to the core MS Excel VBO. The actions are Find in Worksheet and Replace in Worksheet. I believe they'll give you what you're looking for. You can get the latest version of the core VBO here:

https://digitalexchange.blueprism.com/dx/entry/9648/solution/ms-excel-blue-prism-utility

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Excel Code Stage- Find and Replace

PvD_SE — Tue, 09 Mar 2021 10:48:00 GMT

Hi Eric,

Good work! (...and speedy too!)
I was planning to download it but lack access to the link (as usual...). Still have a question though: Is the modification to the VBO tied to a specific version of BP?

------------------------------
Happy coding!
Paul
Sweden
------------------------------

RE: Excel Code Stage- Find and Replace

ewilson — Tue, 09 Mar 2021 11:17:00 GMT

Hi @PvD_SE,

The changes were developed in v6,.9 of Blue Prism, but we ran the exported XML through the Studio Display Fix utility, also available on the DX. So, while I haven't specifically tested every version of BP, this should be usable back to v6 I imagine.

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Excel Code Stage- Find and Replace

Satyanarayanach — Sun, 27 Jun 2021 21:21:00 GMT

Hi Eric,

could you please advice how can i get the Replace in Worksheet excel VBO. i am unable to download due to access denied. we are in need as our windows 10 upgrade impacted lot of our process excel spying objects, and we are in the process of removing spying objects and use default Excel VBO.

could you please provide the code for : Replace in Worksheet, so i can modify the Excel Vbo .

Thanks,
Satya

------------------------------
Satyanarayana chinnapareddy
------------------------------

RE: Excel Code Stage- Find and Replace

ewilson — Mon, 28 Jun 2021 12:44:00 GMT

Hi @Satyanarayanach,

Is the access denied message being displayed by the Digital Exchange, or your corporate firewall? Were you logged into the DX?

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Excel Code Stage- Find and Replace

Satyanarayanach — Mon, 28 Jun 2021 12:51:00 GMT

Hi Eric,

Thanks for your response.

i was able to login into DX and able to download other assets except the below link:

https://digitalexchange.blueprism.com/dx/entry/9648/solution/ms-excel-blue-prism-utility

i even logged a ticket as well, still waiting for support team response.

Thanks,
Satya

------------------------------
Satyanarayana chinnapareddy
------------------------------

RE: Excel Code Stage- Find and Replace

pboutry — Wed, 30 Jun 2021 11:30:00 GMT

Hi,

Got the same problem. Access denied to https://digitalexchange.blueprism.com/dx/entry/9648/solution/ms-excel-blue-prism-utility
No problem to download another assets

br

------------------------------
philippe boutry
Blueprism Solution designer
Aubay
Europe/Paris
------------------------------

RE: Excel Code Stage- Find and Replace

ewilson — Wed, 30 Jun 2021 12:03:00 GMT

The link for the MS Excel VBO has been updated. You can access the VBO at the following link:

https://digitalexchange.blueprism.com/dx/entry/3439/solution/ms-excel-vbo

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Excel Code Stage- Find and Replace

salman_shaik — Mon, 08 Aug 2022 19:15:00 GMT

Hi Eric Wilson,
I tried to use replace worksheet action provided in excel vbo but its not working its replacing the entire worksheet with the new text value.
suppose i want to replace * with null in the worksheet then this action replaces all the cell values to null.
can you please provide a solution for this?

------------------------------
Salman Shaik
------------------------------

RE: Excel Code Stage- Find and Replace

ewilson — Tue, 09 Aug 2022 13:44:00 GMT

Hello @Salman Shaik,

The functions worked as designed. The issue you're encountering is that "*" is a standard wildcard character in Excel as is "?". To search for the actual "*" character as opposed to using it as a wildcard you should try prepending a tilde "~" to the asterisks, so your search text will actually be "~*".

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------