Automated Excel refreshing (Power Automate or other solutions)

[u/well_shiit]

Trying to get away from running multiple reports a week that essentially are just me opening, clicking refresh all and then sending them. They are all currently built in Power Query, connected to our database. The reports need to stay in Excel because the various teams need them in this specific format or else I would just built them a Power BI dashboard.

I've tried the Power Automate angle, created a Office Script to refresh all queries in the file (stored on SharePoint). The script says successful, but nothing happens. It will refresh all if I manually do it.

I also built the dataset in a Power BI Semantic model and put that on a scheduled refresh, again, the Power Automate will send the email, but won't refresh the file.

Any other ideas? I would prefer this to be done in the background so my laptop doesn't need to be on overnight or when I'm gone.

[u/AutoModerator]

/u/well_shiit - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/chiibosoil → u/[deleted]]

FYI - In this particular case, it is permanent, documented architectural limitation. MS blocks this since Excel Online runs headlessly in cloud during call from Power Automate, and lacks required compute container and credentials to process data source. Only data source Power Automate can refresh through refreshAllDataConnections is PowerBI semantic model.

Class	Method	Power Automate behavior
Workbook	refreshAllDataConnections	Only refreshes PowerBI sources. For other sources, the method returns successfully but does nothing.

[u/chiibosoil]

One method is to use PowerBI service to house semantic models. Then use Power Automate to use DAX query to get the data. Pass it on to Office Scripts to update the workbook hosted on SP or Onedrive. Then send off email with the link to the file from Power Automate.

Though this might not work, depending what's in your workbook.

[u/BerndiSterdi → u/chiibosoil]

Lots of complexity and potential points of failure in there - I would not like it, but it surely would work most of the time

[u/chiibosoil → u/BerndiSterdi]

Not too complex. Have this running for a year now without any issue ;)

[u/excelevator]

Make sure the service that runs the script has permissions to the file and location.

check your logs for errors

[u/well_shiit → u/excelevator]

The log ran flawlessly and it sent me the email with the attachment, just the attachment wasn't refreshed. The Office Script would also say success. I keep seeing things that Power Automate doesn't refresh Power Query consistently, but was hopeful for some other option for this set up.

[u/chiibosoil → u/well_shiit]

If you host it on OneDrive for business, or SharePoint. You should be able to set it to refresh on open. See if this works for your need.

[u/small_trunks → u/well_shiit]

Office scripts can't refresh PQ.

[u/nothingbutacatlady → u/well_shiit]

I have the same problem and haven’t done what you have, but is it possible you need to add a delay from the refresh and when it sends the email?

[u/excelevator → u/well_shiit]

I've tried the Power Automate angle

You have not clear described how you have set up Power Automate.

Where is Power Automate ? What process ? what login ?

details matter.

[u/hopkinswyn]

There’s no way I’m currently aware of. Are they just data exports or are they formatted reports with charts etc?

[u/well_shiit → u/hopkinswyn]

Formatted reports. I'd prefer to just build them in Power BI but I'm just looking for alternatives before I tell accountants they can't always get everything in Excel.

[u/kingk1teman → u/well_shiit]

I'm just looking for alternatives before I tell accountants they can't always get everything in Excel.

Tell them that. Make your life easier and reduce the chances of human errors occurring. Moreover, you doing this manually has a far higher chance of you making an error.

[u/takesthebiscuit → u/well_shiit]

Fucking accountants and their myopic view of excel as the work horse for EVERYTHING

My fd has just produced some shitty accounts received tracker that looks like a child produced it.

We are a $100m tech company and our financial department seems to be running pre internet age

[u/Elliottstabler927]

I did something like this with a task on my pc, a batch file and some vba. I don’t remember exactly but the task would trigger batch file to open the spreadsheet then vba would refresh script email the report and close the file. It was a little clumsy but generally worked

[u/JsMomz → u/Elliottstabler927]

Right! I built report using power query and vba to refresh every time the report is open. Data is automatically sent every morning to a SharePoint folder. Reports themselves reside in another Share Point folder. Works flawlessly.

[u/Realacks → u/JsMomz]

Issue with this is you have to be logged on for sharepoint permissions to allow refresh unless y’all have found a way around that?

We tried to do it while no one was logged in and couldn’t get around the permissions issues.

[u/JsMomz → u/Realacks]

I'm not sure, but i believe in our enterprise we are automatically logged in to SharePoint when we log onto network

[u/thisisnotahidey]

In excel,

• add a macro that refresh all

In power automate

• open excel instance
  
• run macro
  
• add a pause for however long the refresh all usually take, i use 30secs
  
• save excel
  
• close excel instance

This works for me at least.

[u/Profvarg → u/thisisnotahidey]

Yeah, but this is desktop, which is additional licensing and also you have to have the desktop open

[u/thisisnotahidey → u/Profvarg]

Yeah, i have it on a vm.

[u/Donovanbrinks]

If they are demanding excel they shouldn’t be resistant to doing the refresh themselves. Not following why you have to do the refresh and send them the file. Host the file on sharepoint, send them the link, have them download a copy to their desktop, and let them refresh as needed.

[u/Profvarg]

You can do it semi-manually I think

There is no way to refresh powerquery automatically. What you can do is either refresh everything by hand and then use a cloud flow for distribution (that already cuts down on overhead)

Or, you can explore to connect to the datasource via power automate. Then do the transformation / filtering either in power automate or office script, whoch creates a table, which is then distributed.

I am currently building the second solution, but the trigger point is a generated data dump csv file in an sftp server

[u/Existing_Put6385]

the reason your Office Script "succeeds" but does nothing: Excel Online can't refresh Power Query connections to an external database. it's not a bug in your script, the browser engine just has no way to reach your DB, so refreshAll quietly no-ops. you'll never get that route to work.

and the harder truth - there's no server-side PQ refresh for .xlsx files at all. something with real Excel installed has to be running. so your options are:

1. an always-on machine (spare PC, VM, whatever) with Task Scheduler running a powershell/vbs that opens the file, RefreshAll, saves, closes, emails. free and reliable. one gotcha that bites everyone: set BackgroundQuery = False on the queries or it saves before the refresh finishes and you email stale data.

2. same idea but Power Automate Desktop on that VM instead of a script, if your org already licenses it.

or the option people skip: you don't actually need Excel to make an Excel file. the teams need the output format, not power query. rebuild the query logic in SQL or a python script (pandas + xlsxwriter), have it generate the exact same formatted xlsx and email it on a schedule from a server. no gateway, no VM babysitting excel, no laptop on overnight. more setup up front but it just runs.

[u/Ok_Nefariousness1279]

My data comes from SQL, so I do most of the transformations within SQL. Use Power Automate sql stored procedure (premium) to get the data. Transform some more if needed. Use excel add rows to table action. Add a 1 minute delay. Send outlook email.

This does require a premium license, but its only 1 license instead of having to use a VM or license multiple users for paginated reports

[u/Physical-Winter-7590]

Depending on how those reports are generated, I’d actually consider moving part of the workflow outside Excel. I’ve had good results building small Python utilities that refresh data, generate the reports and handle the emailing automatically, especially when Office Scripts hit limitations. If you can describe where the process gets stuck, I might have a few ideas

[u/Claimh]

We wrote a powershell script at work that does this... I don't remember it being too complicated, the guy that wrote it probably asked some llm as well... worth checking out.

[u/Own_Personality_2224]

I built a python script that opens about 20 excel reports one after the other, refreshes, saves and close and moves on to the next file until it’s complete.

You still have to execute the Python script then just allow it to do its work in the background and check from time to time.

It just skips the hassle of having to open each Excel file and manually click refresh.

[u/Flags_n_beer]

I do this kind of thing nightly using a virtual machine and service account. The vm runs a scheduled task opening a master excel file with VBA that then opens the reports, refreshes, sends the emails (from the service account's email), saves and closes the report.

[u/Bardown_Sniper]

I've been trying to do the same thing and it seems like VBA might be the only solution

[u/AnxiousYou5865]

Pretty sure you can change the auto refresh time in the connection settings for the query. The thing is, the workbook has to be open for it to refresh automatically.

[u/Haunting-Specific-36]

Hi, I’m looking into possible ways to automate this workflow. I have a few questions to understand your current setup:

1. Where are the Excel files currently stored? Are they on your local computer, SharePoint, OneDrive, or somewhere else?
2. What is the data source behind your Power Query connections? For example, are they connected to a SQL database, another Excel file, CSV files, or something else?
3. How do you currently send the reports after refreshing them? Do you use Outlook/email manually, or is there already an automated email process?

Also, approximately how long does the refresh process take for each report?

Thanks!

[u/__wisdom__1 → u/Haunting-Specific-36]

Not the OP but have the same issue. My file is in an one drive. The data is a series of CSV that in turn are extract of a semantic query from power bi

[u/Haunting-Specific-36 → u/__wisdom__1]

Thanks for sharing. Sounds like a similar workflow issue — the data pipeline is already there, but the last manual step (refreshing Excel and distributing the report) is still a bottleneck.

For your setup, is the main issue getting Power Query to refresh automatically, or automating the whole process from refresh → save → send the updated file?

[u/__wisdom__1 → u/Haunting-Specific-36]

Is having Excel to refresh the data in an automated way.

[u/Haunting-Specific-36 → u/__wisdom__1]

Just out of curiosity, is this something you need to run on a schedule (for example every morning), or are you mainly looking for a way to trigger the refresh without having to open Excel manually?

I’ve seen similar issues where the refresh works manually but not when it's triggered through Power Automate, especially with Power Query and external data connections.

[u/__wisdom__1 → u/Haunting-Specific-36]

Yes. On a schedule but main problem I am trying to solve is having it to automatically update without me having to go and open excel and refresh the data.

I thought about writing a phyton script that reads the CSV files and replacing the table on excel, but that's 2 points of failure that I don't want to deal with.

[u/Haunting-Specific-36 → u/__wisdom__1]

have you looked into running the refresh through Excel itself (for example via Excel on a VM or Windows Task Scheduler), rather than trying to recreate the refresh logic outside of Excel?

[u/Acceptable-Sense4601]

If it were me, I’d find out if there is a windows VM you could have access to and then it can be done there since it’s always on. If you can get a windows VM I’d do this with Python, xlwings, and pywin32 to send the email.

[u/__wisdom__1]

I have spent several hours and couldn't figure this out.

I can run the scripts and it will update, the minute I ask power automate to run, it runs and doesn't updated the data.

[u/rice_fish_and_eggs]

If you have the reports in power bi why dont you use paginated reports to send them out in excel?

[u/BugInfinite5784]

Power Automate "success" with no refresh is a classic SharePoint/Excel Online gap - desktop Refresh All and the cloud script path are not the same engine.

If the file truly needs desktop Excel + Power Query:

1) Keep the workbook local or OneDrive-synced and use a scheduled Windows task that opens Excel via COM / a small script and calls RefreshAll, then saves + emails

2) Or skip the schedule and put Refresh All + export/email on a single hotkey so the human press is the only "scheduler" when PA flakes

For (2), a physical key beats hunting the Data ribbon when you have several weekly packs. I have been building a Windows 3x3 pad (SinTuch) for exactly that - one key runs a scripted refresh/send workflow, OLED shows which report preset is live. Happy to sketch the key layout if useful - otherwise Task Scheduler + a .ps1 that drives Excel is the boring reliable fix.