XL5: Can't Choose Refresh Data Button to Update Query

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

In the versions of Microsoft Excel listed above, when you select data on your worksheet that was returned from an external data source, the Refresh Data tool may be unavailable; that is, your computer may beep when you try to choose the Refresh Data tool. Note that the tool still works correctly when the selected data is contained in a Microsoft Excel PivotTable.

CAUSE

This problem occurs if you choose the Refresh Data toolbar tool on a toolbar other than the built-in Query And Pivot toolbar.

The behavior is by design of Microsoft Excel. You can use the Refresh Data tool any toolbar to retrieve the most current data located in an external data source that has been used to create a Microsoft Excel PivotTable. Additionally, you can use the Refresh Data tool on the built-in Query And Pivot toolbar to refresh data that has been returned to a range of cells on a Microsoft Excel worksheet.

You can move or copy toolbar buttons to any existing toolbar in Microsoft Excel. If you move the Refresh Data button to a toolbar other than the built-in Query And Pivot toolbar, the button will not have the additional functionality of refreshing data retrieved from an external data source to a range of worksheet cells.

WORKAROUND

To work around this problem when you add the Refresh Data tool to another toolbar, you can assign the button to the macro that updates data returned from an external data source by doing the following:

1. On the View menu, click Toolbars. In the Toolbars dialog box, click Customize.

2. Click the Refresh Data button on the toolbar to which you added this button.

3. On the Tools menu, click Assign Macro.

4. In the Macro Name/Reference box, type "XLQUERY.XLA!doRefresh" (without the quotation marks) and click OK.

STATUS

This is a known problem in Microsoft Excel version 5.x. The problem was corrected in Microsoft Excel for Windows 95, version 7.0, Microsoft Excel 97 for Windows, and Microsoft Excel 98 Macintosh Edition.

REFERENCES

"User's Guide," version 5.0, Chapter 23, "Retrieving Data with Microsoft Query", page 449

For more information about the Refresh Data command, choose the Search button in Help and type:

    Refresh Data command