Forcing Excel to use connection file instead of connection string

Question

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!

Answer 1

I struggled with the same thing for hours. However, I found the solution.

1. In the Connection popup, go to the tab 'Definition' and click on 'Export Connection File'.
2. After completing the export, browse for the freshly exported file and tick the checkbox 'Always use connection file'. Now you don't get the popup anymore because this file is exactly the same as the internal connection in the workbook.
3. Save the workbook
4. Edit the connection file afterwards in Notepad or something.
5. Done!