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

ID: Q136872

5.00 5.00c 7.00 WINDOWS kbtool xlquery

The information in this article applies to:

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).
You may notice that your macro will consist of two or more "QueryGetData" lines, Microsoft Excel will divide the SQL statement into several different lines to make the query easier for you to read.

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
For additional information on using SQL functions and the ODBC drivers, please see the following article in the Microsoft Knowledge Base: 

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

REFERENCES

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

KBCategory: kbtool KBSubcategory: xlquery

Additional reference words: 5.00 5.00c 7.00 

Keywords          : xlquery 
Version           : 5.00 5.00c 7.00
Platform          : WINDOWS

Last Reviewed: March 30, 1997