Automatically refresh SharePoint stored Excel Power Query Table when the workbook is closed

Hi everyone,

I’ve been struggling with automatically refreshing my Power Query Table in an Excel Workbook. I’ve already tried the following, but nothing seems to work:

1. Creating a Power Automate flow that runs a script intended to refresh all queries. The script works well when triggered manually, but the flow doesn’t, even though it was created correctly.
2. Using a VBA code scheduled to automatically refresh the data. Again, it works fine when run manually (see the code below).
3. Using the Task Scheduler to open the Workbook daily at 8 am. Unfortunately, this also didn’t work.

I think I’ve run out of options. Could the issue be because the file is stored on SharePoint?

The challenge I’m facing is that I receive multiple Excel files every week via email, with the data stored as ranges. I’ve created a flow that saves these files to a designated location and then deletes the emails.

Afterward, I need to merge and update the downloaded data in my master Excel sheet, which I’ve been doing using Power Query. From this master sheet, I have more than 20 other Excel workbooks created (also connected to the master sheet via Power Query), each tailored to individual employees. These workbooks only include the rows where the employees’ names appear, as I need to share the updated data with them separately.

Do you have any other ideas on how to achieve this? Perhaps even without using Power Query? I’m open to trying anything.

Thank you so much!

P.S.: The VBA code goes as follows:
Public ReloadInterval As Double

Public Const Period = 30

Sub Reload()

MsgBox "Updates will begin to occur at " & _

"the interval of " & Period & " seconds" 

Call FirstReload

End Sub

Sub FirstReload()

ReloadInterval = Now + TimeSerial(0, 0, Period)

Application.OnTime _

EarliestTime:=ReloadInterval, \_ 

Procedure:="ReloadConnections", \_ 

Schedule:=True 

End Sub

Sub ReloadConnections()

ThisWorkbook.RefreshAll

Call FirstReload

End Sub