Refresh connected (imported) data

Admin
18 Dec, 2016

Refresh connected (imported) data

A cell range, Excel table, PivotTable report, PivotChart report, text file, and Web query can all be connected to an external data source. You can refresh the data in order to update data from this external data source. Each time you refresh the data, you see the most recent version of the information in the data source, including any changes that were made to the data.

Security Note: Connections to external data may be currently disabled on your computer. To refresh the data when you open a workbook, you must enable the data connections by using the Trust Center bar, or you must place the workbook in a trusted location.

For more information, see Add, remove, or modify a trusted location for your files, Add, remove, or view a trusted publisher, and View my options and settings in the Trust Center.

What do you want to do?

Refresh data from a Microsoft Query, the Data Connection Wizard, or Web query

Refresh data from an imported text file

Refresh an offline cube file

Refresh data in an imported XML file

Automatically refresh data when a workbook is opened

Automatically refresh data at regular intervals

Require a password to refresh an external data range

Run a query in the background or while you wait

Check the status of a refresh operation or cancel a refresh

Refresh data from a Microsoft Query, the Data Connection Wizard, or Web query

Do one of the following:

  • To refresh a specific data connection in the workbook, click a cell in the external data range. On the Data tab, in the Connections group, click the arrow next to Refresh All, and then click Refresh.

  • To refresh all data connections in the workbook, click Refresh All on the Data tab, in the Connections group.

Note: If you have more than one workbook open, you must repeat the operation in each workbook.

Top of Page

Refresh data from an imported text file

  1. Select the worksheet that contains the imported text file.

  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Refresh.

  3. In the Import Text File dialog box, select your text file, and then click Import.

Top of Page

Refresh an offline cube file

Refreshing an offline cube file, which re-creates the file by using the most recent data from the server cube, can be time consuming and require a lot of temporary disk space. Start the process at a time when you do not need immediate access to other files, and make sure that you have adequate disk space to save the file again.

  1. Click the PivotTable report that is based on the offline cube file.

  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Refresh.

    Excel Ribbon Image

Top of Page

Refresh data in an imported XML file

  1. On the worksheet, click a mapped cell to select the XML map that you want to refresh.

  2. If the Developer tab is not available, do the following to display it:

    1. Click the File tab and then click Options.

    2. Click Customize Ribbon, and under Main Tabs, select the Developer check box.

    3. Click OK.

  3. On the Developer tab, in the XML group, click Refresh Data.

    XML group in Ribbon

Top of Page

Automatically refresh data when a workbook is opened

You can refresh an external data range automatically when you open the workbook. Optionally, you can save the workbook without saving the external data so that the file size of the workbook is reduced.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

    Excel Ribbon Image

  3. Click the Usage tab.

  4. Under Refresh control, select the Refresh data when opening the file check box.

  5. If you want to save the workbook with the query definition but without the external data, select the Remove data from the external data range before saving the workbook check box.

Note: To refresh the data when the workbook is opened for a PivotTable report, you can also select the Refresh data when opening the file check box. This check box is under the PivotTable Data section on the Data tab of the PivotTable Options dialog box.

For more information, see Overview of PivotTable and PivotChart reports.

Top of Page

Automatically refresh data at regular intervals

  1. Click a cell in the external data range.

  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

    Excel Ribbon Image

  3. Click the Usage tab.

  4. Select the Refresh every check box, and then enter the number of minutes between each refresh operation.

Top of Page

Require a password to refresh an external data range

Stored passwords are not encrypted and are not recommended. If your data source requires a password to connect to it, you can require that users enter the password before they can refresh the external data range. The following procedure does not apply to data that is retrieved from a text file (.txt) or a Web query (.iqy).

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.

It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

    Excel Ribbon Image

  3. Click the Definition tab.

  4. Clear the Save password check box.

Note: Excel prompts you for the password only the first time that the external data range is refreshed in each Excel session. The next time that you start Excel, you will be prompted for the password again if you open the workbook that contains the query and then attempt a refresh operation.

Top of Page

Run a query in the background or while you wait

Running a query in the background enables you to use Excel while the query runs.

Note: You cannot run an OLAP query in the background.

  1. Click a cell in the external data range.

  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

    Excel Ribbon Image

  3. Click the Usage tab.

  4. Select the Enable background refresh check box to run the query in the background. Clear this check box to run the query while you wait.

Note: While you are recording a macro that includes a query, Excel does not run the query in the background. To change the recorded macro so that the query runs in the background, edit the macro in the Visual Basic Editor. Change the refresh method for the QueryTable object from BackgroundQuery := False to BackgroundQuery := True. For information about editing macros, see Visual Basic for Applications Help.

Top of Page

Check the status of a refresh operation or cancel a refresh

Do one or more of the following:

  • Check the status of a query    A spinning icon appears on the status bar to indicate that the query is running. Double-click the Background Refresh Refresh indicator icon to check the status of the query.

  • Stop a background refresh    To stop a query that is running in the background, double-click the Background Refresh Refresh indicator icon on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh.

  • Stop a query    To stop a query from running when Enable background refresh is turned off, press ESC.

Top of Page

Next Post Previous Post
No Comment
Add Comment
comment url