Microsoft KB Archive/108010

Microsoft Knowledge Base

Excel: Using Crosstab After DAM in Macro Causes Error Message

Last reviewed: September 12, 1996
Article ID: Q108010

The information in this article applies to:

• Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0

SUMMARY

When you run a macro that uses the Data Access Macro to download information from a remote database server to Microsoft Excel, and the macro then creates a crosstab report based on that data, you may receive one of the following error messages:

• Error retrieving table values from database -- SQL error -- Error in SQL statement. Check criteria values and expressions. -or-
• Invalid fieldname:XXX

These error messages result from hidden names created by the Data Access Macro.

MORE INFORMATION

The Data Access Macro generates the hidden name int_ext_sel to store information important to the external database connection. When the connection is closed from a macro, using the Close.Connection() command, the defined name is not deleted. The CROSSTAB() function recognizes this defined name and attempts to create a crosstab using the external database for the source of information. Once the connection has been closed, there is no external database. Microsoft Excel executes an SQL command to return information about the data tables present in the external database. This command fails, and Microsoft Excel returns the error message.

Steps to Reproduce Problem

1. Start the Macro Recorder.
2. Using the Data Access Macro, connect to an external database and retrieve some data.
3. Close the connection to the external database.
4. Select the range of data on the spreadsheet and set it as a Microsoft Excel database.
5. Create a crosstab based on the database.
6. Stop the macro recorder.
7. Run the Macro.

Note that this error will occur only when these commands are executed by a Microsoft Excel macro. The error does not occur when the operation is carried out by choosing the Crosstab command from the Data menu.

STATUS

Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed above. We are researching this problem and will post new information here invthe Microsoft Knowledge Base as it becomes available.

KBCategory: kbprb KBSubcategory: Additional reference words: 3.00 4.00 5.00 Data Access Language 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 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.