Excel: Building a SQL Query Longer than 255 Characters

 Last reviewed: July 16, 1997
Article ID: Q88974
The information in this article applies to:
  • Microsoft Excel for the Macintosh, versions 3.0 and 4.0

Summary:

Microsoft Excel for the Macintosh limits the number of characters that can be typed into a cell or assigned to a string to 255 characters. One result of this is that SQL statements which are greater than 255 characters in length must be created and saved to a file before accessing them through a macro SQL.QUERY() statement.

More Information:

The Database Access Macro allows accessing remote databases from within Microsoft Excel, including the submission of SQL (Structured Query Language) queries. In order to send a SQL statement longer than 255 characters within a macro statement, the query must be saved to a file and accessed by a special form of the SQL.QUERY() macro function.

To save a SQL query as a text file:

  1. From the Data menu, choose SQL Query...

  2. Type out the SQL statement in the Query Editor dialog box.

  3. Click the Save button and save the query out to a filename.

The syntax for the macro function SQL.QUERY for accessing a SQL query which has been saved to a file is: 

   =SQL.QUERY(3,"Hard Disk:QueryTextFile",1)
The first argument specifies the query type; 3 indicates that a text file will be the source for the SQL statement. The second argument contains the folder location and filename (QueryTextFile) of the SQL query. The '1' in the function above specifies that the information returned after processing the query should be returned to the current selection. This argument can be changed to whatever is appropriate. A fourth optional argument can also be included specifying a file name to store the returned information.

Reference(s):

"Microsoft Excel Database Access User's Guide," versions 3.0 and 4.0, pages 20 and 34

Additional query words: 3.0 3.00 4.0 4.00 dam dal

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: July 16, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.