Forum Discussion
Rickytruco
Apr 17, 2024Brass Contributor
Autoupdate table that get its data from other sources (Sharepoint list, SQL server etc)
I just created a table using this options (sorry its in Spanish =() So basically a table from a sharepoint List. I need the created table to then auto update with no input from me. ...
- Apr 18, 2024
Enable Background Refresh means that with Refresh All we do not wait with performing something else while connections are refreshed. Negative impact could be when you have few Power Query connections and PivotTable. The latest could be updated not waiting for queries refresh, thus we may have not updated data in PowerPivot. We need to Refresh All once more or disable background refresh.
To my knowledge to make any refresh we have to open Excel. Workaround could be PowerShell script which opens Excel, Refresh All and closes the file. Potentially another workaround could be combination of Power Automate and Office Script, but so far they Excel for web doesn't refresh external connections.
In any case above is not every minute frequency. I'd keep Refresh on file open plus Refresh on every n minutes. However, every user with that shall have at least R/O access to data source.
SergeiBaklan
Apr 17, 2024MVP
It is updated every one minute only if file is opened. If file is closed nothing happens.
Rickytruco
Apr 18, 2024Brass Contributor
@SergeiBaklan hey ty for your reply.
Oh 😞 And is there no way to make it update witouth opening the file? Then what is the 1st option for "updating in the background"?
Oh 😞 And is there no way to make it update witouth opening the file? Then what is the 1st option for "updating in the background"?
- SergeiBaklanApr 18, 2024MVP
In addition, couple of links
Refresh an external data connection in Excel - Microsoft Support
- SergeiBaklanApr 18, 2024MVP
Enable Background Refresh means that with Refresh All we do not wait with performing something else while connections are refreshed. Negative impact could be when you have few Power Query connections and PivotTable. The latest could be updated not waiting for queries refresh, thus we may have not updated data in PowerPivot. We need to Refresh All once more or disable background refresh.
To my knowledge to make any refresh we have to open Excel. Workaround could be PowerShell script which opens Excel, Refresh All and closes the file. Potentially another workaround could be combination of Power Automate and Office Script, but so far they Excel for web doesn't refresh external connections.
In any case above is not every minute frequency. I'd keep Refresh on file open plus Refresh on every n minutes. However, every user with that shall have at least R/O access to data source.
- RickytrucoApr 18, 2024Brass ContributorOh 😞 So there really is no way T_T... I'll explore the PowerShell option but it's so weird that they would design it this way. Anyway thanks again!