Power Automate to Run Excel Macro

[coolbeans23]

I have a Excel macro that copies a specific sheet from the source workbook to a destination workbook, both are on SharePoint. The macro resides on the destination workbook. I wanted to create a scheduled flow to run the macro every business day but I'm having some issues.

My Power Automate version does not have "Run Excel Macro" action, so I am trying to use "Run Script" action which appears like it can be used to trigger a macro and not just a Office Script, and in the script field I tried a few things but it keeps giving the error that it's a Bad Request - unable to parse script reference.

I tried the following naming conventions for the script field:

Module1.CopySheet (Module name followed by procedure name)

Module1CopySheet (without the dot)
Daily Orders!Module1.CopySheet (Workbook name followed by module name followed by procedure name)

Daily Orders!Module1CopySheet (without the dot)

I even tried copying/pasting my entire macro to the script field but to no avail.

Can someone please advise on how I make this happen or if there is alternative method to run this routine job?

[AlexEncodian]

@coolbeans23 

You cannot run macros from Power Automate cloud, only Office Scripts. 
Use macro-enabled files in Power Automate flows

Build your solution as a script, alternatively, merge the desired sheet into the new file using Encodian's Merge Excel Files action.

Here's an example:

Merge Excel and CSV Files with Power Automate

[coolbeans23]

Hmm....two different AI's said you can run Excel macros from Power Automate on cloud (they could possibly be wrong) using "Run Excel Macro" or "Run Script" action (which I only have the latter in my version). I don't have Office Scripts.

It's not a matter of consolidating Excel files, that can be easily accomplished with Power Query or even using dynamic array functions (VSTACK). 

Perhaps I can fill you in on more detail, the source workbook has a specific sheet that is connected to a database and displays data where some needs to be corrected, a user remediates any incorrect data in the database then refreshes the Excel sheet to see their changes reflected. My goal is to capture changes to the data for each day of the month to see what has been corrected (if any).  My destination workbook has a macro that makes a static copy version of the sheet from the source workbook and creates a new tab with the current date i.e. Orders 4.20.2024, Orders 4.21.2024, Orders 4.22.2024, etc. I just need an automated way for the macro to run every business day at 11pm (even if my computer is shut down), so I thought Power Automate could help accomplish this.

Is there a way to code macros so it runs on it's own with any user action? Can Windows Task Scheduler run macros? Any other solutions?

[AlexEncodian]

@coolbeans23 

I've had all sorts of suggestions from AI - its still hit and miss. I'm not an expert on macros so someone else might be able to help re coding scheduled macros or Windows Task Scheduler. Or have a google.