3 Easy Ways To Manage Excel Workbook Connections

You all must have heard of Excel Workbook Connections but do know how to manage workbook connection in Excel? Well if you are not having any idea of enabling data connection in Excel workbook.

In that case our, this Excel workbook connections tutorial will help in grabbing every pinch of information reading this. So, that any Excel user can easily perform and manage external data connections in Excel.

What Is Data Connection In Excel?

Data of any Excel workbook can only be bring from different locations. Firstly, either the data is directly stored in your Excel workbook. Or secondly it may be saved in the external data source, like a database, an OLAP (Online Analytical Processing cube) or text file.

Using the data connection in Excel, external data sources are well connected with Excel workbook. Basically this data connection in Excel contains set of information about how to log in, query, locate, and perfectly access the external data source.

After connecting your excel workbook with external data source. One can easily use the refresh option to extract the updated data from their workbook. Using this way, user can get the most updated version of their data including the changes made in the data since it was last refreshed.

Well the Connection information can be stored in a connection file or in the workbook like Universal Data Connection (UDC) file (.udcx) or Office Data Connection (ODC) file (.odc)

These connection files are very useful to share connections on regular basis also to facilitate data source administration.

If you are using the connection file for connecting with the data source. Then in that case Excel will copies down the connection details from the connection file into your Excel workbook.

Different Ways To Perform Excel Workbook Connections

 

In this Excel workbook connections tutorial we will learn 3 different ways to use Data Connection In Excel 2010/2013/2016/2019:

• Workbook Connections dialog box
• Creating an Office Data Connection (ODC) file (.odc)
• Refresh external data connection

Method 1: Excel Workbook Connections Using Workbook Connections Dialog Box

Excel Workbook Connections dialog box option helps in easy managing of single or multiple connections with the external data sources of your workbook. Apart from this the Workbook Connections dialog box is helpful to perform the following tasks:

• It helps to edit, refresh, create and delete connections which are used in Excel workbook.
• Show the location of each connection that is already been used in the current Excel workbook.
• Easy diagnosis of error message regarding external data connections.
• With this option, user can either redirect connection to different data or to the different server. Or alternatively user can easily replace the connection file with the existing connection.
• It becomes too easy to make & share connection files.

Steps To Manage Excel Workbook Connection Using Workbook Connections Dialog Box

Here is how to manage connections in your current using Excel workbook i.e by using Workbook Connections dialog box:

Identify a connection

In the top portion of the dialog box, all connections in the workbook are displayed automatically with the following information:

Column	Comment
Name	connection name is, defined within Connection Properties dialog box.
Description	A short description about connection, is mentioned in Connection Properties dialog box.
Last refreshed	when was the connection was last refreshed such as it’s date and time appears in this section. If it is blank, then it means that the connection has not refreshed yet.

Add a connection

• Tap to the Add option to get the dialog box of Existing Connections.

Display Connection Information

• For this you need to choose a connection from the opened Existing Connections dialog box.
• Now hit the Properties option and this will open the dialog box of Connection Properties.

Refresh The External Data.

• Hit the arrow option present next to the Refresh option. After then perform anyone of the following:
  • If you want to refresh any specific connections only, then make selections of those connections. After then tap to the Refresh option.
  • For refreshing all connections of your workbook, just clear off all the connections. After that tap to the Refresh All option.
  • If you want to get the status information about refresh operation then choose the connections about which you want to extract information. After then hit on the Refresh Status option.
  • For stopping down the current running refresh operation just tap to the Cancel Refresh option.

Remove One Or More Connections

• Choose the connections which you wants to remove from your Excel workbook. After then tap to the Remove option.

Notes:

• Well this option appears disabled to you if your workbook is protected one or if it is an object, like PivotTable report, which uses the protected connection.
• Removing connection will only deletes off the connection. It will not remove any data or any object from your Excel workbook.

Important: By removing connection you are actually breaking the connection with the data source which may leads to cause unintentional consequences, Like different formula results or you may face difficulty in accessing Excel features.

Method 2: Excel Workbook Connections Using Refresh External Data Connection Option

User can connect their Excel workbook with an external data source, like to another Excel workbook, SQL Server database or an OLAP cube.

Well this connection information gets displayed on your workbook as PivotTable report, PivotChart, table.

For keeping the data of your Excel workbook updated you can make use of “Refresh” option to link the data with its source.

So whenever you will refresh your connection, you will only get the most current updated data.

Step To Use Refresh External Data Connection Option

For connections just tap to any cell of your Excel table which uses the connection. After then perform any of the following operation:

• Automatically refresh data when excel workbook is opened
• Automatically refresh data at regular interval

Step To Automatically Refresh Data When Excel Workbook Is Opened

• Tap to the cell present within the external data range.
• Now on the Data tab, go to the Queries & Connections Hit the arrow key present within the Refresh All option, and from this tap to the Connection Properties.

• From the opened dialog box of Connection Properties dialog tap to the Usage tab, within Refresh control. After then choose the check box “Refresh data when opening the file”.
• In order to save your workbook with the complete query definition excluding external data. You need to choose the check box “Remove data from the external data range before saving the workbook”.

Step To Automatically refresh data at regular interval:

• Tap to the cell present within the external data range.
• Now on the Data tab, go to the Queries & Connections Hit the arrow key present within the Refresh All option, and from this tap to the Connection Properties.

• In the opened Connection Properties dialog box. Hit Usage tab option.
• Choose the check box Refresh every. After then set the minute interval that will automatically refresh your data after certain period of time.

Method 3: Excel Workbook Connections By Creating An Office Data Connection (ODC) File

By making use of the Data Connection Wizard or Connection Properties dialog box one can easily use their Excel worksheet to create an Office Data Connection (ODC) file (.odc).

1. You can perform any of the following task:
   • Make new connection with data source. To catch more information, have a look at following topics :
     • Connect to SQL Server Analysis Services Database (Import)
     • Import or export text files
     • Move data from Excel to Access

• Or you can just make use of the existing connection. For more information, see Connect to (Import) external data.

2. After then save the connection detail into the connection file. For this, just make a tap to the Export Connection File option present on the Definition tab of Connection Properties dialog box. This will open the File Save dialog box, so save your current connection information into the ODC file.

Wrap Up:

Hopefully, all the above mentioned fixes to setup Excel Workbook Connections will help you in easy using of data connection in your respective Excel 2010/2013/2016/2019 application. Apart from this if you you have any other query to ask then, ask it in our comment section.