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 on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.).
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.

1. If your worksheet contains more than one 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.) that 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.), on the View menu, point to Toolbars, and click External Data.
2. On the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), click Refresh All to refresh all of the external data ranges in the workbook.

   If you have more than one workbook open, you must click Refresh All in each workbook to refresh external data.

1. Select the worksheet with your imported text file.
2. Click Refresh Data on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.).
3. In the Import Text File dialog box, select your text file, and then click Import.

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 on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.).
2. Select the Refresh every box, and then enter the number of minutes between refreshes (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.).

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.) 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.) first, and then click Refresh Data on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.).
2. Repeat for each external data range until they are all refreshed in the order you want.

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 on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.).
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 .

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 on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.).
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 on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.).

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 on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.).
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

• Stop a query    To stop a query from running when Enable background refresh is turned off, press ESC.
• Stop a background refresh    To stop a query that is running in the background, double-click the Background Refresh icon on the status bar (status bar: A horizontal bar at the bottom of the screen that displays information about the current condition of the program, such as the status of items in the window, the progress of the current task, or information about the selected item.) to display the External Data Refresh Status dialog box, and then click Stop Refresh.
• Recording a macro    While you are recording a macro (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) that includes a query, Excel will not run the query in the background. To change the recorded macro so that it runs in the background, edit the macro in the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.) and change the refresh method for the QueryTable object from "BackgroundQuery := False" to "BackgroundQuery := True". For information about editing macros, see Visual Basic Help (Microsoft Visual Basic Help: To get help for Visual Basic in Excel, point to Macro on the Toolsmenu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.).