I have a wish to refresh the Excel Data connection, without having to have the user open the Excel File everytime. I have created a SharePoint page to display the content, but Excel does not refresh the data unless the file is open. I was wondering if a Flow could be built to refresh the Excel workbook data connection
I followed the steps recommended to try and update two seperate excel files with differents types of data connections, results as follows:
So it looks like the Flow Update File option will not refresh this PowerPivot. Not a solution but hoping to join the conversation to see if this helps come up with a solution.
thanks for your message. I want to do exactly what you have done in your second test. But it just does not work in my case. I don't know what makes the difference.
I have two files (A and B) in one drive which get replaced daily with updated data.
I have a third file C which is updated by MS flow based on a fourth file D.
All files are in same one drive directory
File A is csv while all others are xlsx.
B and C are just tables.
D being just queries (data model). This file can be refreshed successfully using Refresh all or refresh on open.
File D is called by Ms flow (list rows) and its Key ID is used to update file C.
I need D to be refreshed by flow. I tried your suggestion to get file content / update but it doesn't work. No errors. Run succeeded but data still old. Same flow when run after manual refresh gives correct result (updates File C correctly.)
can you help.
I can share all files with you if u send me the link to upload.
The only thing I can think of is to add a delay in there. I have one that does this and when you open the file it takes a minute or so to get everything refreshed so for it I added in a delay.
It is triggered by a date and time I set, then it does the get file content, delay of 2 min and then the update file. For mine it is tied to an Excel file that uses PowerQuery to pull in data from SQL and the reason it needs the refresh on it is I have a PowerBi and another Excel file that look to it. On this one in particular it has to have that delay or it doesn't work...might refresh a few things but doesn't work correctly.
@rebeccas , have been trying to post a reply but keep ending up with some error and website crash.
I tried the delay, doesn't work but thanks for the response. Seems like I have exhausted all options.
What I think is happening is the file is locked when you "open" it with "Get file content". It stays locked for some time. You need to make sure the file is unlocked in between Get File Content and Update File.
@etfortin Tnx for the swift response.
Mine is set to organisational. Just to be clear the Excel file that needs to be refreshed is made up of multiple power queries which get data from other excel or txt files. It is set to refresh "on open" and works fine when opened manually "in app" but gives error if opened in Excel on line.
Flow runs successfully and File metadata shows modified after the flow runs but the results are not what should be after refresh.
Interesting bypass. Could be made even more robust by having it hosted in a VM on Azure or another Cloud. I didn't check it so I don't know but maybe there's more we can do with the Excel Graph API and a custom Power Automate connector could be done for refresh.
Having Excel Online telling us why a particular Workbook can't be refreshed in the browser would also be of great help. MS has a tendency since always to have very criptics error message that don't help much. When I started programming years ago I remember bumping into the infamous "Something went wrong" error message. And that's it, no more info. Still happens to this day.
The third party tool Power-Update works. I think the licencing still gives you free use against 1 workbook.
Easier than using the task schedulerer workaround proposed above.