Power Automate to Run Excel Macro

[legalhustler]

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)
Daily Orders!Module1.CopySheet (Workbook name followed by module name followed by procedure name)

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?

 

[Matt Allington]

Why don’t you set the macro to auto run when the workbook is opened, and get PA to open the file daily.

 

[legalhustler]

Why don’t you set the macro to auto run when the workbook is opened, and get PA to open the file daily.

The workbook needs to be scheduled daily sometime in the evening and run automatically so that it makes a copy of the latest version of the data. When the data is refreshed the next day by different users then at the end of the day the latest version of the sheet gets copied, this is repeated every business day until the whole year. Eventually all the copied daily data should be in one workbook and a dashboard will be created to summarize the data.

Can a macro run by itself without opening a workbook at a specific time of a day? Is Windows task scheduler another option? Can it run macros even when my computer is off?

 

[Matt Allington]

Windows task scheduler is definitely and option that can work. I don't have the detail, unfortunately.

 

[legalhustler]

Windows task scheduler is definitely and option that can work. I don't have the detail, unfortunately.

Yeah that’s my last resort. Just thought a power automate Run Script action would be able to run a macro and unfortunately I don’t have Office Script.

Wasn’t sure if macro can run by itself at a specific time of the day without any human intervention/opening the workbook

 

[Matt Allington]

I found one that I have done in the past. Set up a scheduled task and set it to run a program.
My program script was "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE"
my Optional argument was "C:\Users\Matt\XBI Dropbox\Matt Allington\River\filename.xlsm"

inside the filename.xlsm workbook I had an auto run macro to refresh the workbook and save an extract of the refreshed page as a CSV in my case.
I set "stop the task if it runs for more than".. 1 hour
This ends the task each day.