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

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).

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 my 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:

Please Login or Register  to view this content.

From what you describe, I'd probably set up a Dataflow that aggregates all the workbooks. Then all your workbooks - including the master if you still need that for an overall view - can collect their needed data from the dataflow. It's pretty easy to use a power automate flow to update a dataflow.

Do you have a Power BI workspace (for publishing reports)? If not, this won't be an option for you.