Microsoft KB Archive/277620

= You receive an &quot;Xlquery.xla&quot; error message when you open or save an Excel workbook =

Article ID: 277620

Article Last Modified on 1/29/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



This article was previously published under Q277620



SYMPTOMS
 When you try to save a Microsoft Excel workbook, you may receive an error message that is similar to the following:

'C:\Program Files\Microsoft Office\Office10\Library\Msquery\Xlquery.xla' could not be found.

If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved or deleted.

Note In Microsoft Office Excel 2003, the path in the error message is as follows: 'C:\Program Files\Microsoft Office\Office11\Library\Msquery\Xlquery.xla' When you try to open a workbook, you may receive an error message that is similar to the following:

Cannot find XLQuery.XLA!Register.DClick, which has been assigned to run each time [filename.xls]Sheet1 is opened. Continuing could cause errors. Cancel opening [filename.xls]Sheet1?





CAUSE
This behavior may occur when either of the following conditions is true:
 * You try to save a workbook that contains a query to external data in the Microsoft Excel 5.0/95 format or in the Microsoft Excel dual format.
 * You try to open a workbook that contains a query to external data that was last saved in the Microsoft Excel 5.0/95 format or in the Microsoft Excel dual format.



WORKAROUND
To work around this problem use one of the following methods.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Method 1
Save the file in the current version format only, and then use Microsoft Visual Basic for Applications code to delete the hidden defined names. The defined names retain the information so that even if you save the file in the current version format only (not the dual format XL5.0 converter), the hidden defined names still retain the information to point to the XLQuery add-in.

Sample code to delete the defined names: Sub DeleteQueryDefinedNames Dim n As Name For Each n In ActiveWorkbook.Names If n.Visible = False And InStr(1, n.Name, &quot;QUERY&quot;, _           vbTextCompare) > 0 And InStr(1, n.Name, _            &quot;Query_from&quot;, vbTextCompare) = 0 Then n.Delete End If  Next End Sub

Method 2
Copy the XLQuery.xla file from one of the old computers to the new computers that has Microsoft Office XP installed. The spreadsheets that you created by using the add-in, must access the code that is in the XLQuery add-in. Users can create new spreadsheets in Excel 2002, and then Excel uses the built-in query and not the older add-in.



MORE INFORMATION
The Excel file format is the same for all versions of Excel from Excel 97 through Excel 2003. In Excel 97 through Excel 2003, a dual file format is available that lets you save a file in both the current format and in the previous format, Microsoft Excel 5.0/95. For Microsoft Excel 2002, the dual format is listed as Microsoft Excel 97-2002 & 5.0/95. For Microsoft Office Excel 2003, the dual format is listed as Microsoft Excel 97-2003 & 5.0/95.

For additional information about the Excel dual file format, click the following article number to view the article in the Microsoft Knowledge Base:

214237 Using Excel 2000 files with earlier versions of Excel

Excel uses Microsoft Query to retrieve data from external data sources such as Microsoft Access databases. Versions of Excel that are earlier than Excel 97 use the XLQuery.xla add-in for this functionality. This functionality is built into Excel 97 and into subsequent versions. The add-in was included with Excel 97 and with Excel 2000 for backward compatibility. However, this add-in file is no longer available in the Excel versions that are listed in the &quot;Applies To&quot; section. Therefore, a workbook that is created in Excel 2002 or in Excel 2003 that contains external data that was imported with Microsoft Query can be saved successfully in the Microsoft Excel 5.0/95 format or in Microsoft dual format. However, the query can no longer be refreshed when the workbook is opened in Excel 5.0 or in Excel 95.

For additional information about add-ins that are not included with the versions of Excel that are listed in the &quot;Applies To&quot; section, click the following article number to view the article in the Microsoft Knowledge Base:

288118 Add-ins that are not included with Excel 2002 and Excel 2003

The query can be refreshed when the workbook is opened in Excel 97, in Microsoft Excel 2000, in Excel 2002, or in Excel 2003.

The earlier Excel format stored the query definition in hidden defined names. If you no longer have to use dual or pre-97 file format, you can remove these defined names programmatically, without an adverse effects to the current query tables, by running the following code: Sub deletem For Each n In ActiveWorkbook.Names If n.Visible = False And InStr(1, n.Name, &quot;QUERY&quot;) > 0 And _ InStr(1, n.Name, &quot;Query_from&quot;) = 0 Then n.Delete End If      Next End Sub

Additional query words: XL2003 XL2002 XL2000 prb

Keywords: kbtshoot kbsavefile kbformat kberrmsg kbpending kbprb KB277620

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.