"Invoke Macro": can't run macro, when two workbooks are opened

[annikiforov]

Scenario:

1. Open two workbooks (in example, newly created files; refer to them as wb1, wb2).
2. Try to invoke VBA macro from .vbs file (may be just an empty sub) within excel scope of wb1.
3. Invoke fails with an error “Cannot run the macro ‘Main’. The macro may not be available in this workbook or all macros may be disabled.”

Error does not appear (i.e. Invoke succeeds) in the following scenarios:

• we open only one workbook
• in step 2 excel scope of wb2 is used.

My thoughts:
When running invoke VBA from step 2, .vbs module is copied to workbook 1. Then I guess, activity tries to call the macro within the latest opened workbook (wb2) and certainly fails.

Steps to reproduce:

Current Behavior:

Expected Behavior:

Studio/Robot/Orchestrator Version: 2019.4.2 Community Edition

Last stable behavior:
Last stable version:
OS Version: Windows 10 Enterprise
Others if Relevant: (workflow, logs, .net version, service pack, etc):

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full\Version = 4.7.02556

BugInvokeMacro.zip (2.2 KB)

[annikiforov]

A palliative workaround:

1. Reserve some dummy excel file for calling macros.
2. When calling a macro, use application scope with this excel.
   2.1. Each time you need to invoke a macro, reopen dummy xlsx, i.e. it must be closed after each usage (excel may reorder workbooks according to some unknown to me logic. After reordering, dummy.xlsx is no longer the “latest” workbook and can’t be used for invokation, as in bug description).
3. Don’t rely on active workbook in this case. Specify the needed workbook with parameters.

Please, refer to screenshot.