SQL Statement Not Recorded in Macro If Query Returns Null (136872)
The information in this article applies to:
- Microsoft Excel for Windows 95
- Microsoft Excel for Windows 5.0c
- Microsoft Excel for Windows NT 5.0
This article was previously published under Q136872 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:
- 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.
- 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.
- 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.
- 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.
REFERENCES
"User's Guide," version 5.0, chapter 23
Modification Type: | Minor | Last Reviewed: | 8/17/2005 |
---|
Keywords: | KB136872 |
---|
|