# AppNote: A Practical Guide to Q+E, Part II (WE0821)

Last reviewed: October 6, 1997
Article ID: Q119082

###

The information in this article applies to:

- Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a

The Application Note "A Practical Guide to Using Q+E, Part II," is now available from Microsoft Product Support Services (PSS). This Application Note addresses some specific problems you may encounter when you use Q+E and offers tips about how to perform specific tasks in Q+E. The appendixes list documentation errors, provide an overview of SQL, and supply examples of dynamic data exchange (DDE) macros that you can use when you use Microsoft Excel and Q+E together.

You can obtain this Application Note from the following sources:

You can obtain this Application Note from the following sources:

- Microsoft's World Wide Web Site on the Internet
- The Internet (Microsoft anonymous ftp server)
- The Microsoft Network (MSN)
- Microsoft Download Service (MSDL)
- Microsoft Product Support Services

For complete information, see the "To Obtain This Application Note" section at the end of this article.

## THE TEXT OF WE0821

      Microsoft(R) Product Support Services Application Note (Text File)
                WE0821: A PRACTICAL GUIDE TO USING Q+E, PART II

                                                       Revision Date: 8/94
                                                          No Disk Included

The following information applies to Microsoft Excel versions 3.0, 4.0, and 4.0a for Windows(TM).

-----------------------------------------------------------|

     INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY     |
    | ACCOMPANY THIS DOCUMENT (collectively referred to as an Application |
    | Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER      |
    | EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED      |
    | WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR       |
    | PURPOSE. The user assumes the entire risk as to the accuracy and    |
    | the use of this Application Note. This Application Note may be      |
    | copied and distributed subject to the following conditions: 1) All  |
    | text must be copied without modification and all pages must be      |
    | included; 2) If software is included, all files on the disk(s) must |
    | be copied without modification (the MS-DOS(R) utility diskcopy is   |
    | appropriate for this purpose); 3) All components of this            |
    | Application Note must be distributed together; and 4) This          |
    | Application Note may not be distributed for profit.                 |
    |                                                                     |
    | Copyright (C) 1991-1994 Microsoft Corporation. All Rights Reserved. |
    | Microsoft and MS-DOS are registered trademarks and Windows is a     |
    | trademark of Microsoft Corporation.                                 |
    | Banyan and VINES are registered trademarks of Banyan Systems, Inc.  |
    | dBASE is a registered trademark of Borland International, Inc.      |
    | DEC, DECnet, Pathworks are trademarks of Digital Equipment          |
    | Corporation.                                                        |
    | IBM and OS/2 are registered trademarks of International Business    |
    | Machines Corporation.                                               |
    | NetWare and Novell are registered trademarks of Novell, Inc.        |
    | ORACLE is a registered trademark of Oracle Corporation.             |
    | Q+E is a registered trademark of Pioneer Software Corporation.      |
    | SYBASE is a registered trademark of Sybase, Inc.                    |
    | UNIX is a registered trademark of UNIX Systems Laboratories.        |
    | WordPerfect is a registered trademark of WordPerfect Corporation.   |

|-----------------------------------------------------------

                                   OVERVIEW
                                   ========

This is one of two Application Notes designed to help you use Q+E and Microsoft Excel together to efficiently manipulate your data. The first Application Note, "A Practical Guide to Q+E, Part I," covers the most commonly asked questions about Q+E. This Application Note provides additional examples and contains answers to specific questions about a variety of Q+E issues. Both Application Notes assume that you know how to use Microsoft Excel and that you have a high level of understanding of the Microsoft Excel macro language.

                               TABLE OF CONTENTS
                               =================

Introduction Logging On to Your Database

       ORACLE(R)
       DEC(TM) RDB
       SQL
       SYBASE(R) SQL Server

Problems You May Encounter When You Use Q+E

       Can't Use Allow Editing Command on Novell Network
          To give a user privileges to create and erase files in a
          directory above the QE.EXE file
       Q+E Prompts You to Log On When You Choose Open or Define
       Can't Hide the Logon Dialog Box

Problems Specific to the SQL Server Driver

       Multiple Logon Screens as Default Driver
       Problems Viewing Tables
       Unable to Edit Data or Records Are Locked

Tips for Using Q+E

       Finding Subtotals
          Using the ExcelFile, dBASEFile, and TextFile Drivers
          Using the SQL Server Driver
          Example 1
          Example 2
       Joining Tables
          When You're Working with Large Databases
          When You're Working with More Than One Table at a Time
       Updating Your Query
       Using Q+E When You Create Mailing Labels

Appendix A--Documentation Errors Appendix B--SQL Basics

       What SQL Is
       What SQL Is Not
       Basic Syntax of an SQL Query

Appendix C--EXECUTE Examples

          ADD.CONDITION()
          COLUMN.WIDTH()
          COMMAND()
          DEFINE.INDEX()
          chan=INITIATE("QE","System")
          JOIN()
          KEYS()
          OPEN()
          OPEN.INDEX()
          PASTE.APPEND()
          SQL.QUERY()

Appendix D--Other Examples

       Stored Procedures
          Method 1
          Method 2
       Executing a Stored Procedure from a Microsoft Excel Macro

                                 INTRODUCTION
                                 ============

This Application Note addresses some specific problems you may encounter when you use Q+E and offers tips about how to perform specific tasks in Q+E. The appendixes list documentation errors, provide an overview of SQL, and supply examples of dynamic data exchange (DDE) macros that you can use when you use Microsoft Excel and Q+E together.

                          LOGGING ON TO YOUR DATABASE
                          ===========================

## ORACLE

The Q+E ORACLE driver supports a connection to ORACLE 6.0 running on an OS/2(R) platform. To log on to ORACLE do the following:

1. Obtain the client software from Oracle and install it on your hard disk. ORACLE provides a directory (called ORACLE) of SQL*Net Services to connect to the server through a variety of different networks.

2. Add the following line to your AUTOEXEC.BAT file:

             set config=c:\oracle\config.ora

   Make sure the ORACLE directory is on your path and that Microsoft Excel (or the directory where QE.EXE is located) is on the PATH statement in your AUTOEXEC.BAT file.

3. Before you start Windows, run the appropriate program for your network at the MS-DOS prompt.

             If you are using this network    Run this program
          --------------------------------------------------------

             Microsoft LAN Manager            SQLNTB.EXE
             Novell(R) NetWare(R)             SQLSPX.EXE
             DECnet(TM) Pathworks(TM) 4.0     SQLDNT.EXE
             Banyan(R) VINES(R)               SQLVIN.EXE

4. Start Windows, and then start Q+E.

5. From the File menu, choose Logon. Fill in the appropriate information in the Logon dialog box. For example, type:

             LOGON: <b:MYSERVER>
             USERNAME: <SCOTT>
             PASSWORD: <TIGER>

NOTE: You must specify the drive for LAN Manager networks. This drive letter will vary depending on your particular network. If you are unsure which letter to designate, switch to the MS-DOS prompt, change to the ORACLE directory, and type type config.ora to display a local variable that will indicate the drive letter you are to use. The drive letter is case-sensitive. The following table lists some network programs and specifies the drive letters that are typically associated with them.

          This network program      Uses this drive letter
       -------------------------------------------------------

          TCP/IP SQLNTB.EXE                   T
          Novell SQLIPX.EXE                   X
          LAN Manager SLQNTB.EXE              B
          DECnet SQLDNT.EXE                   D

       Neither the Q+E Oracle driver nor SQLNTB.EXE has been tested for
       connecting to ORACLE running on a UNIX(R) platform. The Q+E Oracle
       driver can connect to ORACLE running on a VAX, but only in standard
       mode.

## DEC RDB

To use the DEC RDB driver to access an RDB database, you must first install and run DECnet services to connect to the data. See your DEC RDB database administrator or RDB manual for details, or contact Digital Equipment Corporation for the proper DECnet files.

You can trace most problems in connecting with RDB to an invalid schema statement. The schema statement cannot include logical operators and the entire sentence must be spelled out as in the following example:

       declare schema filename 'sys$sysdevice:[dbk$personnel]personnel'

       disk name : sys$sysdevice:
       directory name : [dbk$personnel]
       database name : personnel

## SQL

In general, you do not need to obtain any special software to log on to Microsoft SQL Server. Three dynamic-link libraries (DLLs) are required to create named pipes to access Microsoft SQL Server. These DLLs include a DB-Library, a Net-Library, and a Network API Library:

- NETAPI.DLL should be located in your network directory and is provided by your network vendor.
- W3DBLIB.DLL is located in your EXCEL directory.
- DBNMP3.DLL is located in your EXCEL directory.

W3DBLIB.DLL and DBNMP3.DLL are shipped with Microsoft Excel 4.0.

NOTE: The QEREADME.TXT that shipped with Microsoft Excel advises that you use the SQL Server 4.20.00s versions of W3DBLIB.DLL and DBNMP3.DLL. Do not follow this advice. These versions of the DLLs may cause the Q+E warning "Changed language setting to US_English" when you query SQL tables in Q+E. To avoid this error message, use the DLLs that shipped with Microsoft Excel 4.0. To use these DLLS, do either of the following:

- Make sure that Microsoft Excel is in your PATH statement, and rename the SQL W3DBLIB.DLL and DBNMP3.DLL files to W3DBLIB.BAK and DBNMP3.BAK.

  -or-

- Upgrade the DB-Library to version 4.20.21. This version is available with the current version of the SQL Server update.

Microsoft Excel ships with version 1.10 of these DLLs, and installs them in the EXCEL directory.

       Filename      File size       Date

       W3DBLIB.DLL   130736 bytes    4/2/92
       DBNMP3.DLL    8017 bytes      4/2/92

NOTE: These dates reflect the date Microsoft Excel 4.0 shipped; these versions of the DLLs are actually earlier versions than the ones that ship with SQL.

SQL Server 4.2 ships with version 4.20.00 of the same DLLs and installs them in the SQL directory.

       Filename      File size       Date

       W3DBLIB.DLL   163536 bytes    2/26/92
       DBNMP3.DLL    7921 bytes      2/26/92

The text of the QEREADME.TXT that shipped with Microsoft Excel version 4.0a has been revised.

## SYBASE SQL SERVER

Connecting to SYBASE SQL Server has not been tested with Q+E. To connect to a SYBASE SQL Server, you need to obtain files from Sybase called Netlibrary and Client Library. These libraries contain a series of Windows and MS-DOS DLLs that allow you to connect to a SYBASE SQL Server.

                  PROBLEMS YOU MAY ENCOUNTER WHEN YOU USE Q+E
                  ===========================================

## CAN'T USE ALLOW EDITING COMMAND ON NOVELL NETWORK

The Allow Editing command on the Edit menu of Q+E may be unavailable (dimmed) when you open a dBASE(R) file stored on a Novell server. (In most cases, you will be allowed to edit the file if you log on with Supervisor privileges. You will also be able to edit the dBASE file if you open it in Microsoft Excel.) The Allow Editing command is unavailable because Q+E cannot create the temporary file that it requires. Q+E will attempt to create the temporary file in the root directory of the drive on which the QE.EXE file resides. If you do not have create and erase privileges for this root directory, Q+E will fail to create a temporary file, and the Allow Editing command will be unavailable.

Normally, you would not want to give a user privileges to create and erase files in the root directory. However, you can use the Novell Map Root command to map the user to a directory above the QE.EXE file where the user can have create and erase privileges. For example, assume that Q+E is on volume SYS on the file server NETONE. The path to Q+E might resemble the following, NETONE/SYS:PROGRAMS/APPS/EXCEL/QE.EXE.

To give a user privileges to create and

### erase files in a directory above the QE.EXE file

1. Use the Novell Map Root command to map the user to the APPS subdirectory:

             map root f:=netone/sys:programs/apps

             -or-

             map root s1:=netone/sys:programs/apps

2. Give the user create and erase privileges for the APPS subdirectory.

3. Erase any temporary files from F and from the root of the file server.

NOTE: This problem may also occur on Banyan VINES networks.

## Q+E PROMPTS YOU TO LOG ON WHEN YOU CHOOSE OPEN OR DEFINE

When you use the SQL Server, OS/2 Extended Edition, Oracle, or DEC RDB database drivers, you must log on to the specific server to manipulate its data.

If you choose this type of source as your default database driver, Q+E prompts you to log on immediately after you choose Open or Define from the File menu. You can either log on or choose the Cancel button. If you want to switch to a source that does not require logging on (ExcelFile, dBASEFile, or TextFile), change the source in your QE.INI file so that it looks like the following (the QE.INI file is located in your Windows directory and should be modified in a text editor such as Notepad):

       [Q+E]
       NoLogon=dBASEFile,TextFile,ExcelFile
       connect=dBASEFile

On an OS/2 system, this method does not work, and you must reinstall Q+E, taking care not to choose SQLServer as the default driver. Your files will not be affected by this process.

## CAN'T HIDE THE LOGON DIALOG BOX

When you run Microsoft Excel macros or use links to connect to remote databases, the Logon dialog box will always be displayed [regardless of any LOGON() statements you use in your code].

When you log on using DDE, it is often preferable that these types of dialog boxes be hidden from the user. However, this Logon dialog box will appear every time you log on to your database management system (DBMS), and there is no way to disable it.

SQL Server is the only exception to this rule. See "Appendix A," starting on page 12 of this Application Note, for the LOGON macro command that can hide the dialog box.

                  PROBLEMS SPECIFIC TO THE SQL SERVER DRIVER
                  ==========================================

## MULTIPLE LOGON SCREENS AS DEFAULT DRIVER

If your default database driver is an SQL Server driver, you may encounter two Logon dialog boxes when you run the Q+E DB.LOGON() or LOGON() macro commands. To properly log on to the server, cancel the second dialog box, and, in your QE.INI file, change the default driver from SQLServer to another driver (for example, use dBASEFile).

## PROBLEMS VIEWING TABLES

Tables on a SQL Server machine have a three-part ID. The following table describes these parts.

       Part of ID    What the part signifies

       Database      The database where the table is stored
       Owner         The logon ID of the table owner
       Tablename     The name of the table

When you choose Open from the File menu and specify SQLServer as your source, you may not see anything displayed in the Table Name box if you have logged on as a user other than the system administrator or if you have not created any tables.

By default, Q+E displays only the tables you have created. To view tables that were created by another user, select that user's name from the Owner list. For example, if you logged on as GUEST, you would not see the sample files in the PUBS database, because the examples were created by the user DBO. For the sample files to be listed in the Table Name list box, select DBO from the Owner list.

## UNABLE TO EDIT DATA OR RECORDS ARE LOCKED

When you open a SQL Server table, Q+E retrieves as many records as it needs to fill up the window. As you scroll through the records, Q+E retrieves new records from SQL Server. Q+E will not retrieve the last record from SQL Server until you scroll to the bottom of the list.

As records are retrieved from SQL Server, Q+E copies them to a temporary file, or buffer, on your workstation. When you scroll up, Q+E reads copies of the records from the buffer, not from SQL Server. Similarly, if you scroll down to records that you have already seen, those records are read from the buffer.

While Q+E retrieves records from a SQL Server table, SQL Server places locks on all or part of the table. The locks prevent other users from modifying the records in the table while they are being retrieved. Locks may be held until Q+E has read the last record from the table-- that is, when you have scrolled to the bottom of the window (to scroll to the bottom of the window, press CTRL+END)--or until the window is closed.

If you open a SQL Server table but don't scroll to the bottom of the window, locks will remain on records in the table; these locks may prevent other users from modifying records in the table. You can remove the locks by scrolling to the bottom of the window or by closing the window.

                              TIPS FOR USING Q+E
                              ==================

## FINDING SUBTOTALS

### Using the ExcelFile, dBASEFile, Oracle, and TextFile Drivers

The ExcelFile, dBASEFile, Oracle, and TextFile drivers for Q+E do not support the COMPUTE BY or GROUP BY clauses required to find subtotals in an SQL query [COMPUTE BY and GROUP BY are similar to the SUM-IF statement and DSUM() function in Microsoft Excel].

One way to work around this behavior is to create a crosstab table in Microsoft Excel. The following steps use this method to find the total salary of employees in each of the departments in the EMP.DBF sample file:

1.Set Database to External, and select the EMP.DBF sample database.

2.From the Data menu, choose Crosstab to bring up the Crosstab

      ReportWizard.

3.In the Crosstab ReportWizard dialog box, choose the Create A New

      Crosstab button.

4.Choose the Next button to skip the Row Categories dialog box.

5.In the Column Categories dialog box, select DEPT, and choose the

      Add button. Choose the Next button to move to the next dialog box.

6.In the Value Fields dialog box, select SALARY, and choose the Add

      button. Choose the Next button to move to the next dialog box.

7.In the Final dialog box, choose the Create It button.

Your crosstab table should resemble the following example:

        | DEPT |

        | D050 | D101  | D190 | D202 | D050 | D101  | D190 | D202 | Grand
        |      |       |      |      |  Sum |  Sum  |  Sum |  Sum | total

Sum | 68900| 169900| 86900| 86750| 68900| 169900| 86900| 86750| 412450 of | SALARY |

### Using the SQL Server Driver

COMPUTE BY is not supported by the SQL Server driver in Q+E. COMPUTE BY is a Transact-SQL statement that is not part of standard SQL. The examples below describe an SQL query that will obtain subtotals using SUM and GROUP BY. This query will not return a report with subtotals after each category the way that COMPUTE BY does when executed from a SQL Server front-end such as SAF. However, the report will list each record, with the corresponding subtotal for its category displayed in an additional field.

The result of the following steps will be a query that selects records from the SALES table in the PUBS database and finds the total sales for each stor_id. To have the SALES table available, you must have installed the PUBS sample database.

### Example 1

1. Start Q+E.

2. From the File menu, choose Logon, and log on to an SQL Server.

3. From the File menu, choose Open.

4. In the Table Name box, type PUBS.DBO.SALES.

5. In the Source box, select SQLServer, and choose the OK button.

6. From the Select menu, choose SQL Query.

7. After the field name stor_id, type SUM(QTY).

8. At the end of the query, type GROUP BY STOR_ID.

The query should now read:

       USE pubs; SELECT stor_id, SUM(qty), ord_num, date, qty, payterms,
       title_id
       FROM dbo.sales
       GROUP BY stor_id

- Choose the OK button to run the modified query.

  ### Example 2

  To see only the unique stor_ids and their corresponding quantities:

  1. After you follow the steps in Example 1, select the ord_num, date, qty, payterms, and title_id fields.

  2. From the Layout menu, choose Remove Column.

  3. From the Select menu, choose Distinct.

  The result should resemble the following example:

           | stor_id  |  sum(qty)

         1 |  6380    |      8
         2 |  7066    |    125
         3 |  7067    |     90
         4 |  7131    |    130
         5 |  7896    |     60
         6 |  8042    |     80

  ## JOINING TABLES

  ### When You're Working with Large Databases

  You may find that joining and saving large files or tables is very slow. Below are some suggestions for ways that you may be able to optimize both your computer and your task to speed things up:

  - Verify that your tables are truly relational, with no duplicate records or keys.
  - Run a compression utility on your hard disk.
  - Verify that you have 3-4 megabytes (MB) of hard disk space available.
  - Scroll to the end of each table (to do this, press CTRL+END) before joining the files.
  - Direct your SET TEMP statement to an adequate RAMDRIVE.SYS file.
  - Load the large tables to a RAMDRIVE.SYS file before opening them in Q+E.

  ### When You're Working with More Than One Table at a Time

  Q+E lets you display data from multiple database tables in one Query window using the Join command. To join two database tables in a single Query window, the two tables must have at least one column in common. When you join two tables, you join the data in one window (the source) with data in the destination or active window.

  You may need to join two tables if you want to display certain information that is not contained in a single table. For example, you might have a master table that contains employee last names and employee numbers and a Badge table that contains employee numbers and badge numbers to allow for employees who have more than one badge number.

  If you want to create a query that displays last names and badge numbers of employees, you must join the tables. You can join tables by choosing either the Join command or the Outer Join command from the Select menu. Most of the time you will want to use the Join command. The differences between Join and Outer Join are shown in the following table.

         Command      Result

         Join         Discards all records in the destination
                      window that don't have matching records in
                      the source window

         Outer Join   Shows all records from the destination
                      window whether they match or not

  The usual procedure for joining database tables is as follows:

  1. To open the database tables you want to join, choose Open from the File menu.

  2. From the Window menu, choose Arrange All to view both files at once.

  3. In the source file, select the column you want to match.

  4. In the destination file, select the column that matches the source file's column.

  5. From the Select menu, choose Join.

  ## UPDATING YOUR QUERY

  Q+E does not monitor the DBMS you are querying. This means that changes made by other users will not be reflected until you change your query in a way that causes Q+E to reread the records. If you want to force Q+E to refresh the query, choose Query Now from the Select menu.

  ## USING Q+E WHEN YOU CREATE MAILING LABELS

  A common misconception is that you can print or view mailing labels in Q+E. However, Q+E merely creates a text file (with the .LAB extension) based on the records and options you specify. To print or view the file, you can open this text file in a text editor, such as Notepad, or a word processor. You will get better results if you use a word processor, such as Word or WordPerfect(R), because the robust text filters in these word processors are more likely to preserve the correct layout than a simpler program such as Write or Notepad. If the text does not come out the way you like, experiment with different ways to import the text. For example, you will get the best results in Microsoft Word when you use the Text With Layout option when you open the .LAB file.

                         APPENDIX A--DOCUMENTATION ERRORS
                         ================================

  This section identifies a number of documentation errors and ambiguities that you may encounter in the Q+E for Microsoft Excel "User's Guide" that comes with Microsoft Excel 4.0. We've done our best to include them all and to make necessary corrections, but others may exist. If you find what you believe to be a documentation error, or if you find something ambiguous in the documentation, please bring it to our attention.

      Chapter 4:   "Saving a Query, Saving Results, and Printing"

         Page 28   Opening a query file
                   --------------------
                   Step 2. "Select the Query File" should read "Select
                   Queryfile as the source."

         Page 28   Saving results to a new database file
                   -------------------------------------
                   Step 2. "Select the database format..." should read
                   "Select the database format in the Destination
                   box...."
                   Step 4. "...turn on Use Headings for Field Names check
                   box." This option is not available for Mailing Labels,
                   PrintToFile, or QueryFile.

         Page 32   Printing to a file
                   ------------------
                   Step 3. "To set the page width...." This statement is
                   ambiguous. You must specify the number of characters
                   for the page width instead of setting the page width
                   in inches.

      Chapter 5:   "Editing the Current SQL Statement"

         Page 36   It's a good idea to include the source with all
                   SQL SELECT statements. Page 36 has a list of each source
                   and the name of the corresponding driver. The following
                   example shows how to query dBASE-compatible files:

                      select * from dBASEFile | c:\excel\qe\EMP.DBF

                    This list should include the following new drivers.

                      To query this source         Add this prefix
                   ---------------------------------------------------

                      Oracle                       Oracle|
                      DEC RDB                      RDB|
                      dBASE                        dBASEFile|
                      Text file                    TextFile|
                      SQL Server                   SQLServer|
                      Microsoft Excel              ExcelFile|
                      OS/2 1.3 Extended Edition    EEDataMgr|

                   OS/2 is case-sensitive and can distinguish between
                   "SQLServer" and "sqlserver." Please attempt to match the
                   case of these drivers exactly when using OS/2.

                   The documentation doesn't make it clear that some
                   drivers aren't available in both environments. The OS/2
                   Extended Edition database driver is available only with
                   Q+E for OS/2 1.3. The Oracle and DEC RDB drivers are
                   available only with Q+E for Windows. All other drivers
                   are available in both environments.

      Chapter 6:   "Editing Records"

         Page 37   Allowing editing
                   ----------------
                   You cannot edit Microsoft Excel files or Text files, so you
                   cannot choose the Allow Editing command when working with
                   one of these file types. Also, the Select Distinct command
                   is not available with the dBASE File, Excel File, or Text
                   File file types.

      Chapter 8:   "Transferring Data to Other Applications"

         Page 49   The statement, "However, the data remains linked to the
                   underlying database through Q+E," is incorrect. The data is
                   not linked to the underlying database, but instead is
                   linked directly to Q+E.

         Page 52   Linking a Microsoft Excel worksheet to an SQL statement
                   "If the query text is greater than 127 characters,
                   Microsoft Excel cannot automatically start Q+E to access
                   the data." This statement is incorrect. Microsoft Excel has
                   a limit of 255 characters per cell, so pasted data will be
                   unavailable if the total DDE statement, including braces
                   ({ }), is longer than 255 characters. As long as the total
                   DDE statement does not exceed 255 characters, Microsoft
                   Excel can automatically restart Q+E.

      Chapter 9:   "Using Microsoft Excel to Access Data on External
                   Databases"

         Page 57   The instructions listed for automatically starting Q+E
                   in Microsoft Excel apply only to Microsoft Excel version
                   3.0. To automatically start Q+E in Microsoft Excel 4.0, use
                   the Add-in Manager to add the QE.XLA file located in the QE
                   subdirectory (this subdirectory should be located in the
                   directory where you installed Microsoft Excel). The Q+E
                   Add-in menu commands will then be available in Microsoft
                   Excel.

         Page 66   Database functions such as DSUM() do not work with
                   external databases.

         Page 68   DB.DELETE()
                   -----------
                   In earlier versions of the "User's Guide," the heading
                   shows only the question form of the function. There is a
                   standard form as well. Also, change DB.DELETE?() to
                   DB.DELETE()--no question mark--in the second-to-last line
                   in the paragraph below the heading. DB.DELETE?() asks for
                   confirmation from the user to delete all records and
                   reports any errors, DB.DELETE() does not.

                   DB.EXTRACT?()
                   -------------
                   Returns an error message if no records are found.

                   DB.EXTRACT()
                   ------------
                   Does not return an error message.

         Page 69   DB.LOGON()
                   ----------
                   The "source" is the only logon parameter that will work
                   with DB.LOGON. (The Q+E for Microsoft Excel "User's Guide"
                   listed additional parameters, but they did not actually
                   work.) In order to pass the server name, the user ID, and
                   the password, you must use the Send.Keys command.

                   Send.Keys is a Microsoft Excel macro command that passes
                   through keystrokes using the shortcut keys. It is not a
                   preferred method for running macros, but in this case, it
                   is the only method that will work. Documentation for
                   Send.Keys is located in your Microsoft Excel "Function
                   Reference" under "Send.Keys," and the definitions are
                   located under "On.Keys." Send.Keys commands are sent to a
                   buffer, so always place them before the commands they will
                   execute.

                   For example, to log Pam into Testserv with the password of
                   spam, use the following code:

                      =SEND.KEYS("%STestserv%Lpam%Pspam~")
                      =DB.LOGON("sqlserver")

                   NOTE: Use the Logon command for SQL Server; additional
                   parameters can be passed using this command.

         Page 70   DB.SET.DATABASE()
                   -----------------
                   You may need to execute DB.LOGON before the source you
                   specify is available.

         Page 71   DB.PASTE.FIELDNAMES()
                   ---------------------
                   The second argument should read "paste_fields", not
                   "fieldname". Also, when using this function, use one or the
                   other argument (or neither), but not both.

         Page 71   DB.SQL.QUERY()
                   --------------
                   If you use Type 2 or 3, be certain to enter the query
                   argument in double quotation marks:

                      =DB.SQL.QUERY(3,"d:\excel\qe\QUERY1.QEF",1)

  Chapter 10: "Using Dynamic Data Exchange to Communicate with Q+E"

         Page 74   In the sample Microsoft Word macro near the center
                   of the page, the statement

                      chan=DDEInitiate("QE","SELECT LAST_NAME, HIRE_DATE
                      FROM EMP.DBF")

                   should be changed to:

                      chan=DDEInitiate("QE","SELECT LAST_NAME, HIRE_DATE
                      FROM dBASEFile|EMP.DBF")

                   NOTE: The code above is entered all on one line with a
                   space between "FROM" and "dBASEFile."

      Chapter 11   "Q+E Execute Command Reference"

        Page 100   LOGON()
                   -------
                   There are undocumented parameters in the Logon command in
                   Q+E version 3.0a that can be used with SQL Server.

                    - Logon(dbname_Text,Connection_Text,Match_Previous,
                      Show_Dialog)

                    - dbname_Text = ServerType ()

                    - Connection_Text = special keywords used to indicate
                      connection parameters necessary to log on.

                    - Keywords for the connection string include:

                         SRVR=name_of_the_server
                         UID=user_name
                         PWD=password
                         DB=database_name

                    - Match_Previous = TRUE indicates that if the user has
                      previously logged on to the same server, then use that
                      connection. FALSE means start a new connection every
                      time.

                    - Show_Dialog = TRUE indicates that the Logon dialog box
                      should be displayed.

                   Example:

                      =EXECUTE(chan,"[logon('SQLServer','SRVR=MYSVR;
                      UID=guest;PWD=guest', TRUE,FALSE)]")

                   NOTE: The example code is entered all on one line with a
                   space between "PWD=guest'," and "TRUE."

        Page 101   OPEN()
                   ------
                   None of the optional parameters, except for the second one
                   (source), will work with any OPEN version.

        Page 105   PASTE()
                   -------
                   The example should include a closing right parenthesis at
                   the end of each line.

        Page 109   SAVE.LABELS.AS()
                   ----------------
                   The example produces a label with no spaces between
                   FIRST_NAME and LAST_NAME or between STATE and ZIP. To
                   create a label with proper spacing, use the following
                   format instead:

                      SAVE.LABEL.AS('ADDR.LAB','FIRST_NAME+'' ''+LAST_NAME;
                      CITY+'',''+STATE+'' ''+ZIP',5,1,0,0,0)

                   NOTE: This code must be entered all on one line with no
                   space between the semicolon after LAST_NAME and CITY. Note
                   also that the comma between CITY and STATE in the label
                   definition appears to be enclosed in double quotation
                   marks. It is actually enclosed in two sets of single
                   quotation marks.

      Appendix C   "Using Q+E with Microsoft Excel Worksheet Files"

        Page 143   "Opening a Microsoft Excel Worksheet in a Query Window"
                   The second paragraph states that Q+E prompts you for a
                   password if the worksheet is protected. This information is
                   incorrect: there is no prompt, and Q+E is unable to load
                   the file. To use this worksheet, you must unprotect it in
                   Microsoft Excel first.

                              APPENDIX B--SQL BASICS
                              ======================

  The following information is included in the event that your DBMS has not provided you with descriptions of Structured Query Language (SQL). We have tried to cover only the major points; you may want to consult more complete documentation for further information.

  ## WHAT SQL IS

  SQL is a standard database language used to query and manage relational databases. Pronounced "sequel" by some and "S-Q-L" by others, SQL is the database language of choice for the vast majority of client-server database management systems. SQL is a comprehensive language for controlling and interacting with a DBMS. SQL is appropriate for a wide range of tasks, from casual database querying to administration and programming. In this document, we will look at using SQL as a query tool only--not as a programming tool.

  ## WHAT SQL IS NOT

  SQL is not a complete computer language like COBOL or C. There are no IF statements for testing conditions and no DO or FOR statements for looping. Instead, SQL is a database sublanguage, consisting of about 30 statements specialized for data management tasks. These SQL statements are embedded in a host language, such as C, to extend that language for use in database access.

  The "structured" part of SQL is a little misleading, because it isn't particularly structured, especially when compared to highly structured languages such as C or Pascal. Instead, SQL statements resemble simple English sentences with commonly understood keywords.

  ## BASIC SYNTAX OF AN SQL QUERY

  In the following examples, the SELECT statement is used to express an SQL query. Every SELECT statement produces a table of query results containing one or more columns and zero or more rows of data. The FROM clause specifies the tables containing the data to be retrieved by a query. The WHERE clause selects the rows to be included in the query results by applying a criterion to rows of the database.

  The SQL SELECT statement is used to retrieve data. When SQL is used interactively, SELECT is the core of most queries. A SELECT statement operates on a table (or tables) and produces another table containing the results of the query.

  The SELECT statement specifies the columns you want to retrieve. The FROM clause specifies the tables in which the columns are located, and the WHERE clause specifies the rows in the table you want to see. The basic syntax of the SELECT statement is:

         SELECT columns
         FROM tables
         WHERE search_conditions

  You must select at least one column and at least one table in order to have a valid SELECT statement. You can specify more than one column (and/or table) by separating the column (and/or table) names with a comma.

  The following is an example of a SELECT statement in its simplest form:

         SELECT * FROM employee

  The asterisk refers to all the columns in the referenced tables--in this case, the Employee table. The above statement is equivalent to:

         SELECT EmpNum, Name, Weight FROM employee

  Both of these SELECT statements return:

         EmpNum    Name      Weight
         398       Smith     178
         402       Jones     124
         839       Brown     155
         118       Smith     140

  SELECT is most often used with a number of optional clauses. Most of the time you'll want to narrow the scope of the query to include a specific set of columns. For example, if you wanted to view only the weights of the employees, you would make the following SQL query:

         SELECT weight FROM employee

  The result is the following:

         Weight
         178
         124
         155
         140

  You might further narrow your query by adding conditions. SQL uses the keyword WHERE to specify which of the available records you want. To list only the names and weights of those employees who weigh more than 150 pounds, use the following SQL query:

         SELECT name, weight FROM employee
         WHERE weight > 150

  The result is the following:

         Name      Weight
         Brown     155
         Smith     178

  SQL offers a rich set of search conditions that allow you to specify many different kinds of queries. Only those rows that evaluate to TRUE, based on the comparison, are displayed. In addition to ">", all the comparison operators are supported in SQL Server. They are:

         =    Equal to
         !=   Not equal to
         <    Less than
         <=   Less than or equal to
         >    Greater than
         >=   Greater than or equal to

  Using the rules of logic, you can join these simple SQL search conditions to form more complex ones. The search conditions can be combined using the logical conditions AND, OR, and NOT. For example, to find all employees who weigh between 150 and 160 pounds, your query would be:

         SELECT name, weight FROM employee
         WHERE weight >= 150 AND weight <= 160

                           APPENDIX C--EXECUTE EXAMPLES
                           ============================

  This appendix contains examples of most of the commands documented in Chapter 11 of the Q+E for Microsoft Excel "User's Guide." We highly recommend you read Chapter 10 to understand the basic concepts of dynamic data exchange (DDE) and that you become familiar with the syntax of the following commands:

         INITIATE()
         REQUEST()
         FETCH()
         EXECUTE()
         TERMINATE()

  For more information about the syntax of these commands, see the "A Practical Guide to Q+E, Part I" Application Note (WE0500).

  For this section, the following assumptions have been made:

  - QE.EXE is located in a directory listed in your PATH statement.
  - You have installed the dBASEFile and ExcelFile drivers.
  - The dBASEFile and TextFile drivers are located in a directory listed in your PATH statement.
  - You have not deleted or modified the following sample files, which are located in the QE subdirectory:

              ADDR.DBF
              DEPT.DBF
              EMP.DBF
              LOC.DBF
              EMP.XLS

    NOTE: If Q+E and the sample files are not located in the C:\EXCEL\QE directory, you will need to modify many of the following SELECT and OPEN commands to reflect the actual location of Q+E and the sample files.

  ### ADD.CONDITION()

  Select employees who are earning less than $30,00 and who are exempt.

         chan=INITIATE("qe","system")
         =EXECUTE(chan,"[open('c:\excel\qe\EMP.DBF')]")
         =EXECUTE(chan,"[select.column('SALARY')]")
         =EXECUTE(chan,"[add.condition(1,3,30000,FALSE)]")
         =EXECUTE(chan,"[select.column('EXEMPT')]")
         =EXECUTE(chan,"[add.condition(1,1,'Y',FALSE)]")
         =TERMINATE(chan)
         =RETURN()

  ### COLUMN.WIDTH()

  Format the column FIRST_NAME.

         chan=INITIATE("QE","SYSTEM")
         =EXECUTE(chan,"[open('c:\excel\qe\EMP.DBF','ExcelFile')]")
         =EXECUTE(chan,"[select.column('FIRST_NAME')]")
         =EXECUTE(chan,"[column.width(30,false)]")
         =TERMINATE(chan)
         =RETURN()

  COMMAND()

  Send a SELECT statement to a Q+E buffer.

         chan=INITIATE("qe","system")
         =EXECUTE(chan,"[command(1,'[open(''select * from ')]")
         =EXECUTE(chan,"[command(2,'dBASEFile|')]")
         =EXECUTE(chan,"[command(3,'c:\excel\qe\ADDR.DBF'')]')]")
         =TERMINATE(chan)
         =RETURN()

  ### DEFINE.INDEX()

  Create a unique index on EMP.DBF.

         chan=INITIATE("QE","SYSTEM")
         =EXECUTE(chan,"[define('c:\excel\qe\EMP.DBF','dBASEFile')]")
         =EXECUTE(chan,"[define.index('test.ndx','','last_name',
          TRUE,FALSE,1)]")
         =TERMINATE(chan)
         =RETURN()

  ### chan=INITIATE("QE","System")

  Copy all records from a SQL Server table to a separate sheet named "QEXAMPLE.XLS."

         =EXECUTE(chan,"[Logon('SQLServer')]")
         =EXECUTE(chan,"[OPEN('use pubs; select * from
          pubs.dbo.sales','SQLServer')]")
         NR=REQUEST(chan,"NUMROWS")
         NC=REQUEST(chan,"NUMCOLS")
         =EXECUTE(chan,"[fetch('Excel','qexample.xls','R1C1:R"&NR&"C"&NC&"',
          'ALL')]")
         =TERMINATE(chan_num)
         =RETURN()

  Find and copy the location of department L23 to a separate sheet named "QEXAMPLE.XLS."

         chan=INITIATE("QE","System")
         =EXECUTE(chan,"[open('c:\excel\qe\LOC.DBF')]")
         =EXECUTE(chan,"[select.column('LOC_ID')]")
         =EXECUTE(chan,"[sort.ascending()]")
         row_found=MATCH("L23",REQUEST(chan,"C1:C1"))
         =EXECUTE(chan,"[fetch('Excel','qexample.xls','R1C1:R1C3''
          R"&row_found&"')]")
         =TERMINATE(chan)
         =RETURN()

  ### JOIN()

  Join EMP.DBF and DEPT.DBF.

         chan=INITIATE("QE","SYSTEM")
         =EXECUTE(chan,"[open('c:\excel\qe\EMP.DBF','dBASEFile')]")
         =EXECUTE(chan,"[select.column('EMP_ID')]")
         =EXECUTE(chan,"[open('c:\excel\qe\DEPT.DBF','dBASEFile')]")
         =EXECUTE(chan,"[select.column('MGR_ID')]")
         =EXECUTE(chan,"[join()]")
         =TERMINATE(chan)
         =RETURN()

  ### KEYS()

  Update the first record in EMP.DBF by sending keystrokes.

         chan=INITIATE("qe","select * from dBASEFile|c:\excel\qe\EMP.DBF")
         =EXECUTE(chan,"[allow.edit(TRUE)]")
         =EXECUTE(chan,"[select.area('R1C1')]")
         =EXECUTE(chan,"[keys('Tami{tab}Sanders{tab}E99999')]")
         =TERMINATE(chan)
         =RETURN()

  Increase the salaries in EMP.DBF by a user-specified percentage.

         increase=INPUT("Enter the percentage to increase salaries
          by:",1)/100
         chan=INITIATE("qe","select * from dBASEFile|c:\excel\qe\emp.dbf")
         =EXECUTE(chan,"[allow.edit(TRUE)]")
         NR=REQUEST(chan,"NUMROWS")
         =FOR("count",1,NR)
         =EXECUTE(chan,"[select.area('R"&count&"1C5')]")
         =REQUEST(chan,"R"&count&"C5")
         =EXECUTE(chan,"[select.area('R"&count&"C5')]")
         =EXECUTE(chan,"[keys('"&A155*increase+A155&"')]")
         =NEXT()
         =TERMINATE(chan)
         =RETURN()

  ### OPEN()

  Open a dBASE file.

         =EXECUTE(chan,"[open('c:\excel\qe\DEPT.DBF','dBASEFile')]")
         =EXECUTE(chan,"[select.column('MGR_ID')]")

         chan=INITIATE("QE","System")
         =EXECUTE(chan,"[OPEN('c:\excel\qe\ADDR.DBF','dBASEFile')]")
         =TERMINATE(chan)
         =RETURN()

  Open a Microsoft Excel file with a query.

         chan=INITIATE("QE","System")
         =EXECUTE(chan,"[OPEN('select * from
         c:\excel\qe\EMP.XLS','ExcelFile')]")
         =TERMINATE(chan)
         =RETURN()

  Open a SQL Server table with a query.

         chan=INITIATE("QE","System")
         =EXECUTE(chan,"[Logon('SQLServer')]")
         =EXECUTE(chan,"[OPEN('use pubs; select * from
          pubs.dbo.sales','SQLServer')]")
         =TERMINATE(chan)
         =RETURN()

  Open an IBM(R) OS/2 EE table.

         chan=INITIATE("QE","SYSTEM")
         =EXECUTE(chan,"[LOGON('EEDataMGR')"])
         =EXECUTE(chan,"[OPEN('SELECT * FROM USERID.STAFF')]")
         =TERMINATE(chan)
         =RETURN()

  ### OPEN.INDEX()

  Define an index.

         chan=INITIATE("QE","SYSTEM")
         =EXECUTE(chan,"[open('c:\excel\qe\EMP.DBF','dBASEFile')]")
         =EXECUTE(chan,"[open.index('c:\excel\qe\EMPHIRE.NDX',FALSE)]")
         =EXECUTE(chan,"[use.index('emphire')]")
         =TERMINATE(chan)
         =RETURN()

  ### PASTE.APPEND()

  Append a new record to LOC.DBF.

         new_code=INPUT("Enter a new location code:",2)
         new_city=INPUT("What city is this new code for ?",2)
         new_state=INPUT("What state ?",2)
         chan=INITIATE("QE","System")
         =EXECUTE(chan,"[open('c:\excel\qe\LOC.DBF','dBASEFile')]")
         =EXECUTE(chan,"[Allow.Edit(true)]")
         =EXECUTE(chan,"[Paste.Append('"&new_code&CHAR(9)&new_city&CHAR
          (9)&new_state&"')]")
         =TERMINATE(chan)
         =RETURN()

  ### SQL.QUERY()

  Send an SQL query to Q+E.

  NOTE: The SQL Query option is not available in Q+E until a query has been opened. However, you can enter a query by selecting the SQL option in the Open dialog box.

         chan=INITIATE("qe","system")
         =EXECUTE(chan,"[open('select * from c:\excel\qe\EMP.DBF WHERE
          salary > 30000')]")
         =EXECUTE(chan,"[sql.query('select * from c:\excel\qe\ADDR.DBF where
          state=''NC''')]")
         =TERMINATE(chan)
         =RETURN()

  Select all employees in NC, WA, or CA.

         gstate=INPUT("Which state would you like to limit the query to: NC,
          WA, or CA ?",2)
         chan=INITIATE("qe","system")
         =EXECUTE(chan,"[open('c:\excel\qe\ADDR.DBF','dBASEFile')]")
         =EXECUTE(chan,"[sql.query('select * from c:\excel\qe\ADDR.DBF where
         STATE=''"&gstate&"''')]")
         =TERMINATE(chan)
         =RETURN()

  NOTE: The second execute statement should be entered all on one line with a space between "where" and "state."

                            APPENDIX D--OTHER EXAMPLES
                            ==========================

  ## STORED PROCEDURES

  Stored procedures are collections of SQL statements and control-of- flow language. Stored procedures are compiled the first time they are executed, and their execution plans are stored, dramatically improving the performance of SQL statements and batch processes.

  There are two ways to execute a stored procedure from inside Q+E:

  ### Method 1

  Log on to SQL Server. From the File menu, choose Open and choose the SQL button. You can type an SQL command directly into this dialog box. For example, type:

            USE pubs; sp_helpjoins 'publishers', 'authors'

  -or-

  Choose the OK button. Any results generated by the stored procedure will be returned to a blank query file in Q+E.

  ### Method 2

  After opening an SQL Server table, choose SQL Query from the Select menu. Type the command for the stored procedure as shown in the example above.

  ## EXECUTING A STORED PROCEDURE FROM A MICROSOFT EXCEL MACRO

  Stored procedures can also be executed as part of a Microsoft Excel macro. This is done by using the DDE EXECUTE function or by using QE.XLA add-in macro sheet functions. The following are examples of both methods:

         =EXECUTE(chan,"[open('use pubs; sp_helpjoins ' ' ' ' '
         authors' ' ')]")

         -or-

         =DB.SQL.QUERY(2,"use pubs; sp_helpjoins ' 'publishers' ' ' '
         authors' '",1,false)

  ## TO OBTAIN THIS APPLICATION NOTE

  The following file(s) are available for download from the Microsoft Software Library:

       ~ WE0821.EXE (size: 62905 bytes)

  For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base:

         ARTICLE-ID: Q119591
         TITLE     : How to Obtain Microsoft Support Files from Online Services

  If you are unable to access the source(s) listed above, you can have this Application Note mailed or faxed to you by calling Microsoft Product Support Services Monday through Friday, 6:00 A.M. to 6:00 P.M. Pacific time at (425) 635-7070. If you are outside the United States, contact the Microsoft subsidiary for your area. To locate your subsidiary, see the Microsoft World Wide Offices Web site at:

         http://www.microsoft.com/worldwide/default.htm

> Additional query words: 4.00 WE0500
> Keywords : kbappnote kbfile kbusage
> Version : 3.00 4.00 4.00a
> Platform : WINDOWS
> 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: October 6, 1997
©1997 Microsoft Corporation. All rights reserved. Legal Notices.