topic RE: Excel Macro with Blue prism in Product Forum

Excel Macro with Blue prism

MayankGoyal2 — Fri, 14 Aug 2020 00:06:00 GMT

Hi,

We have a action to run macro from BP in excel VBO, the objects waits for macro to complete. If there is any error in macro, it goes into recover. Now when macro runs for long time like 20-30 min, BP is waiting on that action stage, however it crashes the BP and in control room it shows disconnected. Please let us know why this happens and how the same can be fixed?
Ideally BP should be waiting on action stage and whenever macro completes should move to next stage.

------------------------------
Mayank Goyal
------------------------------

RE: Excel Macro with Blue prism

nobu — Mon, 17 Aug 2020 03:38:00 GMT

Hi Mayank,

If Blue Prism was doing something (Calculating, Logging sessions, etc), it may possible get an out of memory issue during 20-30 minutes time. However, in this scenario, Blue Prism was simply waiting for the completion of the Action stage. I don't see any reason why Blue Prism was crashed. But if Blue Prism does some activities, you can check by enabling "Log Memory Usage" from System > Resources > Management > Right Click the runtime resource > Logging Level. The information will be taken in either

[BPASessionLog_NonUnicode].[automateworkingset]
[BPASessionLog_Unicode].[automateworkingset]

If there was large size of memory consumption (e.g., 700MB, 1GB), most likely this is the reason.

Alternatively, you may consider the design change since this is not effective way to use a robot just waiting for another automation has bene done for a long time. Maybe, the process can be divided to 2 parts.

Do something --> Run the macro --> End
Pickup the result of the macro --> do the next task --> End

Cheers,

Nobu

------------------------------
Nobuhiro Tokushige
Product Specialist
Blue Prism
Australia/Sydney
------------------------------

RE: Excel Macro with Blue prism

MayankGoyal2 — Mon, 17 Aug 2020 17:50:00 GMT

@Nobuhiro Tokushige - We have to run some steps after macro is completed and as soon as macro is completed, hence we want to keep it in one process. The steps are linked and decision has to be made based on success/error in macro. So the most suitable design in our case was to keep in one process.

On the other hand, you can try creating a very simple macro with a for loop and wait of 1 min in that loop so that it runs for 20 min. Call it in BP process and run process from control room from another machine. I believe you should be able to replicate the issue. It will show disconnected.

Let me know if you were able to find the root cause and a fix.

------------------------------
Mayank Goyal
------------------------------

RE: Excel Macro with Blue prism

nobu — Wed, 19 Aug 2020 04:02:00 GMT

Hi Mayank,

I crated a sample process based on your information. But the process worked without any issue.

The following image is my process design.

And the following is the screenshot when the process was completed. The runtime resource was still running and I don't see any memory issue.

Just for your reference, I'm sharing my test process and sample excel file. Please find attached files.

Cheers,

Nobu

------------------------------
Nobuhiro Tokushige
Product Specialist
Blue Prism
Australia/Sydney
------------------------------

RE: Excel Macro with Blue prism

MayankGoyal2 — Wed, 19 Aug 2020 05:24:00 GMT

@nobu - Thanks for your response, the intent was a little bit different, can you keep that for loop in macro rather than blue prism so that macro runs for 20 min rather than running for 1 min 20 times. I want blue prism to wait for 20 min on the box ("Run macro") during the time macro is running and shoukld move to next stage after 20 min. The session should show as running on control room when checked from other machine monitoring this process and not disconnected.

------------------------------
Mayank Goyal
------------------------------

RE: Excel Macro with Blue prism

nobu — Thu, 20 Aug 2020 06:15:00 GMT

Hi Mayank

I modified my process and excel macro running 20 minutes (not 1 minute x 20 by Blue Prism). As a result, Blue Prism could wait for the completion of macro.

I'm sharing the process, excel file and video file of my test. Please run the process and check how it goes in your system environment.

Cheers,

Nobu

------------------------------
Nobuhiro Tokushige
Product Specialist
Blue Prism
Australia/Sydney
------------------------------

RE: Excel Macro with Blue prism

MayankGoyal2 — Fri, 21 Aug 2020 05:06:00 GMT

@Nobuhiro Tokushige - Thanks for the updates, kindly suggest one more thing is there a updated action for this in newer version of blue prism which provides option of timeout as well?
The issue is if due to any reason macro got stuck and gives a pop up, BP will stuck for indefinite period of time unless someone goes in and hard kill the process from task manager.
If this action has a timeout option as well we can use the same and if BP doesn't get back control within time out period defined, it goes to exception.

------------------------------
Mayank Goyal
------------------------------

RE: Excel Macro with Blue prism

ewilson — Fri, 21 Aug 2020 13:10:00 GMT

@Mayank Goyal have you taken a look at the latest MS Excel VBO - Extended and the "Run Macro with Parallel Thread" action? It's basically a way that you can provide a timeout as well as a check for a pop-up dialog.

Cheers,

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

RE: Excel Macro with Blue prism

MayankGoyal2 — Sat, 22 Aug 2020 19:38:00 GMT

@ewilson - I downloaded this VBO some time back, however there are few things --
1) The action run macro with parallel thread is not published, not sure if its ready
2) It is not even mentioned in pdf guide for this VBO
3) I published it on my own and tried using it it gave me an error, please find attached the screenshot
4) How I assume this action is intended to work is - I will have to code a msgbox in my maco at end - something like automation completed, this action will run macro and it waits for that message box. If message box is displayed before timeout, action will click ok on message box and move to next stage else will give exception if no message box is displayed within timeout. Also I am not sure what it will do in case of any internal error within excel macro which shows a error box. How will it differentiate between completion popup and any internal excel error popup?

------------------------------
Mayank Goyal
------------------------------

RE: Excel Macro with Blue prism

MayankGoyal2 — Sat, 22 Aug 2020 19:42:00 GMT

@nobu - I executed one macro which was running for 20 min, attached is the screenshot of what I see in control room in between. It goes to warning status.

------------------------------
Mayank Goyal
------------------------------

RE: Excel Macro with Blue prism

nobu — Mon, 24 Aug 2020 01:24:00 GMT

Hi Mayank,

"Waring" message is expected behaviour. When it exceeded the timeout value, the message was shown in the control room. It is just a message for you which doesn't stop the process.

You can change the timeout value from 2 locations

System - Settings:
Each Stage:

FYI,
I still believe just waiting for a long time (e.g., 20 minutes) is not ideal way to utilise a robot. In this case, Advanced workqueue guide (section7 Design example) may provide a better solution. If you are interested in, please check the document from the Blue Prism portal site (https://portal.blueprism.com/)

Cheers,

Nobu

------------------------------
Nobuhiro Tokushige
Product Specialist
Blue Prism
Australia/Sydney
------------------------------

RE: Excel Macro with Blue prism

MayankGoyal2 — Tue, 25 Aug 2020 04:25:00 GMT

@ewilson - Any inputs on above issue with run macros with parallel thread ?

------------------------------
Mayank Goyal
------------------------------

RE: Excel Macro with Blue prism

ewilson — Tue, 25 Aug 2020 12:56:00 GMT

@Mayank Goyal I've updated the VBO so the action is published. I also updated the guide to include that action in the list. Seems those were oversights. As for the error you're seeing, I'm wondering if that tied to the macro you're trying to execute? There is no Dictionary used in the code stages of this action or the regular Run Macro action that this references.

As a test, I created a blank Excel workbook and added the below macro to it. Then I execute it from the VBO and everything worked fine.

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

Sub WasteTime()
    ' All this macro will do is run through a loop and then display a dialog.
    Dim NowTick As Long
    Dim EndTick As Long
    Dim Finish As Long
    
    Finish = 10
    
    EndTick = GetTickCount() + (Finish * 1000)
     
    Do
 
        NowTick = GetTickCount()
        DoEvents
 
    Loop Until NowTick >= EndTick
    
    MsgBox "We are finished looping!"
    
End Sub

Cheers,

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

RE: Excel Macro with Blue prism

MayankGoyal2 — Tue, 06 Oct 2020 07:17:00 GMT

@ewilson - Thanks for your response, just want to check if this Excel extended VBO includes all functionality from basic excel vbo as well and additional functionality on top of it?

------------------------------
Mayank Goyal
------------------------------

RE: Excel Macro with Blue prism

ewilson — Tue, 06 Oct 2020 10:12:00 GMT

@Mayank Goyal that's correct.

Cheers,

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

RE: Excel Macro with Blue prism

NileshJadhavBP — Tue, 06 Oct 2020 19:34:00 GMT

Hi Mayank,

If activity take 20-30 min that mean excel file is huge and you are processing lots of action with same data.

Please check or do below thing

if there are formula used in the excel file.Please convert that into value so file size will get reduce.
filter excel data and keep or take necessary data so your process will work fast.
Check VBA code & remove unnecessary loops and variables declaration.
Try to create and use function in VBA.
try to divide your main action in to small action.

i hope it will help you.

Thanks
Nilesh Jadhav.

------------------------------
Nilesh Jadhav
Senior RPA Specialist
------------------------------