Microsoft Excel is a tool with functionality beyond imagination and ‘data connections’ is one such functionality, which facilitates users with much ease. ‘Data connections’ web part enables storage and maintenance of multiple data connections to rapidly build workbooks. Excel can be effectively used to import data located externally which can be narrowed down using connections in the workbook or even in Office Data Connection (.odc) files. This provides an additional advantage where various updates to the external data connection properties are to be made. As, these are only carried out once in the .odc file, while the updates in any or all workbooks using the .odc file for the data connection will be made automatically and will later use the updated properties of the connection. This means updates such as migrating or changing the location of a cube or a data base to a different server than the previous one will only require modification the .odc file only, in the safe and reliable data connection library. No alterations in the individual workbooks are required. Such files can also be opened directly by the user and thus Excel can also create new workbooks that already contain the external data connection. Excel will only be able to expand upon and open files that have a trusted location within the data connection libraries. Additional information can be found on

Plan external data connections for Excel Services.

Not only .odc files are easy to be made using SharePoint, they are also easily accessible from their stored locations in the data connections libraries. There are two ways to achieve this; firstly a navigation link can be added for the data connection library. The second alternate is to use SharePoint’s web part for ‘Data Connections’. New data connections can be made using the ‘Data Connection Wizard’ on Excel. To ensure that the external data connection and the .odc file are created, ensure that the check box labelled ‘Always attempt to refresh data’ is selected. Then the file can be uploaded on SharePoint, and will be published to a safe and reliable data connection library hosted by the SharePoint Server. A web part can be created on the page chosen for editing that shows .odc file by selecting ‘Add a Web Part’ and ‘Advance Web part gallery and options’ and the selecting ‘Data connections’ web part.

Related reading on open data connection

  1. Using Office data connection files
  2. How to Modify an Office Data Connection in Dynamics GP