Excel Macro with Blue prism

MayankGoyal2 · ‎14-08-20

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

ewilson · ‎25-08-20

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

View answer in original post

nobu · ‎17-08-20

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

MayankGoyal2 · ‎17-08-20

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

nobu · ‎19-08-20

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

MayankGoyal2 · ‎19-08-20

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

nobu · ‎20-08-20

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

MayankGoyal2 · ‎21-08-20

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

ewilson · ‎21-08-20

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

MayankGoyal2 · ‎22-08-20

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

MayankGoyal2 · ‎22-08-20

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

SS&C Blue Prism Community

Excel Macro with Blue prism