I have a spreadsheet in Excel 2016 using PowerPivot that refreshes every 5 minutes, 24/7, from a data source over ODBC.
Occasionally (every couple days), it gets the following error message, and stops refreshing. After clearing the dialog, the data won't refresh anymore, and Excel needs to be restarted:
I have been unable to determine what causes this error to appear, but ideally I'd like to just gracefully ignore update errors, and just have it try again for the next scheduled refresh.
From what I have observed, if you update the model via VBA it will fail silently if there is an error. The main problem with this, is there is nothing to tell you that there is an issue.
Since you mention Excel needs to be restarted to fix the issue, your issue could be more related to excel doing some form of memory leak till you are low on RAM. Since Power Pivot is designed to be entirely in RAM, it will start throwing errors when the system is low on RAM. Also keep in mind that it uses more RAM when it is updating.
It might make sense to try automating that at a certain point in the day excel is closed and reopening to force Excel to release memory.
