cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
J_K_
Level: Powered On

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

Thanks for the suggestion. But it does not workin my case, unfortunately. Do you have an example where this is actually working?

Highlighted
mnmsymmons
Level: Powered On

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

Hi @J_K_ 

 

I followed the steps recommended to try and update two seperate excel files with differents types of data connections, results as follows:

  1. First file is a PowerPivot linked to PowerBI data - like your situation, the file was modified, saved and the flow was successful but when I go in straight afterwards I can see the data has not actually refreshed. FAIL
  2. Second file is a PowerQuery data connection to the first excel file (i've used this to essentially complete formatting and calculations on the first data set so that it is in a table format that can be read and used by Flow to add the data to a SharePoint list which is then used by a PowerApp) - This works, If I first manually refresh file 1 and then run this flow the second file has saved with the updated data. PASS

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.

 

 

J_K_
Level: Powered On

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

Hi @mnmsymmons 

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. 

navaidm
Level: Powered On

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

@rebeccas , 

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.

Super User
Super User

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

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. 

 

Try that.

 

navaidm
Level: Powered On

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

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

Eserapicos
Level: Power Up

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

Same problem... The question is simple: "How to refresh an Excel external data without manually open it, using MS Flow" ?
Tks for attention.
etfortin
Level: Powered On

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

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.

navaidm
Level: Powered On

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

@etfortin  Tried with 20 Minutes delay between get file content and Update. Didn't work.

 

etfortin
Level: Powered On

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

I'm working on it as well and found out my data can't refresh in Excel Online. I'm still investigating why. Maybe a Permissions thing? Permissions being the "Public, Organizational, Private" settings for data source in Power Query that screws things up almost each time its not set to None. I'll report back.
navaidm
Level: Powered On

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

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

 

 
 
etfortin
Level: Powered On

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

For the sake of testing, can you try having all the sources permission set to "none" or "Public"?
JRGL
Level: Power Up

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

I use task scheduler to open a workbook (1) that I want to refresh. Then I use task scheduler again to open another workbook (2) with a macro that runs as soon as it opens and refreshes all connections on workbook (1). My macro has do events after the refresh all command then after do events I do application quit on VBA as well. You have to make sure background refresh is unchecked on all connections. Is not an online refresh per se but you can still automate the refresh on a schedule.
etfortin
Level: Powered On

Re: Looking to Refresh an Excel workbook in a SharePoint Online DocLib

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. 

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors
Users online (4,985)