topic RE: How to choose DAT file on clicking of Macro in Excel in Digital Exchange

How to choose DAT file on clicking of Macro in Excel

pmittal — Wed, 06 Mar 2024 17:09:00 GMT

Hi Everyone

I'm looking for a way to load the DAT file when run a macro action opens a pop-up dialog box to select the DAT file using Blue Prism Process Studio or Object Studio.

I'm able to run a macro using MS Excel VBO action but am not able to find a way to pass the DAT file path without spying.

If I need to use SPY then sometimes it can break.

Is there any other possible way to handle this situation?

------------------------------
Prashant Mittal
------------------------------

RE: How to choose DAT file on clicking of Macro in Excel

harish.mogulluri — Wed, 06 Mar 2024 19:12:00 GMT

HI Prasanth Mittal

I have worked on couple of automation similar to this but it is to save or save as window to save the file, instead of opening the file, the way we handled that is by spying the window explorer window and sending the full path in the filename box highlighted in the screenshot.

Looking forward to see if some other members in the community, tried different approach to handle this with out spying.

------------------------------
-----------------------
If I answered your query. Please mark it as the "Best Answer"

Harish Mogulluri
Lead developer
America/New_York TX
------------------------------

RE: How to choose DAT file on clicking of Macro in Excel

Daniel_Sanhueza — Wed, 06 Mar 2024 20:05:00 GMT

Hello Prasanth Mittal,

The thing is when running the macro with the MS Excel VBO action and a popup is displayed, Blue Prism hangs until the macro is finished, that means the macro is waiting for someone to type the path and save the file. This has occurred to me on numerous occasions, so the main approach I've taken is similar to what @Harish Mogulluri has implemented.

Once, while experimenting with process and run modes, I developed Process A(Foreground) to handle everything related to an Excel file with a macro, and Process B(Background) was designed to constantly 'listen' for a macro popup to display, both running simultaneously on the same resource. When Process A executed a macro, Process B handled it successfully, as tested in the Control Room.

Another approach involves editing those macros in their code to prevent any popups from displaying and to send paths to a local folder (e.g., C:\...), but this requires proficiency in VBA coding. However, sometimes clients prefer not to modify macros, so caution is necessary in such cases.

Has anyone attempted different approaches?

------------------------------
Daniel Sanhueza
RPA Professional Developer
Deloitte
America/Santiago
------------------------------

RE: How to choose DAT file on clicking of Macro in Excel

pmittal — Wed, 06 Mar 2024 20:41:00 GMT

Hi Daniel,

Could you please give more info on Process B Background Listener for a pop-up to display?
I have created an object that is window-based spy to identify the pop-up and enter the file path to open after clicking on the macro icon.
But I'm unable to identify to run both at the same time.

------------------------------
Prashant Mittal
------------------------------

RE: How to choose DAT file on clicking of Macro in Excel

Daniel_Sanhueza — Wed, 06 Mar 2024 21:18:00 GMT

It's a complex topic to delve into, but essentially, with run modes, you can have multiple instances running on the same resource simultaneously. I'll leave this link here that explains very well what run modes are about: how-determined-processes-run-mode-in-blueprism. However, I advise you to check the documentation regarding run modes because it's far more complex than what I've mentioned. It only takes one object in exclusive run mode on your process to prevent you from running another instance.

Regarding your issue, if you have an object that enters the path and saves it correctly, then you are fine. I suggest not using the MS Excel VBO to run the macro. Think of Excel as another platform to run the macro. What I used in the past was "Utility - Environment:Start Process" and gave input parameters with the process Excel and the path of the Excel file but also you can open the Excel using the MS Excel VBO, but don't use run macro action because it will hang. Create another action to click the button that runs the macro, and then call the action that you've already built, and you should be good. There's a lot of ways to reach the desire result.
Regards!

------------------------------
Daniel Sanhueza
RPA Professional Developer
Deloitte
America/Santiago
------------------------------