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.