Running macro with Power Automate Desktop

[Justplainj]

Hi All,

I am using power automate desktop on windows 10 with office 365
I am running a flow that is splitting a large workbook into several smaller workbooks by using the manager name as the qualifying criteria.
This results in separate workbooks only containing the employees that report to that specific manager. 1 workbook per manager.
I use a template workbook and the data is formatted as a table.

therefore the flow at a very high level is.
1. open main workbook > find manager column as list > remove duplicate
2. open template workbook > copy rows that equal first manger as column as list > save template as new name
3. loop until all managers in column as list has been processed.

One of the columns however in the main workbook has a formula in it and when the split occurs the formula now points to the original workbook instead of the new workbook.
I cannot include the formula in the template as the copy over just replaces it.

I have therefore resulted to writing a simple macro that will just replace the formula entirely before saving the template as a new workbook.

The problem that I am having is that power automate desktop will not run the macro.
I keep getting the following error.
Failed to run macro (make sure the macro containing file isn't included in the disabled items list).

I have done everything can think of include.
1. Making sure power automate enables macros in the Launch Excel section on power automate
2. Macros are enabled in the Trust centre
3. The File location is added as a trusted location
4. Add-Ins are disabled
5. Replacing the workbook macro name with ThisWorkbook.MacroName or Sheet1.MacroName

I am unable to get the macro to run.

Any assistance or other suggestion to get the formula to corrected will be helpful.

PS. Some more info on the formula.
The Formula:
=IFERROR(LET(
A,VLOOKUP(W2,'Data Tab'!$K$3:$O$10,2,FALSE),
B,VLOOKUP(W2,'Data Tab'!$Q$3:$U$10,2,FALSE),
C,VLOOKUP(W2,'Data Tab'!$W$3:$AA$10,2,FALSE),
IF(OR(V2="A",V2="B Lower",V2="B Upper"),A,
IF(OR(V2="C Lower",V2="C Upper",V2="D Lower"),B,C))
),"")

The sheet name Data Tab is within the template also.
It pulls through a specific range based on the employees grade.

Thanks.
J

 

[Rajneesh Rawat]

Hi,

You need to mention your Macro name only.

Simply drag the action "Run Excel Macro" from PAD(Power Auto Desktop) and mention your macro name in textbox "Macro". For Ex- If my module is "Sub Get_ID" then I will mention in PAD as Get_ID.

regards
Rajneesh