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.