The connection in this workbook will no longer be identical to the connection defined in the external file located at ...

Office Data Connection files (ODC files) are a great feature in Excel 2007. Basically this technology allows you to store connection information (server name, authentication, database name, cube, ...) in a file. This file can be placed on a central file share or in a Sharepoint library. In stead of defining new connection info in every single Excel file, you just point to the "central" ODC file. If something changes, e.g. the cube is moved to another server, you simple update the ODC file and everyone is redirected the new server automatically.

At least this wil work if you specied "Always attempt to use this file to refresh data" the during the initial creation of the ODC file.

If you didn't check the check box then you and want to enforce the link afterwards you might be tempted to check the "Always use connection file" checkpoint in the connection properties (Click Connections in Data ribbon, Properties, Definition)

However if you check "Always use the connection file" you will get following error message:

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 with the changes to the connection in this workbook?

Excel will break the link between the "Excel internal" connection and the ODC file. The UI seems to suggest that you can fix the link. At least that's what I thought. The key point is that the option "Always use the connection file" is not stored in Excel but in the ODC file.

If you want to fix the link between Excel and the ODC file you can manually update the ODC file by adding the following line

<odc:AlwaysUseConnectionFile/>

Make sure to add it just before the </odc:Connection> line.

After you saved the ODC file go back to Excel. Click Connections in the Data ribbon, Properties, Definition. Click Browse and point to the updated ODC file. After you click OK you will notice that the "Always use connection file" checkbox is now checked. And you no longer receive the error message.

Took me quite some time recently to find out, so I wanted to share this one.