Microsoft KB Archive/112854

= Err Msgs When Dynamic MSQuery/Microsoft Excel Links Activated =

Article ID: 112854

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0 for Macintosh
 * Microsoft Query 2000

-



This article was previously published under Q112854



SUMMARY
The steps on pages 54-55 of the Microsoft Query "User's Guide" explain how to create dynamic links between Microsoft Query and Microsoft Excel 5.0. These instructions are only partially correct. If you create dynamic links by copying the data in Microsoft Query and using the Paste Link command in Microsoft Excel, and you activate the link, linked cells will contain #REF error values, and you may receive the following error messages, depending on how you re-establish the link.

NOTE: The Error message you receive on the Macintosh simply states "Can not find Data Source."



CAUSE
The instructions in the Microsoft Query "User's Guide" use the Paste Link DDE method to create dynamic links; this is the same method that is used with Q+E (the query tool that is available with Microsoft Excel version 4.0). The Paste Link DDE method is not the best method for creating dynamic links between Microsoft Query and Microsoft Excel 5.0.



WORKAROUND
To avoid these error messages, do not use the Paste Link command to paste data from Microsoft Query to Microsoft Excel. Instead, use the Get External Data command from the Data menu in Microsoft Excel. This method will establish a dynamic link between Microsoft Query and Microsoft Excel.

To use the Get External Data command to create a dynamic link, follow these steps:
 * 1) In Microsoft Excel, choose the Get External Data command from the Data menu.

NOTE: If this command is unavailable, choose Add-Ins from the Tools menu, and select the Query Add-In check box.
 * 1) Select the desired data source.
 * 2) Construct a new query or close the Add Table dialog box and open an existing query.
 * 3) From the File menu, choose Return Data to Microsoft Excel.
 * 4) Make sure that the Keep Query Definition check box is selected when you return Data to Microsoft Excel.

To edit the query, do either of the following:
 * Double-click any cell containing the external data -or-


 * From the Data menu, choose Get External Data, Edit Query.



MORE INFORMATION
You cannot use the old Paste/Link method from Query to Microsoft Word 6.0, because the Paste Link command is unavailable. To dynamically link data between Microsoft Query and Microsoft Word, use the Insert Database command instead.

When you use Q+E, a common way to dynamically exchange data with Microsoft Excel 4.0 is to copy the data from the Q+E query and use the Paste Link command to paste the data in Microsoft Excel. A dynamic link is then established between Q+E and Microsoft Excel. Double-clicking the data activates Q+E, as does re-opening the Microsoft Excel-dependent sheet and updating the remote links.

