XL5: Refresh Data Command May Not Update as Expected

ID: Q118304

The information in this article applies to:

Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel 5.0, when you choose the Refresh Data command, the data in your worksheet may not be updated as expected.

CAUSE

You can use the Refresh Data command on the Data menu or the Refresh Data button on the Query and Pivot toolbar to update a range of data that has been extracted from an external source (the result set).

If there are fewer records in the result set when you refresh the data, Microsoft Excel will clear the old data range and redefine it with the new result set. However, if additional records have been added to the data source and should be included in the data range, they may not be displayed on the worksheet. This new data is not displayed because the previously defined data range will hold only the number of records that were retrieved the last time you returned the result set from Microsoft Query.

WORKAROUND

To display the new records, do the following:

Select a cell in the data range or select the entire data range.

From the Data menu, choose Get External Data.

Choose the Edit Query button. Microsoft Query will be opened with the current query and updated result set.

From the File menu, choose Return Data To Microsoft Excel.

Choose OK.

Microsoft Excel will clear the old data range and create a new data range with the updated query results.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

MORE INFORMATION

When you retrieve data from an external data source using the Get External Data command on the Data menu, you have the option to keep the query definition with the result set on your worksheet. Microsoft Query uses the query definition information to connect to a data source and decide which data to retrieve from it.

When to use the Keep Query Definition option

If you want to update the result set later, select this check box

If you do not want to save the query, and if you do not need to update the result set later, clear this check box

You also have the option to specify where you want the data to be placed on the worksheet. The area that contains the pasted data is called the data range.

REFERENCES

"User's Guide," version 5.0, Chapter 23, "Retrieving Data from Lists and External Databases Using Microsoft Query"

For more information about refreshing data, choose the Search button in Help and type:

refreshing result sets

-or-

Refresh Data command (Data menu)

Additional query words:

Keywords : xlquery
Version : WINDOWS:5.0
Platform : WINDOWS
Issue type :