Microsoft KB Archive/115975

{| = AppNote: A Practical Guide to Using Q+E, Part I (WE0500) =
 * width="100%"|

Last reviewed: October 6, 1997

Article ID: Q115975 The information in this article applies to:
 * 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

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

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 The following information applies to Microsoft Excel for Windows(TM), versions 3.0, 4.0, and 4.0a. | 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.     |
 * Q+E is a registered trademark of Pioneer Software 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. This Application Note covers the most commonly asked questions about Q+E. Another Application Note, "A Practical Guide to Using Q+E, Part II" (WE0821), 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. 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 ================= INTRODUCTION Conventions Used in This Document HOW Q+E USES DRIVERS 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 SPECIFYING THE DEFAULT DRIVER FOR Q+E WHAT IS A QUERY?

To use the menu commands to enter a query To enter a query manually USING QE.XLA (THE MICROSOFT EXCEL ADD-IN MACRO FOR ACCESSING Q+E) 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 USING DYNAMIC DATA EXCHANGE COMMANDS WITH Q+E AND MICROSOFT EXCEL 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 THE DDE EXECUTE FUNCTION Using Simple EXECUTE Functions Using Complex EXECUTE Functions EXECUTE and Concatenation EXECUTE and Variables in WHERE Clauses Troubleshooting Failed EXECUTE Statements DDE Limitations USING MICROSOFT EXCEL AND Q+E TOGETHER 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 COMPARISON: QE.XLA MACRO FUNCTIONS OR DDE EXECUTE COMMANDS? 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 ============ Q+E manipulates database files through the use of database drivers. These drivers emulate the particular database management system (DBMS) used. These drivers have been designed to provide all the features of your DBMS; however, they will be restricted by the limitations of your system. For example, if your DBMS does not support the use of the Structured Query Language (SQL) WHERE clause, Q+E won't provide support for it either. 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 ==================== Q+E drivers are designed to emulate a number of database systems. Q+E can use these drivers to query these different kinds of systems. In Q+E, when you choose an option from a menu or enter an SQL query, Q+E sends an equivalent query to the driver that can communicate with the particular DBMS you are attempting to access. For example, the ExcelFile driver can read the binary interchange file format (BIFF) used by Microsoft Excel, the dBASEFile driver can read dBASE(R)- compatible files, and so forth.

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 NOTE: If your DBMS is not listed here, please read the "Using Q+E with Other Database Systems" section of this Application Note to see if Q+E can access your data some other way. 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.

Once you choose the source to log on to, you will be prompted for the server name, login ID (user name), and password. If these identifiers are accepted by the database system, the driver will appear in the Source list box when you choose Open from the File menu. 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 either your network does not support named pipes, or the support has not been installed correctly. Networks that are compatible with Microsoft's LAN Manager versions 2.0 and later include a file called NETAPI.DLL. Once this file is installed, you should no longer receive the above error message. 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:\ where is the name of the file you are trying to access.

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 ===================================== You can set the default database driver (the driver that will appear when you choose Open from the File menu) by modifying the CONNECT= line in your Q+E initialization file. This line indicates which driver will be selected in Q+E when you open a file. The following is an example of the sections of the initialization file as they appear immediately after Q+E is installed (in this example, the dBASEFile driver was selected as the default database driver): [SQLCONNECT] ExcelFile=QEXLS SQLServer=QESS dBASEFile=QEDBF TextFile=QETXT

[Q+E] connect=dBASEFile NoLogon=dBASEFile,TextFile,ExcelFile Maximized=1 To select a default driver other than the one you selected during installation:   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.   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? ================ 

A query is the process of creating a request in the language of the DBMS you are accessing, and sending that request to the system. The query is processed by the DBMS, and the results, whether data or a confirmation of a change, are sent back to the front end that made the 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
 From the File menu, choose Open. Select the source dBASEFile. Choose the SQL button.  Type the following and choose OK: select * from c:\excel\qe\ADDR.DBF 

The Q+E manual contains some simple examples of SQL statements and the "A Practical Guide to Q+E, Part II" Application Note (WE0821) describes the most common SQL terms. If you need to create more complicated queries, we recommend that you purchase a third-party reference guide to supplement this information. 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.

-or- 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:

  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. </li> Select the range N1:N2 and choose Set Criteria from the Data menu.</li> Copy the data in cell N1 and paste it into cell N3.</li> From the Data menu, choose Extract.</li>  When Microsoft Excel reports that four records are found, choose the Paste button. USING DYNAMIC DATA EXCHANGE COMMANDS WITH Q+E AND MICROSOFT EXCEL ================================================================= </li></ol>

In addition to QE.XLA, you can use dynamic data exchange (DDE) macro commands to communicate between Microsoft Excel and Q+E. DDE is basically a set of rules supported by the operating system (either Windows or OS/2) that an application must follow in order to communicate with another application. An application that supports DDE can manipulate another DDE application to perform such tasks as printing or querying. Not all applications support DDE, but both Microsoft Excel and Q+E do. 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 The examples included in later sections of this Application Note will use these functions. For more information about these functions and what they do, please refer to their descriptions in the "Function Reference" and Chapter 10 of the "Q+E for Microsoft Excel User's Guide."

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") INITIATE will attempt to open a channel of communication with another application. If that application is not already running, you will be prompted to start it with the following message: Remote data not accessible. Start application "Q+E.EXE"? If you choose Yes, Q+E will start, and the DDE channel will open. 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) If Q+E is already running and the INITIATE function is successful, a DDE channel will be opened, and the unique DDE channel identifier returned. It is often convenient to create a variable that will hold the DDE channel identifier. For example, the following statements are equivalent: chan=INITIATE("QE","SYSTEM")

=SET.NAME("chan",INITIATE("QE","SYSTEM")) Both of these statements create a variable "chan" and assign it the value returned by the INITIATE function. The name "chan" can now be referred to in other macro statements to represent the DDE channel that was just opened. 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 You can insert any query or data manipulation statements between the INITIATE and TERMINATE functions. 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! For this reason, you should not use the EXEC function to open an application that may already be 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'? If this happens while you are running your DDE macros, shut down your system, restart it, and then make sure you use a TERMINATE function for each DDE channel you have initiated. 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 ================================ The EXECUTE function is the workhorse of DDE. It allows the client to manipulate the server to perform whatever function you want. EXECUTE also has some very special syntax requirements that will be described later in this Application Note. You can trace most problems in DDE macros to incorrect EXECUTE command syntax.

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 that each function should be entered as a single continuous line of code. 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 100 of the "Q+E for Microsoft Excel User's Guide"). 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.

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')]") However, by using the ampersand concatenation operator (&), you can break this statement into two text strings: =EXECUTE(chan,"[open('select * from c:\excel\qe\"&"ADDR.DBF')]") Note the syntax of this statement. The single text string has been broken into two independent strings by using a double quotation mark to complete the first text string: "[open('select * from c:\excel\qe\" Then the concatenation operator & was used to combine the two strings. Finally, the last text string was started with a double quotation mark to make   "ADDR.DBF')]" an independent text string. This statement will be interpreted exactly as the first, and the idea is easily expanded to use variables to represent all or portions of these text segments. Now, with the concatenation operator in place, if you ask the user to enter the filename of his or her choice, you can use the variable FileName to represent ADDR.DBF: FileName=INPUT("Input a FileName (ADDR.DBF)",2) =EXECUTE(chan,"[open('select * from c:\excel\qe\"&FileName&"')]") Note that the concatenation operator (&) is used with double quotation marks (""). The syntax for concatenating a variable with a text string is to use "&variable_name&", and the result is another text string. The three EXECUTE functions in this section will evaluate the information the same way, but the last example allows more flexibility by giving the user an opportunity to specify which file he or she wants to use.

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:

 In Q+E, open the dBASE file ADDR.DBF (this file should be located in the QE subdirectory).</li> Select the LAST_NAME column so that every last name is highlighted.</li>  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. </li>  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')]") </li></ol>

TIP: If you find that the variables in your WHERE clauses aren't behaving as you expect, perform the same functions using the Q+E menu command and look at the result in the SQL query window. When you create your macro, duplicate exactly what is in that statement.

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 To demonstrate why this command fails, close and restart Q+E, but do not open any queries. Note that the only menus available are File and Help. The SQL Query command is on the Select menu, and, therefore, it is not available until you have actually opened a query. In Q+E, when you want to enter an SQL query and there are no queries open, you must choose the Open command from the File menu and select the SQL option. This is exactly what you must do when you want to send a new query through DDE: chan=INITIATE("qe","system") =EXECUTE(chan,"[OPEN('select * from  dBASEFile|c:\excel\qe\ADDR.DBF')]") =TERMINATE(chan) =RETURN Once you have a query open, you can send the SQL.QUERY command: 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:

  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. </li>  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. </li>  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. </li></ol>

For more information about using Q+E with Microsoft Excel, see the "A Practical Guide to Q+E, Part II" Application Note (WE0821). 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 where the second and third lines of code should be entered all on one line. 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')]") your data will be returned to cell R1C2 (or B1) on that sheet. This function achieves the same result as the example in the "With the COPY and PASTE Functions" section of this Application Note, but the FETCH command requires only one statement.

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") where the second and third lines of code are entered all on one single line with a space between FROM and C:. 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 You can paste a large amount of data into cell B1 because Microsoft Excel will automatically expand the range if there isn't enough room. Six records will be returned, and you should see this data highlighted in B1:E6. Performing the same operation with the FETCH command might look like the following: =EXECUTE(chan,"[FETCH('excel','Macro1',  'r1c2:r"&NR&"c"&NC&"','r1c2:r"&NR&"c"&NC&"')]") NOTE: The above code should be entered as one single line of code, with a space after the comma and before the first single quotation mark before r1c2.

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") where the variable "NR" is defined as the number of rows (or records) in the active query window. The variable "NC" is defined as the number of columns (or fields) in the active query window. These functions allow the client to determine how many rows (records) and columns (fields) have met the criteria. You can use these functions when you need to know the number of records or fields that are available in your application.

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)]") To place the value in B1 in the Clipboard so that you can paste it, use the following functions: =SELECT(B1) =COPY =EXECUTE(chan,"[PASTE]") With the POKE Function 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") For example, if you modify some of the fields for Tyler Bennett, and you store this data in the macro sheet in cells B1:I1, this data constitutes one complete record in the database: | B   |   C     |   D   |  E    |  F   |  G   | H |    I 1 |Tyler | Bennett |E10297 |6/1/77 |32000 | D101 | Y | Volleyball 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]") This macro updates the entire record in the database to the values in B1:I1. You can obtain these same results with the POKE function: =POKE(chan,"r1c1:r1c8","r1c2:r1c9") You could also select the range B1:I1 in the macro sheet, define it with the name <NewData>, and use the POKE function with the defined name, as follows: =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 | 2 | Steve | Jones | E23456 | 2/1/89 | 22500 | D050 | Y | Sailboard 3 | Linda | Simons | E34567 | 3/1/89 | 22500 | D101 | N | Scuba 4 | Peter | Thomas | E45678 | 4/1/89 | 24250 | D101 | Y | 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 It doesn't really matter what this data is, but it does matter whether your database requires a value to be in the field. If the field is a required field that you've defined, you must put some data in that field in your BlankRecord definition. No fields in EMP.DBF are required, but Q+E does not allow a new record to be empty--that is, a new record must contain at least one field with data. 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 You can also use this method of appending one or more records to insert records inside the database itself. Instead of adding the record at the end, the blank records are inserted in the middle, and the data is transferred using the POKE function. 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 Note that the third and fourth lines of code should be entered as one single line.

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 Note that the third and fourth lines of code should be entered as one single line.

TO OBTAIN THIS APPLICATION NOTE
The following file(s) are available for download from the Microsoft Software Library:

~ WE0500.EXE (size: 50529 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
 * }