Answered

Running Excel VBA script through Power Automate generates "Subscript out of range"

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have created a flow as follows:

This flow basically opens SAP, opens an excel file and runs the VBA script in that excel file, and then closes the excel file.

When I run the script without Power Automate, the script runs perfectly without any errors. When I try to run the script through Power Automate, it always gives me an error "Subscript out of range".

I tried using the run macro in the Power Automate, but it gives the same result. I am not sure what exactly is the problem.

The error sub in VBA is this:

'Sub to close Workbook Inventory on Hand

Sub Close_WBIOH()

Application.DisplayAlerts = False

Dim wbIOH As Workbook
Dim wsIOH As Worksheet
Dim rngcpy As Range

Set wbIOH = Workbooks("Inventory on Hand.xlsx")
Set wsIOH = wbIOH.Worksheets("Sheet1")
Set rngcpy = wsIOH.Range("A2", Range("A2").End(xlDown).End(xlToRight))

rngcpy.Copy

wbIOH.Close

Application.DisplayAlerts = True

End Sub

The line on which the error occurs is:

Set wbIOH = Workbooks("Inventory on Hand.xlsx")

Anyone knows why is this happening? Am I doing something wrong here?

Thank you.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (7)

Answers (2)

Sort by

Verified answer

Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

In thread Running an excel macro in PAD - Power Platform Community (microsoft.com), fellow users discuss that it's not possible to run a macro in an excel file different than the one that contains the code without workarounds.
I guess this is the case, as your macro triggers another workbook (the line on which the error occurs - 'Inventory on Hand').
In that thread, @MichaelAnnis suggests a workaround. Another one is 'transfer' the macro to 'Inventory on Hand' file (obviously with proper adjusts) and call this file/macro instead.

Was this reply helpful? Yes No
Verified answer

MichaelAnnis 5,727 Moderator on at

Like (2)

Report
Copy link

Link copied!

Is "Launch MA_Excel" your macro workbook?
And then what? You click on a button "Rectangle Rounded Corners 2" to activate the macro?

Is the Inventory on Hand workbook already open when this macro starts? If so, when Power Automate opens the Macro workbook, they are going to be in 2 different instances of Excel, and the macro will not be able to find the Inventory on Hand workbook.

If so, the solution would be to make sure the Inventory on Hand workbook is closed when the macro start, and re-write the VBA macro to open, copy the range, and close the Inventory on Hand workbook, because if the macro opens it, then it will be in the same instance and accessible to the macro.

You don't have this problem when manually doing it, because if you manually open a new workbook, it automatically attaches to an open instance of Excel, but Power Automate always opens a new instance when using "Launch Excel," even if you give it the same instance variable.

Best of Luck!

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hello @Anonymous, thank you for your reply.

Unfortunately, I cannot add code to the file because the file is generated anew by a different code every time I run the macro. I will try the solution mentioned by @MichaelAnnis and see if it works for me.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hello @MichaelAnnis, thank you for your reply. The "Inventory on Hand" workbook is downloaded by the VBA code and then it automatically opens. I found a way around this so will try that first and see if that helps me run the macro automatically.

Thank you!

Was this reply helpful? Yes No
Betaboy 29 on at

Like (0)

Report
Copy link

Link copied!
revisit this line of code.

Set rngcpy = wsIOH.Range("A2", Range("A2").End(xlDown).End(xlToRight))
what is your use case for this line?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hello @MichaelAnnis, I made changes in the code to close the open WO and then reopen it and it works.

Even though this is very redundant, I guess until Microsoft makes updates this is the only way I can run the code.

Thank you for your help!

Was this reply helpful? Yes No
Betaboy 29 on at

Like (1)

Report
Copy link

Link copied!

my suggestion is store the vba script as a addin and store with a short cut key.

so whenever you open the excel, you can just use "send keys" method to trigger the vba script.

Was this reply helpful? Yes No