I have created a excel file with a table. That table is connected to a SharePoint list. I have created 12 PivotTables based on the data in that table. I save the file in a SharePoint library.  Then I replicate that file many times placing different filters in the PivotTables.  I have lots of these created at this point.

I can open up the files and hit Refresh-All and the data updates and all my pivot charts are refreshed as well.  I have salesmen in the field that also open up these files.  They have proper access to the data in SharePoint so the refresh data works fine.  This worked for about a month flawlessly.

But now, seemingly randomly, some of my workbooks table's lose connection (some of them still work fine for the moment).  The workbook shows a connection to the data under "Connections" but that data connection is no longer linked to the table.  Now my Refresh-All doesn't work any longer.

First - I need to know if there is a way to make sure this doesn't happen any longer.  Am I missing a setting upon creating the data connection that is allowing this disconnect?  The data connection is created by opening the SharePoint list, and clicking "Export to Excel."  When asked, I tell it to create a connection to the data.

Also, in a perfect world, I'd like a way to re-connect my tables to their long lost data.  This way I can avaoid re-creating all these PivotTables.

Let me know what information is needed to further assist me.

Can you share two workbooks - one with the loss of connection, and a second with the connection intact?
Which Excel versions do you all use? You can consider to use the Data Model when you load the data, with the data loaded to the Data Model, the PivotTables may be more resilient during refresh.