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
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.