Looking for a way to change the connection in an existing spreadsheet from old SSAS server to the new SSAS server without losing the data in the spreadsheet. Hardware refresh has made this change necessary. We use Excel 2010. Nothing I have tried so far has worked. I created a new connection file (.odc) however, I can't seem to get the existing spreadsheet to connect using it or save with the new connection. Any help would be greatly appreciated.


Easiest fix would be to have your networking folks point DNS for your old server name to your new server's IP address. Unless you rely on Kerberos that will likely work fine.

If that's not an option, then if you left click on the PivotTable, to the Data tab in the ribbon, then click Properties under the Connections section, then go to the Definition tab, please see if "Always use connection file" is checked. If it is, then you can just manually edit the .odc file in Notepad and point to the new server.

If "Always use connection file" isn't checked, then you can left click on the PivotTable, then go to the PivotTable Tools... Analyze tab in the ribbon, then click the Change Data Source button, then click Choose Connection, then choose a different .odc file to connect to. Your PivotTables should be preserved and they should now connect to your new server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.