XL: SQL Statement Not Recorded in Macro If Query Returns Null

The information in this article applies to:

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows NT, version 5.0

SYMPTOMS

When you record a macro from Microsoft Excel to run Microsoft Query and return data back to Microsoft Excel, if the query that is run does not return any data, then an SQL statement will not be recorded.

For example, if you run a query that looks in a particular field for all records that contain a value greater than 500 and there are no records that meet this criterion, then the recorder will not create the SQL statement to run the query.

WORKAROUND

To work around this problem, use the following steps to set up your query manually and then copy it into your macro code:

1. Record a macro that will return a data set to Microsoft Excel.

   This will give you the basic syntax for executing a query via macro.

2. To generate a query that will produce the desired result, start Microsoft Query. Select the correct Data Source for your scenario and click Use. Add the tables that your query will reference. Click Close when you've finished adding tables. Set up the query with your criteria. For more information on using Microsoft Query, see the "More Information" section later in this article.

3. To copy the SQL statement from Microsoft Query, click SQL on the View menu. Select the SQL statement and press CTRL+C. Close Microsoft Query.

   The statement in the dialog box is the SQL statement that should replace the one in your macro.

   NOTE: To close Microsoft Query without returning data to Microsoft Excel, click Close Query on the File menu and don't save the changes. Then, on the File menu, click Return Data to Microsoft Excel. Because there is no query open, Microsoft Query will ask you to verify that you do not want to return any data. Click Yes.

4. Paste the SQL statement from the clipboard into Microsoft Excel. Go back to the macro that you recorded in Step 1. Replace the statement that starts with SELECT in the above macro with the SQL statement from Step 3 (Select everything between the quotation marks and click Paste on the Edit menu).

NOTE: If this workaround does not produce the desired effect, set up your database so that it will return data under the conditions you are trying to use and then record the macro.

STATUS

This behavior is by design of Microsoft Excel. Microsoft Excel assumes that the query definition was incorrect.

MORE INFORMATION

For more information about a specific XLODBC.XLA function, choose the Search button in Visual Basic Reference help, type the appropriate function name, and choose the Show Topics button.

If you have an SQL statement that is longer than 255 characters, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q114992
   TITLE     : GP Fault in XLODBC.DLL Using SQLExecQuery or SQLRequest

   ARTICLE-ID: Q125898
   TITLE     : ODBC Examples from NT Help File Using NWind Data Source

REFERENCES

"User's Guide," version 5.0, chapter 23