I have an Excel report that uses a local connection file stored on my PC to connect to a Microsoft Analytics server. I want to switch that connection file to one hosted on a Sharepoint server and force the report to always query that connection file on refresh (so if I have several reports I can change the connection file once instead of changing all the reports). I go to data, properties and browse for the connection file. Excel accepts the file, I check "Always use connection file" and press yes. Then I get a popup that says: "The connection in this workbook will no longer be identical to the connection defined in the external file located at …. The link to that external file will also be removed. Do you want to proceed?"
If I click yes it prompts me for username/password and does the refresh, however if I then change the connection file on Sharepoint and press refresh, Excel seemingly still uses the connection string it got from the file the first time. When I open properties the connection file path, as well as the "always use connection file" checkbox are greyed out.
I read here: http://www.vandeputte.org/2008/07/connection-in-this-workbook-will-no.html that I should insert in the connection file but that does not work either. I am using Excel 2013 if that matters.
I hope someone can help me. Thank you in advance!