Excel Code Stage- Find and Replace

[JordanWain]

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

[ewilson]

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

[ewilson]

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

[ewilson]

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

[PvD_SE]

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

[ewilson]

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

[Satyanarayanach]

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

[ewilson]

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

[Satyanarayanach]

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

[philippeboutry]

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