Removing connections to SharePoint lists

[Kaddrik]

Hello,

Hope you are all doing well.

I have a several Excel workbooks connected to different SharePoint 2013 lists.

These documents are yearly reports. and we need to keep the data they contain.

The company I'm working for is , like most of the companies, switching to M365 and the SharePoint site has been recreated. We still have those documents linked to the SP2013 lists, since we need to keep them for regulatory purposes and the company will be decommissioning the SP2013 in a few weeks.

Of course, if the environment, that means lists are not accessible anymore...meaning my documents will not keep the information anymore.

Does one of you know a way to keep the content of those report even if the connection is not behind anymore ? I tried to remove the connection but all the content of the file disappears...

Thanks a lot for your help !

[#2]

@Kaddrik 

If you remove connection only, not entire query to list, data shall be kept.

[#3]

Hi @Sergei Balkan ,

ok...so it's just as simple as that !...thanks !!!
If i can ask something more.... I just did in a normal XLSX file where I have simple data and it works but... when i try to do the same with Pivot Tables, data still disappears.

Would you have any idea how i could do it for Pivot Tables ?

Thanks a lot !!

[#4]

@Kaddrik 

That's another story. If you connect Table to the list, data is kept in the grid, with removing of connection you only won't be able to update it.

With PivotTable data is on SharePoint, removing the connection you remove data. Actually I don't know hoe to resolve. Depends on which PivotTable you use data could be kept in PivotTable cache or in data model. If you disable all possible options to refresh PivotTable (without removing the connection) it is some chance data will be kept in caches if you do nothing with such files. That's easy to test.

[#5]

Hi @Sergei Baklan,

Sorry for my late reply.

Not sure i know how to do that
When you say "Data Model" it's the file that is stored locally on my computer isn't it ? So that means i should put it in my SharePoint library...
Isn't the Pivot table cache activated by default so the data would not disappear ?

Now i'm thinking about something , Since I have a file with all the data (the one where i removed the connections) and the additional file with the PivotTables only, isn't it possible to simply "switch" the data connection from SharePoint to the other file ?

Thanks a lot !!

[#6 · Accepted solution]

@Kaddrik 

Both are kept within Excel file. If you don't know about data model most probably you don't use it, details are here Create a Data Model in Excel (microsoft.com)

Without data model PivotTable creates copy of the source data in local cache and works with it. It updates the cache on each refresh from data source. Understanding the Excel PivotTable Cache – (bluepecantraining.com)

I'm not sure you can change PivotTable data source from one connection type to another, but my practical experience here is quite limited. 

[#7]

Hi @Sergei Baklan ,

Ok, I will look into this first and let you..and everyone ... know.

Thanks a lot

[#8]

Hi @Sergei Baklan,

So i guess i have it... I tried to follow what was on your 2 links but i had error messages...
But i thought about what you said. using the cache, i'm not too confident because i'm thinking if someone plays around the file one day or if someone refreshes the tables, everything might be lost.

I tried to look into the Data model thing. As i said, i had an error when trying to load it but i figured out that ... if i go to the connections properties and i untick the boxes "Refresh data when opening the file" and "Refresh this connection on Refresh all" (i do it for all my connections) and then i click "Refresh all" in the Data menu, that generates the Data Model!

I saved the file and i stopped my internet connection opened the file and the data is still there, i hit refresh, everything stays there. i just get an error message saying that my data source is not accessible. If i try to refresh again or do whatever, everythinng is still there.

in addition to be sure everything is ok, i sent the file to another computer which doesn't have access to the SharePoint site and even stopped my internet connection on this other computer and everything is still there.

So I guess it's good !

Thanks a lot !!!

[#9]

@Kaddrik , great to know you solved that