Refresh imported data

Refreshing data

ShowAutomatically refresh data when a workbook is opened

You can refresh an external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.) automatically when you open the workbook, and optionally save the workbook without saving the external data, so that the workbook file size is reduced.

  1. Click a cell in the external data range you want to refresh (refresh: To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.), and then click Data Range Properties Button image on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.).
  2. Select the Refresh data on file open check box.
  3. If you want to save the workbook with the query definition but without the external data, select the Remove external data from worksheet before saving check box.

ShowRefresh multiple external data ranges

ShowRefresh data in an imported text file

  1. Select the worksheet with your imported text file.
  2. Click Refresh Data Button image on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.).
  3. In the Import Text File dialog box, select your text file, and then click Import.

ShowTip

If you want to import only part of the data in a text file as a refreshable data range, you can create a query to retrieve the data. For more information, see Microsoft Query Help.

ShowAutomatically refresh data at intervals

ShowControl the order of refreshes

Setting refresh options

ShowRequire a password to refresh an external data range

If your data source (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.) requires a password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) to connect to it, you can require that the password is entered before the external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.) can be refreshed (refresh: To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.). This procedure does not apply to data retrieved from a text file (*.txt) or a Web query (*.iqy).

  1. Click a cell in the external data range, and then click Data Range Properties Button image on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.).
  2. Under Query definition, clear the Save password check box.

 Notes 

  • Stored passwords are not encrypted.
  • Microsoft Excel prompts for the password only the first time that the external data range is refreshed in each Excel session. The next time you start Excel, you will be prompted for the password again if you open the workbook that contains the query, and then click Refresh Data Button image.

ShowPreserve cell formatting when you refresh an external data range

  1. Click a cell in the external data range (external reference: A reference to a cell or range on a sheet in another Excel workbook, or a reference to a defined name in another workbook.), and then click Data Range Properties Button image on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.).
  2. Do one or both of the following
    • If you want to preserve cell formatting that you apply, select the Preserve cell formatting check box under Data formatting and layout.
    • If you want to preserve column widths that you set, clear the Adjust column width check box under Data formatting and layout.
  3. Click OK.
  4. To refresh (refresh: To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.) the external data range, click Refresh Data Button image on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.).

ShowRun a query in the background or while you wait

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

  1. Click a cell in the external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.), and then click Data Range Properties Button image on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.).
  2. Select the Enable background refresh check box to run the query in the background or clear it to run the query while you wait.

 Notes 

 Note   You can check the status of a refresh by clicking Background Refresh Refresh indicator in the status bar. To cancel the query, click Cancel Refresh on the External Data toolbar.

 
 
Applies to:
Excel 2003