AppNote: A Practical Guide to Using Q+E, Part I (WE0500)

• Microsoft Excel for Windows, version 3.0, 4.0, 4.0a

This is one of two Application Notes designed to help you use Q+E and Microsoft Excel together. It covers commonly asked questions. The second Application Note, "A Practical Guide to Using Q+E, Part II" (WE0821), provides more examples and has answers to specific questions about 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.

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

THE TEXT OF WE0500

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

                                                   Revision Date: 2/95
                                                      No Disk Included

| 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 RightsReserved.  |
| Microsoft and MS-DOS are registered trademarks and Windows is a     |
| trademark of Microsoft Corporation.                                 |
| dBASE is a registered trademark of Borland International, Inc.      |
| DEC and DECnet 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.      |
|---------------------------------------------------------------------|


                               OVERVIEW
                               ========

We know that this document may not answer all your questions about Q+E. Therefore, we have tried to provide some general examples that you can adapt to your particular situation. When necessary, specific instructions will be given to allow for differences in database drivers.

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

   Conventions Used in This Document

   Systems That Q+E Drivers Support
      dBASE
      Text
      SQL Server
      ORACLE
      OS/2 Extended Edition
      Microsoft Excel
      Digital Equipment Corporation (DEC) RDB
   Using Q+E with Other Database Systems
      Accessing Data from dBASE- or Microsoft Excel-Compatible Files
      Accessing Data from Text Files

WHAT IS A QUERY?

      To use the menu commands to enter a query
      To enter a query manually

   The Add-in QE.XLA Macro Functions
   Joining Multiple External Databases
      Example of Using Criteria to Join Two Database Tables
      Example of Joining Two External Database Tables

   Preparing to Communicate Through DDE
      PATH or LIBPATH Environment Variables and Support Files
      Number of Available System Resources
   Using DDE Command Functions to Get Q+E and Microsoft Excel to Talk
   to One Another
   Opening and Closing the DDE Channel
      Tips for Using the INITIATE() Function with Q+E

   Using Simple EXECUTE() Functions
   Using Complex EXECUTE() Functions
      EXECUTE() and Concatenation
      EXECUTE() and Variables in WHERE Clauses
   Troubleshooting Failed EXECUTE() Statements
   DDE Limitations

   Getting Data from Q+E to Microsoft Excel
      With the COPY() and PASTE() Functions
      With the FETCH() Command
   Using the REQUEST() Function
      To Determine the Results of Your Query
      To Determine How Many Records Meet Your Criteria
   Getting Data from Microsoft Excel to Q+E
      With the COPY() and PASTE() Functions
      With the POKE() Function
   Using COPY() and PASTE() to Add New Records to an Existing Database
   Using POKE() to Add New Records to an Existing Database

   Advantages of Using QE.XLA Macro Functions
   Disadvantages of Using QE.XLA Macro Functions
   Sample Macros to Compare
      Example of Using Q+E Macro Functions
      Example of Using EXECUTE() Commands


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

This Application Note will be the most useful if you are already familiar with the features and limitations of the DBMS you use. If you are not familiar with your DBMS, we recommend that you consult your DBMS manual or contact its manufacturer for support.

CONVENTIONS USED IN THIS DOCUMENT

Whenever possible, this document uses the sample files located in the QE subdirectory of your Microsoft Excel directory (EMP.DBF, LOC.DBF, ADDR.DBF, DEPT.DBF). If you have removed these files from your hard disk, you may want to reinstall Q+E so you can complete the examples later in this document. In this Application Note, you will be instructed to construct database files and to study examples that build on those used in previous sections. It is important that you perform all the examples in the same order as they appear in this Application Note, because the results of some examples will be the starting data for examples in later sections.

Many examples in this Application Note use single quotation marks (') and double quotation marks ("); be sure to note this distinction as you study these examples. The following is a sample of the format that will be used for examples in this Application note:

   =EXECUTE(chan,"[logon('SQLServer')]")


                         HOW Q+E USES DRIVERS
                         ====================

SYSTEMS THAT Q+E DRIVERS SUPPORT

Q+E can use drivers to provide direct support for the following DBMSs:

   dBASE Files
   Text Files
   Microsoft SQL Server
   ORACLE(R)
   OS/2(R) Extended Edition
   Microsoft Excel Files
   Digital Equipment Corporation (DEC(TM)) RDB

In Q+E, you can specify which driver to use by selecting it from the Source list in the dialog box that appears when you choose Open from the File menu. Some drivers will not be available as sources until you have logged on.

NOTE: The TextFile, dBASEFile, and ExcelFile drivers will never be listed in the Logon dialog box because Q+E can access these file types from your hard disk.

To specify a driver by selecting it in the Sources list

1. From the File menu, choose Logon to display a list of the installed drivers you can log on to.

2. Select the driver you want to use, and choose the OK button.

The driver, or source, you select is responsible for parsing each query sent from Q+E, translating it into the language understood by the database system, and returning the results to Q+E. The best method for this process depends on the type of database you are accessing.

dBASE

Q+E can read, modify, save, and create dBASE II-, III-, and IV- compatible files and indexes. Appendix A of the "Q+E for Microsoft Excel User's Guide" (page 115 for version 4.0 and page 124 for version 3.0) describes in detail the features supported by Q+E. This appendix implies that the features described are unique to dBASE-compatible files. This compatibility information is only partially true: many of the concepts and syntax for the nonunique features in dBASE are supported by most of the other Q+E database drivers. The dBASE features will probably work on your system even if it's not a dBASE system.

Text

Q+E can read and create a variety of text files. These files include files with tab-separated values (these files should have the filename extension .TXT), and comma-separated values (filename extension .CSV). For more information about text files, see the appendix in the "Q+E for Microsoft Excel User's Guide" that describes using text files. When you use text files, several different options are available; the appendix describes many of the most popular options.

SQL Server

SQL Server is a client-server-based system where the actual data, as well as the procedures for accessing that data, are stored on a server. A variety of front end (client) applications, including Q+E, can communicate with the server because both the front-end and the server understand the SQL language.

When a front end needs to access the server, it must create a channel of communication called a named pipe. Named pipes are not supported by all network systems, and on many systems that do offer support for them, the support was not available at the time of the original network installation. If you receive this message:

   Network Communication Layer Not Loaded

An example of a network that only recently has begun offering named pipe support is Novell(R). If you are using Novell NetWare(R), you may need to contact an authorized Novell reseller for a NETAPI.DLL file developed for Novell NetWare. If you are certain that you have correctly installed the NETAPI.DLL file and you continue to get error messages, verify that the directory in which you installed the file is listed in the PATH statement, and that your PATH statement is not too long or otherwise invalid. If you continue to have difficulties, you may want to contact Novell support for more information about installing named pipe support.

ORACLE

In order to access ORACLE, you must have a number of environmental variables defined (these are set in the AUTOEXEC.BAT or other batch file), and you must run SQLNTB.EXE before you start Microsoft Windows. SQLNTB.EXE is an ORACLE file that can be obtained by contacting Oracle Corporation. Be sure to check the documentation for further information if problems persist. The maximum number of logons to ORACLE allowed at one time per Q+E session is two.

OS/2 Extended Edition

IBM(R)'s OS/2 Extended Edition (EE) databases can currently be accessed only by the 3.0 version of Q+E with Microsoft Excel 3.0 on OS/2 1.3. OS/2 2.0 has not been tested with any versions of Microsoft Excel or Q+E.

Microsoft Excel

Q+E can open Microsoft Excel versions 2.x, 3.0, 4.0, and 4.0a files if your Microsoft Excel file contains a defined database. Q+E can neither edit nor define a database in Microsoft Excel; however, Q+E can save the results of a query as a new Microsoft Excel worksheet file. For more information about accessing Microsoft Excel files, please see the appropriate appendix in the "Q+E for Microsoft Excel User's Guide."

Digital Equipment Corporation (DEC) RDB

If you have Microsoft Excel version 4.0 or 4.0a, the RDB driver is installed during the normal installation of Microsoft Excel and Q+E. If you have Microsoft Excel version 3.x or earlier, the DEC RDB driver is available for Q+E for Windows only through a special release of Microsoft Excel, available by calling Microsoft Sales and Information at (800) 426-9400. There are no references to DEC RDB in the "Q+E for Microsoft Excel User's Guide." However, documentation has been provided in the READMEQE.TXT file included with the special release. The Application Note "Q+E, Advanced Topics" (WE0821) also contains some information about using the RDB database.

To use the DEC RDB driver to access an RDB database, you must first install and run DECnet(TM) 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.

Most problems that occur when you connect with RDB can be traced to an invalid schema statement. The schema statement cannot include logicals, 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

USING Q+E WITH OTHER DATABASE SYSTEMS

If Q+E does not have a driver to access your DBMS directly, you may be able to access your data indirectly. That is, if your DBMS can save the data in one of the formats that Q+E can read, you can probably access it with Q+E.

There are two other database systems groups: those whose files can be saved as dBASE- or Microsoft Excel-compatible files, and those whose files can be saved only in text file format. Consult your DBMS manual or manufacturer to see if your system fits one of these groups.

Accessing Data from dBASE- or Microsoft Excel-Compatible Files

Many DBMSs can save data as dBASE-compatible files. A few can save data as Microsoft Excel-compatible files. If your system supports one of these options, consult the manual for instructions on saving in this format. After saving the file in a Q+E-compatible format, attempt to open it in Q+E. If Q+E recognizes the format, the file will be opened, and you will be able to manipulate your data. If Q+E cannot recognize the format, an error similar to the following may be produced

   Invalid file format: c:\<filename>

Accessing Data from Text Files

Most DBMSs can also save database information in a text file format. Q+E can recognize character- delimited files (every field is separated by a character, such as a comma or an asterisk) or fixed-format files (every field is the same number of characters wide). Check your DBMS manual to see if either of these options is available to you. Remember that Microsoft Excel and Q+E work as a team: if one application cannot read your file format, try opening it in the other. Once your file is open, you can save the data in a format the other application can read.

                 SPECIFYING THE DEFAULT DRIVER FOR Q+E
                 =====================================

   [SQLCONNECT]
   ExcelFile=QEXLS
   SQLServer=QESS
   dBASEFile=QEDBF
   TextFile=QETXT

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

1. In a text editor (such as Windows Notepad), open the Q+E initialization file.

   NOTE: The date of QE.EXE determines where your Q+E initialization file will be located. The QE.EXE file dated 12/09/90 uses the WIN.INI file for initialization. Versions of QE.EXE for Windows dated later than 12/09/90 use the QE.INI file that is placed in your Windows directory during installation.

2. Delete the text after CONNECT=, and insert the name of another valid driver.

   Valid driver names are listed in the [SQLCONNECT] section of your initialization file. For example, if you usually open Microsoft Excel files in Q+E, you might want to change the CONNECT=DBASEFILE line to CONNECT=EXCELFILE, as follows:

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

   NOTE: You cannot specify a driver this way with Q+E for OS/2, because the initialization files are binary. To change the default driver under OS/2, you must reinstall Q+E.

   If your QE.EXE file is dated earlier than to 4/2/92, you must restart Windows for this change to take effect. If your QE.EXE file is dated 4/2/92 or later, you must restart Q+E for your changes to take effect.

                              WHAT IS A QUERY?
                              ================
   
   

Q+E offers two ways for you to query the data in your DBMS: you can use the menu commands, or you can enter an SQL query manually. SQL is a standard language used by many database systems and front ends for manipulating and viewing data.

To use the menu commands to enter a query

1. In Q+E, choose Open from the File menu.

2. Select the source (for example, select dBASEFile).

3. Choose a file (such as the sample file ADDR.DBF in the QE subdirectory), and choose OK.

To enter a query manually

1. From the File menu, choose Open.

2. Select the source dBASEFile.

3. Choose the SQL button.

4. Type the following and choose OK:

         select * from c:\excel\qe\ADDR.DBF
   

   USING QE.XLA (THE MICROSOFT EXCEL ADD-IN MACRO FOR ACCESSING Q+E)
   =================================================================

THE ADD-IN QE.XLA MACRO FUNCTIONS

When you use QE.XLA, several commands are added to the Data menu. These commands are supported by corresponding custom macro functions that allow you to create command macros with the same functionality as the menu commands. Keep in mind that the macro functions QE.XLA supports, such as DB.LOGON() and DB.SQL.QUERY(), rely on QE.XLA; therefore, QE.XLA must be open in order for your macro to run.

TIP: You may want to comment your macro to indicate that in order for the macro to run correctly, the user must have QE.XLA loaded; or, you may want to include code that checks whether QE.XLA is loaded.

If you use Microsoft Excel version 4.0 or 4.0a, use the Add-in Manager to automatically add the Q+E items to the Data menu:

1. From the Options Menu, choose Add-Ins.

2. Choose the Add button and select QE.XLA (this file should be located in the QE subdirectory in the directory in which you installed Microsoft Excel).

3. Choose OK.

If you are using Microsoft Excel version 3.0, place the QE.XLA file in your XLSTART subdirectory in order to automatically load Q+E when you start Microsoft Excel.

   NOTE: You can also open QE.XLA by choosing Open from the File menu
   and choosing QE.XLA from the list of files to open. Once QE.XLA is
   loaded, the Paste Fieldnames, SQL Query, and Activate Q+E commands
   are added to the Data menu. When you choose Set Database, you will
   also see the new option External. These commands are explained in
   the  Q+E for Microsoft Excel User's Guide.

JOINING MULTIPLE EXTERNAL DATABASES

QE.XLA will enable you to extract information from multiple external database tables. For example, you might want to extract the employee database information for all managers listed in the department database. Each manager has an ID number that is identical in both databases. You can use the ID number to establish a relationship between the two database tables. In addition, you could add criteria to find only the records for all managers who are located in departments L31 or L04, have a salary of $48,000 or more, and were hired after January 1, 1985.

For more information, see pages 15-18 of the "Q+E for Microsoft Excel User's Guide" for version 4.0, or pages 16-18 of the "Q+E for Microsoft Excel User's Guide" for version 3.0.

Example of Using Criteria to Join Two Database Tables

To view a sample document that demonstrates how to join tables and set criteria, do the following to create a worksheet that has external database definitions and extract ranges:

1. From Microsoft Excel, start QE.XLA.

2. On a new worksheet, choose Set Database from the Data menu, select External, and choose OK.

3. In the Set External Database dialog box, select the QE directory, and then select the DEPT.DBF file, choose OK, and choose Add.

4. Select the EMP.DBF file, choose OK, choose Add, and then choose OK again.

5. From the Data menu, choose Paste Fieldnames, and choose the Paste All button.

Example of Joining Two External Database Tables

The files EMP.DBF and DEPT.DBF have common data in one of their fields. In EMP.DBF, this field is called EMP_ID, and in DEPT.DBF, this field is called MGR_ID. These fields represent the same data but have different field names. To join information from these two tables, you must specify this relationship in a criteria range. To extract the joined records, do the following:

1. In cells N1 and N2, type the following:

            |     N
      --------------------
         1  | dept.MGR_ID
         2  | emp.EMP_ID
   
      This criteria can be translated as follows: for every manager ID
      number in DEPT.DBF, find the corresponding ID number and record in
      EMP.DBF.
   
   

2. Select the range N1:N2 and choose Set Criteria from the Data menu.

3. Copy the data in cell N1 and paste it into cell N3.

4. From the Data menu, choose Extract.

5. When Microsoft Excel reports that four records are found, choose the Paste button.

   USING DYNAMIC DATA EXCHANGE COMMANDS WITH Q+E AND MICROSOFT EXCEL =================================================================

A DDE conversation requires two participants: a client to initiate communication and send commands, and a server to execute and respond to those commands. An application can be a client, a server, or both, depending on how the application is designed. Microsoft Excel is both a DDE client and server, but Q+E has been designed to function as a DDE server only; therefore, when you use DDE with Microsoft Excel and Q+E, Microsoft Excel is the client, and Q+E is the server.

Communicating with Q+E from Microsoft Excel can be challenging because you must understand the capabilities and requirements of Q+E, the syntax and limitations of Microsoft Excel's macro language, and the basics of DDE. Hopefully, the following information will address many of the issues you may encounter when you create Microsoft Excel macros to communicate with Q+E.

PREPARING TO COMMUNICATE THROUGH DDE

Stable communication between applications through DDE may be influenced by many factors, including the PATH or LIBPATH environment variables and support files, and the number of available system resources.

PATH or LIBPATH Environment Variables and Support Files

One of the most important factors in DDE communication is the PATH environment variable for MS-DOS and the PATH and LIBPATH environment variables for OS/2. For OS/2 systems, these variables are set in your CONFIG.SYS file. The PATH variable for MS-DOS systems is set in your AUTOEXEC.BAT file. Both Microsoft Excel and Q+E require a number of support files for proper operation, and unexpected results may occur if the operating system cannot find these critical files. It is very important that the EXCEL directory appear in the PATH and/or LIBPATH statements. For more information about these variables, please consult your MS-DOS or OS/2 documentation.

Number of Available System Resources

Another important factor for DDE communication is the amount of free system resources. In general, the term "system resources" is used to describe the environment an application must operate in. System resources are a conglomeration of many factors, both free or in use, such as memory, the number of applications, graphical items in use (icons, buttons, scroll bars, window borders, and so forth), and open DDE channels.

Windows reports the amount of system resources available when you choose About from the Help menu in Program Manager. There is no way to check the amount of system resources available in OS/2, but, generally speaking, OS/2 has a larger amount of system resources available than Windows does.

The maximum amount of system resources is available when the system is restarted and no applications are running. You can determine how many applications are running by bringing up the Task List (press CTRL+ESC). The Task List displays all running applications and allows you to close those you don't need, freeing system resources.

At times you may find that the DDE channel becomes unstable, performs poorly or slowly, or returns incorrect results. When this happens, we recommend that you shut down your system, restart it, and reopen your applications. Sometimes the system itself may become unstable; when this occurs, you will need to restart your system.

USING DDE COMMAND FUNCTIONS TO GET Q+E

AND MICROSOFT EXCEL TO TALK TO ONE ANOTHER

A number of functions are available for communicating between applications through DDE. Although a DDE command name may vary among applications, its purpose remains the same. Because Microsoft Excel is the DDE client and is manipulating Q+E, we will look at the following five client DDE command functions available in Microsoft Excel:

   Function    |  Description

   INITIATE()  |  Opens a DDE conversation
   TERMINATE() |  Closes a DDE conversation
               |
   REQUEST()   |  Requests information from the server
   POKE()      |  Sends information to the server
               |
   EXECUTE()   |  Tells the server to perform some known function

OPENING AND CLOSING THE DDE CHANNEL

An application must be running before it will respond to a request to open a DDE channel. In Microsoft Excel, there are two functions that start an application: INITIATE() and EXEC(). The best way to start Q+E from a Microsoft Excel macro is with the INITIATE() function, as in the following example:

   =INITIATE("QE","System")

   Remote data not accessible. Start application "Q+E.EXE"?

TIP: If you do not want to see the above error message, you can turn Microsoft Excel's error checking off. Be sure to turn error checking on again right after the INITIATE() function--turning error checking off turns off all of Microsoft Excel's warning messages. For example, use the following commands:

   =ERROR(FALSE)
   =INITIATE("QE","System")
   =ERROR(TRUE)

   chan=INITIATE("QE","SYSTEM")

   =SET.NAME("chan",INITIATE("QE","SYSTEM"))

Opening a DDE channel consumes valuable system resources. Therefore, to free system resources for other functions, close every DDE channel by using a TERMINATE() function. (Note that every example in this Application Note that contains an INITIATE() function will also contain a TERMINATE() function). For example, use the following macro to open a DDE channel and then immediately close it:

   chan=INITIATE("QE","SYSTEM")
   =TERMINATE(chan)
   =RETURN()

Although you can also use the Microsoft Excel EXEC() function to open an application, if the application is already open, you will receive the following error message:

   <Application Name> is already running!

Tips for Using the INITIATE() Function with Q+E

To use Q+E efficiently, think of some of the DDE macro functions as opposites that you can use in pairs. For example, the TERMINATE() function performs the opposite of INITIATE(). Because Q+E has a limit of 64 DDE channels, you shouldn't use the INITIATE() function without using the TERMINATE() function. When more than 64 DDE channels to Q+E have been opened, you may receive the following error, even though Q+E is open:

   Remote data not accessible. Start application 'QE.EXE'?

The REQUEST() and POKE() functions can also be used as pair, although you do not have to use them this way. These functions request or send data between applications. The macro example in the "Q+E for Microsoft Excel User's Guide" (at the bottom of page 73 in both versions 3.0 and 4.0), allows you to retrieve data from Q+E, manipulate it in Microsoft Excel, and then return it to Q+E.

                   USING THE DDE EXECUTE() FUNCTION
                   ================================

USING SIMPLE EXECUTE() FUNCTIONS

Microsoft Excel can send any Q+E command to Q+E using the EXECUTE() function. For a complete description of all the Q+E EXECUTE() commands, refer to Chapter 11 of the "Q+E for Microsoft Excel User's Guide."

EXECUTE() requires two parameters, channel_num and execute_text. Channel_num is represented in the examples below by the defined name "chan." The execute_text argument is a text string enclosed in brackets and enclosed in double quotation marks; that is "[server_command_to_execute]".

The following are several examples of simple EXECUTE() functions:

   =EXECUTE(chan,"[logon('SQLServer')]")
   =EXECUTE(chan,"[allow.edit(TRUE)]")
   =EXECUTE(chan,"[open('select * from c:\excel\qe\EMP.DBF')]")
   =EXECUTE(chan,"[select.column('SALARY')]")
   =EXECUTE(chan,"[add.condition(1,3,30000,FALSE)]")
   =EXECUTE(chan,"[open('select * from c:\excel\qe\EMP.DBF where
   SALARY < 30000')]")

Note also that all execute_text arguments are surrounded by brackets and double quotation marks. These examples meet the requirements of the corresponding Q+E commands as described in Chapter 11 of the "Q+E for Microsoft Excel User's Guide." For example, the text string

'SQLServer' is a valid parameter for the Q+E LOGON command (see page

Note that the third, fourth, and fifth functions in the examples above can be combined into the sixth function. This point is best illustrated by choosing these commands from the menus in Q+E and looking at the resulting SQL query.

USING COMPLEX EXECUTE() FUNCTIONS

EXECUTE() and Concatenation

When you're using variables to represent values, EXECUTE() functions with concatenation operators allow you more flexibility. Because the second argument of the EXECUTE() function is a quoted text string, you can concatenate two or more text fragments and create a valid function.

For example, the following EXECUTE() statement without the concatenation operator will open a query and will display all columns and records from the ADDR.DBF sample database file:

   =EXECUTE(chan,"[open('select * from c:\excel\qe\ADDR.DBF')]")

   =EXECUTE(chan,"[open('select * from c:\excel\qe\"&"ADDR.DBF')]")

   "[open('select * from  c:\excel\qe\"

   "ADDR.DBF')]"

   FileName=INPUT("Input a FileName (ADDR.DBF)",2)
   =EXECUTE(chan,"[open('select * from c:\excel\qe\"&FileName&"')]")

EXECUTE() and Variables in WHERE Clauses

The syntax of an EXECUTE() function becomes somewhat more complicated when a variable is needed to represent a value in a WHERE clause. When you execute DDE commands in Q+E, you are really trying to duplicate the menu commands and the resultant SQL query. The following Q+E procedure illustrates this point:

1. In Q+E, open the dBASE file ADDR.DBF (this file should be located in the QE subdirectory).

2. Select the LAST_NAME column so that every last name is highlighted.

3. From the Select menu, choose Add Condition. The Column should be LAST_NAME, the Operator should be Equal, and the Value should be Bennett. Choose the OK button.

   At this point, only the record for Tyler Bennett should be visible.

4. From the Select menu, choose SQL Query, and note that the SQL query reads:

         SELECT FIRST_NAME, LAST_NAME, EMP_ID, STREET, CITY, STATE, ZIP
         FROM C:\EXCEL\QE\ADDR.DBF
         WHERE LAST_NAME='Bennett'
   

   Note the single quotation marks around Bennett. To place single quotation marks around the name Bennett in a macro, use a DDE statement such as the following:

         =EXECUTE(chan,"[open('select * from ADDR.DBF
         where LAST_NAME = ''Bennett''')]")
   

   You use a pair of single quotation marks around the name to force Q+E to place one set of single quotation marks in the query. To have a user-defined variable LastName represent the name "Bennett" would require a command macro such as the following:

         LastName=INPUT("Enter employee's last name (Bennett)",2)
         =EXECUTE(chan,"[open('select * from ADDR.DBF where
         LAST_NAME = ''"&LastName&"''')]")
   

   This step is not necessary for a WHERE clause that looks for a value as in the following example:

         =EXECUTE(chan,"[open('select * from c:\excel\qe\EMP.DBF
         where SALARY < 30000')]")
   

TROUBLESHOOTING FAILED EXECUTE() STATEMENTS

There are times when a properly constructed EXECUTE() function fails because the menu command being emulated is unavailable (dimmed). For example, the menu command Reset Sort is unavailable until you have actually performed a sort on a query. The procedure that most frequently fails is executing the SQL.QUERY command across a DDE channel that has just been initiated. The following is an example of a command that will fail:

   chan=INITIATE("qe","system")
   =EXECUTE(chan,"[SQL.QUERY('select * from
   dBASEFile|c:\excel\qe\ADDR.DBF')]")
   =TERMINATE(chan)
   =RETURN()

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

   chan=INITIATE("qe","system")
   =EXECUTE(chan,"[OPEN('select * from
   dBASEFile|c:\excel\qe\ADDR.DBF')]")
   =EXECUTE(chan,"[SQL.QUERY('select * from
   ExcelFile|c:\excel\qe\EMP.XLS')]")
   =TERMINATE(chan)
   =RETURN()

DDE LIMITATIONS

When you are using DDE to transfer information from Microsoft Excel to Q+E, be aware of the following three limitations:

1. Your data cannot contain more than 127 characters per item or topic.

       - Both the topic and the item in a DDE link are limited to a length
         of 127 characters.
   

       - The topic is the information that defines the "subject" of a DDE
         conversation and represents some unit of data that is meaningful
         to the DDE server conversation. For most applications that
         manipulate files on disk, the topic is a filename (for example,
         SALES.QEF).
   

       - The item is a reference to a piece of data (such as an integer, a
         string, or a range of cells in a query) that can be passed between
         two applications engaged in a DDE conversation.
   

       - In the following example, QE is the application, SELECT
         FIRST_NAME...EMP.DBF is the item, and R1C1:R1C1 is the topic:
   

            =QE|'SELECT FIRST_NAME, LAST_NAME, EMP_ID, HIRE_DATE,
            SALARY, DEPT, EXEMPT, INTERESTS FROM
            dBASEFile|C:\EXCEL\QE\EMP.DBF'!'R1C1:R1C1'
   
               NOTE: The above sample macro code should be entered as one
               single line of code.
   
   

2. Your data cannot contain more than 225 characters per cell.

       - Assuming that your topic and item are fewer than 128 characters,
         the only size limitation is the 255-character limit per cell in
         Microsoft Excel. Microsoft Excel does not allow you to enter a
         formula longer than 255 characters. This can be a problem when you
         are sending large queries and other DDE instructions to Q+E.
   

       - Q+E does provide the COMMAND() function, which buffers a DDE
         command received from another application in chunks. For example,
         you could send a long SQL query from Microsoft Excel using the
         COMMAND() function by dividing the query into sections 100 to 200
         characters long. Each section can be sent separately to Q+E, which
         concatenates the sections and executes your request.
   

3. You cannot open more than 64 channels per instance of Microsoft Excel.

       - Q+E allows an application to create only 64 channels. However,
         each channel does use a small amount of system resources, so it is
         possible that you will run out of system resources while opening
         DDE channels.
   

                USING MICROSOFT EXCEL AND Q+E TOGETHER
                ======================================

GETTING DATA FROM Q+E TO MICROSOFT EXCEL

Once you have retrieved the data from your source in Q+E, you will usually want to modify or analyze the data in some fashion. While Q+E does have some capabilities for doing this, Microsoft Excel is much more robust and gives additional freedom to the automated system designer. The following examples illustrate some simple methods of retrieving data from Q+E for manipulation in Microsoft Excel.

This section does not show all the methods of retrieving data, but it presents examples that show beginning users how to accomplish their goals.

With the COPY() and PASTE() Functions

The easiest way to get data into Microsoft Excel is to duplicate what is possible "manually"--that is, to physically copy and paste the data. Assume, for example, that you have been asked to verify one piece of data: Tyler Bennett's salary. The following macro uses the COPY() and PASTE() functions to find Bennett's record based on his unique employee ID

   chan=INITIATE("QE","SYSTEM")
   =EXECUTE(chan,"[open('select * from c:\excel\qe\EMP.DBF where
   EMP_ID = ''E10297''')]")
   =EXECUTE(chan,"[select.area('r1c5')]")
   =EXECUTE(chan, "[copy()]")
   =SELECT(B1)
   =PASTE()
   =TERMINATE(chan)
   =RETURN()

Note that because you know Bennett's salary is going to be in the first row, fifth column (R1C5), you can select the area with the SELECT.AREA() function, copy the cell, and paste the contents into B1 of your macro sheet (where B1 is an arbitrarily chosen cell).

After this macro has run, you should see the value 32000 in cell B1 of your macro sheet.

With the FETCH() Command

You can also use the Q+E FETCH() command to bring the data into Microsoft Excel. For example, in the following line of macro code, where your macro sheet is named <Macro1,>

   =EXECUTE(chan,"[FETCH('excel','<Macro1>','r1c2','r1c5')]")

USING THE REQUEST() FUNCTION

To Determine the Results of Your Query

Assume that you are asked to retrieve a large list of data from your DBMS containing the first and last name, employee ID, and salary of all employees earning $30,000 or more. To locate this unknown number of records, you could use a macro similar to the following

   chan=INITIATE("QE","SYSTEM")
   =EXECUTE(chan,"[open('select FIRST_NAME, LAST_NAME, EMP_ID,
   SALARY from c:\excel\qe\EMP.DBF where SALARY >= 30000')]")
   NR=REQUEST(chan,"NUMROWS")
   NC=REQUEST(chan,"NUMCOLS")

The variables "NR" and "NC" are used to represent the number of records (rows and columns) returned by the query. It could be that there are no employees making more than $30,000. You could use macro code similar to the following to select the area, copy, and paste the data into Microsoft Excel:

   =EXECUTE(chan,"[select.area('r1c1:r"&NR&"c"&NC&"')]")
   =EXECUTE(chan,"[copy()]")
   =SELECT(B1)
   =PASTE()

   =EXECUTE(chan,"[FETCH('excel','Macro1',
   'r1c2:r"&NR&"c"&NC&"','r1c2:r"&NR&"c"&NC&"')]")

To Determine How Many Records Meet Your Criteria

You may need to determine how many records a query has returned. If you are expecting one and receive many more, your data may be in error, or your query may need to be refined. As the macro designer, it is up to you to decide which of these two conditions exists.

You can determine how many rows (records) and columns (fields) are in the active query window by using the REQUEST() function with "NUMROWS" and "NUMCOLS" as parameters. Using these parameters, you can determine the number of records, the number of fields or both, as in the following example

   NR=REQUEST(chan,"NUMROWS")
   NC=REQUEST(chan,"NUMCOLS")

GETTING DATA FROM MICROSOFT EXCEL TO Q+E

This section discusses many different ways to get data from Q+E to Microsoft Excel. The method you should use depends on the kind of data you are using. You may also be able to use a combination of methods. This section does not show all the methods of moving data from Microsoft Excel to Q+E, but it presents you with the most straightforward methods.

With the COPY() and PASTE() Functions

Assume Tyler Bennett has received a $3,000 raise (his salary increased from $32,000 to $35,000). Building on the example from the "Getting Data from Q+E to Microsoft Excel" section this Application Note, assume that a query window is open in Q+E with all fields visible and only Tyler's record

from EMP.DBF. The following macro asks you to enter Tyler's new salary, and

the macro places that value in cell B1 of the macro sheet. The macro then selects the destination cell with the SELECT.AREA() function and turns on the ALLOW.EDIT() function so you can make changes:

   =FORMULA(INPUT("Input Tyler's NewSalary (35000)",1),B1)
   =EXECUTE(chan,"[SELECT.AREA('r1c5')]")
   =EXECUTE(chan,"[ALLOW.EDIT(true)]")

   =SELECT(B1)
   =COPY()
   =EXECUTE(chan,"[PASTE()]")

You can also use the POKE() function to move the data into Q+E by specifying where the data is stored in Microsoft Excel (R1C2 or B1) and then specifying the destination in Q+E (R1C5):

   =POKE(chan,"r1c5","r1c2")

   |  B   |   C     |   D   |  E    |  F   |  G   | H |    I

To place this data into Q+E as a unit [with a different method than just copying and pasting or using the POKE() function with individual fields], you could use the following macro (assume that you have only Bennett's record from EMP.DBF in a query window in Q+E, and you've already allowed editing):

   =EXECUTE(chan,"[SELECT.AREA('r1c1:r1c8')]")
   =SELECT(B1:I1)
   =COPY()
   =EXECUTE(chan,"[PASTE()]")

   =POKE(chan,"r1c1:r1c8","r1c2:r1c9")

   =POKE(chan,"r1c1:r1c8",NewData)

USING COPY() AND PASTE() TO ADD NEW RECORDS TO AN EXISTING DATABASE

One of the most basic features of an automated system is that of adding records to an existing database. In this example, assume that you have a record or series of records that needs to be appended to an existing database. These records are in B1:I4 (you've named this range "NewRecords"). The data is as follows:

  |  B    |   C    |   D    |   E    |   F   |  G   | H |    I

1 | Bob   | Smith  | E12345 | 1/1/89 | 18750 | D101 | N |

The easiest way to place these new records in the EMP.DBF database is to use the COPY() function to copy this area to the Clipboard and use the PASTE.APPEND() function to paste the records at the end of the database (assume that you've opened EMP.DBF and allowed editing):

   =SELECT(NewRecords)
   =COPY()
   =EXECUTE(chan,"[PASTE.APPEND()]")

USING POKE() TO ADD NEW RECORDS TO AN EXISTING DATABASE

Using the POKE() function when you are adding new records is more complicated than using the COPY() and PASTE() functions. There is no Q+E command that allows you to easily create new blank records. To add new records, you must add blank records to your database as placeholders, and then you can use the POKE() function to transfer these new records.

For example, assume you have set aside a range of cells (J1:O1 in this example) on your macro sheet that you can use for placeholders. Name this area "BlankRecord". Your data will resemble the following example:

     |  J    |   K    |   L   |   M    | N |  O

   1 | Blank | Record | e0000 | 1/1/00 | 0 | d000

To successfully append the new records, you must use the COPY() function to copy data and use the PASTE.APPEND() function to paste the same number of blank records to the end of your database as the number of new records. To do this, you must know how many records are currently in the database (NR) and the number of new records to be added (NNR). In a FOR-NEXT loop, use the PASTE.APPEND() function to paste a blank record for each new record required, then use the POKE() function to move the new records into the database as follows:

   chan=INITIATE("qe","system")
   =EXECUTE(chan,"[open('select * from c:\excel\qe\EMP.DBF')]")
   NR=REQUEST(chan,"numrows")
   NC=REQUEST(chan,"numcols")
   NNR=ROWS(NewRecords)
   =EXECUTE(chan,"[allow.edit(true)]")
   =SELECT(BlankRecord)
   =COPY()
   =FOR("counter",1,NNR)
   =EXECUTE(chan,"[paste.append()]")
   =NEXT()
   =POKE(chan,"r"&NR+1&"c1:r"&NR+NNR&"c"&NC,NewRecords)
   =TERMINATE(chan)
   =RETURN()

     COMPARISON: QE.XLA MACRO FUNCTIONS OR DDE EXECUTE() COMMANDS?
     =============================================================

ADVANTAGES OF USING QE.XLA MACRO FUNCTIONS

• For those who are new to Microsoft Excel and Q+E, QE.XLA macro functions are easier to use and understand than EXECUTE() commands.
• Q+E macro functions require fewer lines of code to accomplish tasks similar to ones you accomplish with EXECUTE() commands.
• When modeling, you can initially create a macro more quickly with QE.XLA macro functions.
• For some operations, QE.XLA macro functions are faster than the EXECUTE() command equivalents.
• When you use the add-in macro functions, you do not need to know as much about DDE and the Microsoft Excel macro programming language as you do if you are using the EXECUTE() commands.

DISADVANTAGES OF USING QE.XLA MACRO FUNCTIONS

• Because QE.XLA macro functions are provided in a Microsoft Excel add-in macro, QE.XLA macro functions may be supporting a large overhead. The QE.XLA macro functions are often very general with multiple (and sometimes redundant) error checking. Depending on your situation, this can be either an advantage or a disadvantage.
• Using Q+E macro functions is not as flexible as using the EXECUTE() commands.
• The more complicated your macro, the slower Q+E macro functions will be in comparison to EXECUTE() commands.

SAMPLE MACROS TO COMPARE

The following sample macros compare Q+E macro functions and EXECUTE() command-equivalent macros. Both sample macros log on to an SQL Server, perform a simple query, and return the results to a Microsoft Excel worksheet:

Example of Using Q+E Macro Functions

   =ACTIVATE("sheet1")
   =DB.LOGON("SQLServer")
   =DB.SQL.QUERY(2,"use pubs; select * from sales where stor_id =
   ''7131''",1,FALSE)
   =RETURN()

Example of Using EXECUTE() Commands

   chan=INITIATE("qe","system")
   =EXECUTE(chan,"[logon('SQLServer')]")
   =EXECUTE(chan,"[open('use pubs; select * from SQLServer|dbo.sales
   where stor_id=''7131''')]")
   NR=REQUEST(chan,"numrows")
   NC=REQUEST(chan,"numcols")
   =EXECUTE(chan,"[fetch('excel','sheet1',
   'r1c1:r"&NR&"c"&NC&"','all')]")
   =EXECUTE(chan,"[close()]")
   =TERMINATE(chan)
   =RETURN()

TO OBTAIN THIS APPLICATION NOTE

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

 ~ WE0500.EXE (size: 50529 bytes) 

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

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