Retrieving Excel external data using Microsoft Query

A range of cells, an Excel table, a PivotTable report, or a PivotChart report can be connected to an external data source data source: A set of stored source information that is used to connect to a database. The data source contains the name and location of the database server, the name of the database driver, and the information required to log on to the database. )。 You can refresh the data to update the data for this external data source. Each time you refresh the data, you can see the latest version of the information in the data source, including any changes to the data.

Security Currently, your computer may be disabling the connection to external data. To refresh the data when you open the workbook, you must use the Trust Center column to enable the data connection or to place the workbook in a trusted location.

Refresh data automatically when you open a workbook

You can automatically refresh the external data range when you open the workbook, and you can choose to reduce the size of the workbook file by selecting not to save the external data when you save the workbook.

Click a cell in the external data range. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

Click the Use Status tab. Select the Refresh data when opening file check box. If you want to save the query definition while saving the workbook, but do not save the external data, select the Remove external data from the query table check box before saving the worksheet.

Note To refresh the data in the PivotTable report when you open the workbook, you can also use the Refresh data when opening a file under PivotTable data section on the Data tab of the PivotTable Options dialog box.

Refresh multiple external data ranges

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

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

Refresh the data in the imported text file

Select the worksheet that contains the imported text file. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Refresh. Select the text file in the Import Text File dialog box, and then click Import.

Automatically refresh data on a regular basis

Click a cell in the external data range. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

Click the Use Status tab. Select the Refresh frequency check box, and then enter the number of minutes between each refresh operation.

Require password when refreshing external data range

The stored password is not encrypted and we do not recommend that you use it. If your data source requires a password password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can have up to 255 letters, numbers, spaces, and symbols. When you set up and enter a password, you must type the correct uppercase and lowercase letters. To connect, you can require that you enter a password before refreshing the external data range. This procedure does not apply to data retrieved from a text file (*.txt) or a Web query (*.iqy).

Click a cell in the external data range. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

Click the Definition tab. Clear the Save Password check box.

Note Microsoft Office Excel prompts for a password only the first time an external data range is refreshed in each Excel session. The next time you start Excel, if you open the workbook that contains the query and try to refresh, you will be prompted to enter the password again.

Run a query in the background or when you wait

By running the query in the background, you can use Excel when you run the query.

Note You cannot run OLAP queries in the background.

Click a cell in the external data range. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

Click the Use Status tab. Select the Allow background refresh check box to run the query in the background. If the check box is cleared, the query runs when you wait.

Comments

Stop a query to prevent a query from running with "Allow background refresh" turned off, press ESC. Stop background flush to stop a query running in the background, double-click the status bar (status bar: The horizontal bar at the bottom of the screen that displays information about the current state of the program, such as the status of the item in the window, the process of the current task, or information about the selected item.) , to display the External Data Refresh Status dialog box, and then click Stop Refresh. Record macros when you record Macros macro: An action or a set of actions that you can use to automate a task. You can record macros in the Visual Basic for Applications programming language. , Excel does not run queries in the background. To change a recorded macro to run in the background, in the Visual Basic Editor Visual Basic Editor: An environment for writing new Visual basic for Applications code and procedures, and editing existing code and procedures. The Visual Basic Editor includes a complete set of debugging tools to find syntax, run-time, and logical problems in your code. Edit the macro, and then change the query Table object's Refresh method from "BackgroundQuery: = False" to "backgroundquery: = True". For information about editing macros, see Visual Basic Help (Microsoft Visual Basic Help: To get Visual basic Help in Excel, point to Macros on the Tools menu, and then click Visual Basic Editor.) On the Help menu, click Microsoft Visual Basic Help. )。

Check the status of the refresh operation or cancel the refresh operation

To check the status of the refresh, double-click background refresh on the status bar to cancel the query, press ESC.

Refreshing an offline cube file

Refreshes the offline cube file offline cube file: A file that is created on a hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow users to continue operating after disconnecting from the OLAP server. (That is, rebuilding a cube file based on the latest data in the server cube) is time-consuming and requires a large amount of temporary disk space. Start this process without immediate access to other files, and make sure that you have enough disk space to save the file again.

Click the PivotTable report that is based on the offline cube file. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Refresh.