XL5: Refresh Data Command May Not Update as Expected

 Last reviewed: September 12, 1996
Article 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:

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

  2. From the Data menu, choose Get External Data.

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

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

  5. 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)

KBCategory: kbtool
KBSubcategory: xlquery

Additional reference words: 5.00



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.