What are the Ways To Manage Excel Workbook Connections?

Some of the computer users, don’t know how to manage the Excel Workbook connection. So, in this blog you will read the ways to manage Excel Workbook connection. To know more about Excel, go to the site of Microsoft Office via www.office.com/setup.

Method To Manage Excel Workbook Connections: Office.com/setup

1: Using Workbook Connections Dialog Box:

It helps to manage the single or multiple connections with the external data sources of your workbook. Along with this, it helps to edit, refresh, create and also delete connections which are there in workbook. It also helps to show the location of each connection which is used in the current Excel workbook. With this, you can diagnose the error message related to external data connections. It also helps to redirect connection to different data or to the different server. Or you can also replace the connection file with the existing connection. With this, you can make & share connection files.

To Manage Excel Workbook Connection By Using Workbook Connections Dialog Box:

  1. Identify a Connection: You can identify all the connections in the Excel workbook as it appears automatically with the following information like Name of the connection with connection Properties, description about the connection, and it also gives the information when was the connection was last refreshed. If the refresh option is blank, this means the connection has not refreshed yet.
  2. Add a Connection: You should click on the Add option just to get the dialog box of Existing Connections.
  3. Display Connection Information: First, you have to select a connection from the opened Existing Connections dialog box. Then, you should click on the Properties option and then the Connection Properties dialog box will open.
  4. Refresh The External Data: First, you have to click on the arrow option which is next to the Refresh option. After this, you should perform anyone of the following:

    If in case, you wish to refresh any specific connections, then you should make selections of those connections. After this, click on the Refresh option.

    If you want to refresh all the connections of your workbook, then you should clear off all the connections. Then, hit on the Refresh All option.

    For getting, the status information about refresh operation then you should select the connections for which you require to extract information. Then, press Refresh Status option.

    To stop the current running refresh operation, hit on the Cancel Refresh option.
  5. Remove One Or More Connections: You should select the connections which you wants to remove from your workbook. Then, hit on the Remove option.

2: By Using Refresh External Data Connection Option:

If you want to Use Refresh External Data Connection Option, first you should click on any cell of your Excel table which has the connection. After this, you should perform any of the following operation:

Automatically refresh data when excel workbook is opened

Automatically refresh data at regular interval

To Automatically Refresh Data When Excel Workbook Is Opened, you should click on the cell which is in the external data range. Then on the Data tab, you should go to the Queries & Connections. After this, click on the arrow key in the Refresh All option, and then click on the Connection Properties. Here, from the opened dialog box of Connection Properties, you should click on the Usage tab, in Refresh control. Then, just select the check box “Refresh data when opening the file”. To save your workbook, you should select the check box “Remove data from the external data range before saving the workbook”.

To Automatically refresh data at regular interval, you should click on the cell which is there in the external data range. Then, on the Data tab, you should go to the Queries & Connections. After this, click on the arrow key in the Refresh All option, and then click on the Connection Properties. Now, in the opened Connection Properties dialog box, you should click on the Usage tab option. At last, you should select the check box Refresh every. After this, you should set the minute interval which will automatically refresh your data.

3: By Creating Office Data Connection (ODC) File:

You can use the Data Connection Wizard or Connection Properties dialog box to create an Office Data Connection (ODC) file (.odc). For this, you should make new connection with data source. Like Connect to SQL Server Analysis Services Database, Import or export text files and also you can move data from Excel to Access. Here, you can also make use of the existing connection. After this, you have to save the connection detail in the connection file. You should click on the Export Connection File option which is on the Definition tab of Connection Properties dialog box. It will open the File Save dialog box, you should save your current connection information in the ODC file.

For details information about Excel application, visit to the official site of MS Office via www.office.com/setup.